By: Jared Westover | Updated: 2024-05-07 | Comments (5) | Related: 1 | 2 | 3 | 4 | 5 | > Temp Tables
Problem
Temporary (temp) tables are one of Microsoft SQL Server's most powerful features, but you only benefit from this handy resource when you know how to use them. Did you know there are two types of temp tables? Also, when should you skip temp tables in favor of a common table expression (CTE)? Knowing how to create temp tables and their limits is essential to any SQL developer's career. Plus, you'll make your favorite DBA happy.
Solution
In this tutorial, I'll walk through the syntax for creating both kinds of SQL temp tables to store data. You'll also see firsthand how long each type lasts. Next, we'll look at situations where you might want to skip temp tables. By the end, you'll be on your way to mastering temp tables and creating them in your environment today.
Exploring SQL Temporary Tables
I started using temp tables in SQL Server 7.0, so they've been around for a while. Articles often describe them as temporary storage for an intermediate result set, like a rented storage unit you might use while moving. No special permissions are required to create them, which makes using them a breeze. There are two types of temp tables available in SQL Server. For this article, I'm excluding tables you create in tempdb.
- Local: This type is only available to the session that created it and closes once the session closes.
- Global: This type is available to all sessions until the session closes that created it.
SQL Server Local Temporary Tables
I've included the T-SQL syntax for creating a local temp table below. Always
remember to use a single # sign for local temp tables. Out of habit, I include the
DROP TABLE IF EXISTS SQL statement to ensure one with the same name doesn't
exist.
-- mssqltips.com
-- local temporary table
DROP TABLE IF EXISTS #TempTableLocal; -- Drop Table Statement
CREATE TABLE #TempTableLocal -- Create Table Statement
(
Id INT NOT NULL, -- Data Type and NULL setting
NameValue NVARCHAR(200) NULL,
CreateDate DATE
);
INSERT INTO #TempTableLocal
VALUES
(1, 'red', GETDATE()),
(2, 'green', GETDATE()),
(3, 'blue', GETDATE());
GO
SELECT Id, -- SELECT statement
NameValue,
CreateDate
FROM #TempTableLocal;
GO
Results:
Id NameValue CreateDate ----------- ---------- ------------ 1 red 2024-03-29 2 green 2024-03-29 3 blue 2024-03-29
You can also create a temp table by inserting data without defining the schema. This method comes in handy if you don't want to take the time or have a ton of columns.
-- mssqltips.com DROP TABLE IF EXISTS #TempTableLocal_2; SELECT * INTO #TempTableLocal_2 FROM #TempTableLocal; GO
Temp tables act like permanent tables when performing CRUD operations. For instance, the code below performs an insert, update, and delete and finally returns the results.
-- mssqltips.com
INSERT INTO #TempTableLocal
(
Id,
NameValue,
CreateDate
)
VALUES
(4, 'yellow', GETDATE()),
(5, 'purple', GETDATE());
DELETE FROM #TempTableLocal
WHERE NameValue = 'blue';
UPDATE #TempTableLocal
SET NameValue = 'teal'
WHERE NameValue = 'red';
SELECT Id,
NameValue,
CreateDate
FROM #TempTableLocal;
GO
Results:
Id NameValue CreateDate ----------- ---------- ---------- 1 teal 2024-03-29 2 green 2024-03-29 4 yellow 2024-03-29 5 purple 2024-03-29
If you open another session and reference the temp table created above, SQL throws a message saying it doesn't exist.
Msg 208, Level 16, State 0, Line 1 Invalid object name '#TempTableLocal'.
Let's return to our original session, where we created the temp table. One of the nice things about temp tables compared to table variables is that SQL Server creates statistics for them. Since it can be challenging to find the statistics name, I've included the code below from an article Kendra Little wrote.
-- mssqltips.com
USE tempdb;
GO
SELECT sp.stats_id,
name,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('#TempTableLocal');
GO
Once you have the name, run the following code to view the statistics for your table created above. Remember, the names of your statistics will be different.
DBCC SHOW_STATISTICS (#TempTableLocal,_WA_Sys_00000002_B4AE847C);
Do you notice anything about the screenshot above? My statistics need updating! It's not a big deal for this example, but it's something to be aware of.
Have you ever tried to join a wide table that is missing a critical index, and you can't create an index on the fly in production? Another nice thing about a temp table is that you can create indexes. Check out the example below.
-- mssqltips.com
DROP TABLE IF EXISTS #SmallTempTable;
CREATE TABLE #SmallTempTable -- Create Temporary Table
(
Id INT NOT NULL,
OneColumnWeNeed VARCHAR(1000) NULL,
CONSTRAINT PK_Id
PRIMARY KEY CLUSTERED (Id)
);
INSERT INTO #SmallTempTable
(
Id,
OneColumnWeNeed
)
SELECT Id,
Column1
FROM dbo.ReallyWideTable;
GO
SQL Server Global Temporary Tables
Creating a global is simple if you know how to make a local temp table. You simply include two (##) signs in the name prefix. The code below creates the global variety.
-- mssqltips.com
-- Session 1
DROP TABLE IF EXISTS ##TempTableGlobal;
CREATE TABLE ##TempTableGlobal
(
Id INT NOT NULL,
NameValue NVARCHAR(10) NULL,
CreateDate DATE
);
INSERT INTO ##TempTableGlobal
VALUES
(1, 'red', GETDATE()),
(2, 'green', GETDATE()),
(3, 'blue', GETDATE());
GO
What's nice is if I open another session and execute the following SQL query, I don't get an error message.
-- mssqltips.com
-- Session 2
SELECT Id,
NameValue,
CreateDate
FROM ##TempTableGlobal;
GO
But, if I return to the original session and DROP the global temp
table, SQL Server returns an error saying the table doesn't exist in my new
session.
Results:
Msg 208, Level 16, State 0, Line 1 Invalid object name '##TempTableGlobal'.
A former co-worker claimed global temp tables remain active if another session performs a CRUD operation against them even when the original session closes. Based on testing, this claim doesn't hold up. Once that original session closes, it's time to say goodbye to the global temp table. It's easy enough to test this out using the code below.
-- mssqltips.com
-- Session 1
DROP TABLE IF EXISTS ##TempTableGlobal;
CREATE TABLE ##TempTableGlobal
(
Id INT NOT NULL,
NameValue NVARCHAR(10) NULL,
CreateDate DATE
);
INSERT INTO ##TempTableGlobal
VALUES
(1, 'red', GETDATE()),
(2, 'green', GETDATE()),
(3, 'blue', GETDATE());
GO
In another session, execute the code below.
-- mssqltips.com
-- Session 2
INSERT INTO #TempTableLocal
(
Id,
NameValue,
CreateDate
)
VALUES
(4, 'yellow', GETDATE()),
(5, 'purple', GETDATE());
DELETE FROM #TempTableLocal
WHERE NameValue = 'blue';
UPDATE #TempTableLocal
SET NameValue = 'teal'
WHERE NameValue = 'red';
GO
Now, close the original session and try to run the code below.
-- mssqltips.com
-- Session 2
SELECT Id,
NameValue,
CreateDate
FROM ##TempTableGlobal;
GO
Results:
Msg 208, Level 16, State 0, Line 1 Invalid object name '##TempTableGlobal'.
In my experience, global temp tables have limited uses compared to local ones. I only recall using them in demos or loading data via an ETL process where multiple sessions needed access. Also, we dynamically created the global temp tables based on varying columns at the source.
When to Use SQL Temporary Tables
Let's review a few of the most common scenarios where I might use a temp table. Each point below could be an article.
- A temp table might improve performance when dealing with complex calculations where you need to call the results multiple times. For example, imagine a stored procedure where you initially perform calculations and then need to make updates along the way.
- If you can't add indexes on a permanent table but need to join, move only the required data into a temp table, and create an index to help SQL find the data. In the real world, there are instances where you can't create indexes in production on the fly or ever.
- Temp tables can remove SQL Servers' reliance on table spools when performing mass updates on a table. Eric Darling has a post on the topic at the following link.
- You need to chain together several CTEs, which makes the code difficult for anyone other than an SQL guru to read.
- You can replace a cursor using a temp table and the OUTPUT clause. For example, if you need access to inserted, deleted, or updated identity records in your query, you can store those in a temp table using the OUTPUT clause. Jim Evans wrote a helpful article on using OUTPUT for auditing, but the same idea applies.
When to Skip SQL Server Temporary Tables
Since temp tables consume resources in tempdb, I don't recommend creating them for every little query or stored procedure. Don't use a temp table if your query isn't complex, and a simple CTE could gather the results. For example, let's say you have a query where you put a subset of results referenced in a query right below. Why not simply use a CTE or derived table to hold the results?
As with everything in SQL Server, it comes down to testing your code and reviewing the actual execution plan and performance metrics from STATISTICS IO to find what's optimal for you.
SQL Temporary Table Key Points
- There are two types of temp tables: local and global. Local temp tables last for the duration of the session where you created them, and you can only access them in that session. Other sessions can access global temp tables, but they close when the session that created them closes.
- SQL Server stores temp tables on disk in the tempdb database, not in memory. The same is true for table variables, which are close relatives of temp tables.
- Don't get in the habit of using them for everything. Try to stick to use cases where you need a temp table. They take up resources in tempdb. However, they're perfect when trying to reduce the complexity of a long query or capture a calculation to use later.
Next Steps
- Did you know that SQL Server caches metadata from a temp table? Simon Liew wrote an informative article, SQL Server Temporary Table Caching, that describes the benefits and potential drawbacks.
- Do you want to compare the performance of different table objects in SQL Server? Check out the article, SQL Server CTE vs Temp Table vs Table Variable Performance Test, by Ben Snaidero for helpful insights.
- Are you thinking of using permanent tables instead of temp ones? Rick Dobson wrote SQL Server Staging Table vs Temp Table to help you decide.
About the author
Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-05-07
