Manage SQL Server temp tables using T-SQL code

How to create and manage SQL Server temp tables with T-SQL code? A complete guide to local (#) and global (##) temporary tables.

SQL Server temp tables are temporary objects stored in tempdb that hold intermediate result sets for the duration of a session. They come in two types:

• Local temp tables (#TableName) — visible only to the session that created them, dropped automatically when the session ends.
• Global temp tables (##TableName) — visible to all sessions on the instance, dropped when the last referencing session ends.

This tutorial covers how to create, populate, drop, and list temp tables using T-SQL, with a complete real-world example using multiple temp tables for a complex data transformation. SSMS and T-SQL provide all the options needed to manage these objects, which are a valuable tool for optimizing performance and simplifying complex code.

T-SQL – Temp table quick syntax reference

Here is a quick T-SQL cheat sheet to bookmark for your daily work with SQL Server temp tables:

Action	T-SQL syntax
Create local temp table	CREATE TABLE #MyTemp (...);
Create global temp table	CREATE TABLE ##MyTemp (...);
Drop (SQL Server 2016+)	DROP TABLE IF EXISTS #MyTemp;
Drop (pre-2016)	IF OBJECT_ID('tempdb..#MyTemp') IS NOT NULL DROP TABLE #MyTemp;
List all temp tables from db	SELECT * FROM tempdb.sys.tables WHERE name LIKE '#%';

1. What exactly is a SQL Server temporary table?

SQL Server temp tables are temporary objects used for storing data during the lifetime of a session. They are created with the CREATE TABLE command in T-SQL, exactly like permanent tables, but with a hash prefix in the name. In this MS SQL tutorial, we will learn what temp tables are used for, the two types available (local and global), how to create them, and how to insert data into them.

SQL is a programming language designed for managing data in relational database management systems. SQL statements are used to create, read, update, and delete data, and the main object used is the table. A temporary table is a table created in SQL Server for the purpose of storing temporary data that will be used only during the duration of a single session or stored procedure execution.

Local temp tables are dropped when the session that created them is closed (whether the client is SQL Server Management Studio, Azure Data Studio, an application using ADO.NET, or any other connection), or when you execute DROP TABLE on them explicitly.

1.1 Differences between a permanent and a temporary table

What is the difference between a permanent and a temporary table in SQL Server?

• A permanent table is created when a user first creates it and is only dropped explicitly. So, it remains in the database. It is stored on the disk in the user database, and it is backed up if backups are active on the SQL Server database.
• Temporary tables are also created with a simple SQL CREATE statement, but they live in tempdb and are dropped after the end of a stored procedure, after the session is closed, or after all tasks referencing the table are closed (for global temporary tables).

1.2 Main characteristics of temp tables in T-SQL projects

The main goal and benefit of temporary tables is to store intermediate result sets of queries. These result sets live in tempdb — backed by disk, but heavily cached in memory by SQL Server — which is why they are fast to read and write. Temporary tables can be created to store data from a single or multiple queries. However, do not use temporary tables for long-term storage or for frequently accessed data, that is what permanent tables are for.

A version of the MS SQL tutorial is available in video on the Youtube Channel of the blog:

2. Manage local temporary tables overview

A local temporary table can be created and called inside a stored procedure. In this case, the same stored procedure can be called by many sessions at the same time without interference. The MS SQL database engine distinguishes the tables by adding a numeric suffix at the end of the table name. You can inspect this suffix in the modern sys.tables catalog view (the legacy sys.sysobjects still works but has been deprecated since SQL Server 2008). Local temporary tables are stored in tempdb on the same instance as the database that created them.

3. Create a local temporary table

All temp tables always live in tempdb.dbo, the schema is fixed and cannot be specified in the CREATE statement. To create a local temporary table, simply execute a query like this one:

-- Create a sales local temporary table
-- with two columns
CREATE TABLE #Sales_Temp (
   [MonthName]   nvarchar(20),
   [Amount]      numeric(8)
);
GO

-- Insert sales data for the first
-- three month of the year
INSERT INTO #Sales_Temp (MonthName, Amount)
VALUES ( N'January', 1000);
INSERT INTO #Sales_Temp (MonthName, Amount)
VALUES ( N'February', 2000);
INSERT INTO #Sales_Temp (MonthName, Amount)
VALUES ( N'March', 3000);
GO

-- script provided by https://expert-only.com 

However, it is also possible to create a temporary table outside a stored procedure, like for example in a simple T-SQL statement. This is also a way to avoid errors if a stored procedure has the exact same name inside a procedure.

It is recommended to check and drop any existing temp table before creating a new one with the same name, even if they are not permanent objects. The official Microsoft reference is the CREATE TABLE (Transact-SQL) documentation.

4. Drop a local temporary table

To drop a local temporary table on SQL Server 2016 or later, use this syntax:

DROP TABLE IF EXISTS #Sales_Temp; 
GO

4.1 Drop a local temp table with SQL Server versions before 2016

For SQL Server versions before the 2016 release, use this statement instead, because DROP TABLE IF EXISTS was only introduced in MS SQL Server 2016:

IF OBJECT_ID('tempdb..#Sales_Temp', 'U') IS NOT NULL
   DROP TABLE #Sales_Temp;
GO

Here is another IT tutorial for managing classic SQL Server tables this time, i.e. permanent tables.

Manage SQL Server tables

5. Global temporary tables overview

On the other hand, global temporary tables (with the ## prefix) are visible to every session on the instance and are only dropped after all tasks on the table are finished. In other words, the global temp table will be deleted from the system after the session that created it has disconnected and the last query referencing it from any other session has finished executing.

6. Create a global temporary table

This script allows you to create a simple global temporary table:

-- Create a sales global temporary table
-- with two columns
CREATE TABLE ##Sales_Global (
   [MonthName]   nvarchar(20),
   [Amount]      numeric(8)
);
GO

-- Insert sales data for the first
-- three month of the year
INSERT INTO ##Sales_Global (MonthName, Amount)
VALUES ( N'January', 1000);
INSERT INTO ##Sales_Global (MonthName, Amount)
VALUES ( N'February', 2000);
INSERT INTO ##Sales_Global (MonthName, Amount)
VALUES ( N'March', 3000);
GO

-- script provided by https://expert-only.com 

7. Drop a global temporary table

To check if a temp table exists and drop it at the same time without error, use a query like this one (SQL Server 2016 and later):

DROP TABLE IF EXISTS ##Sales_Global;
GO

7.1 Drop a global temp table with SQL Server versions before 2016

To check if the table exists and drop it in versions earlier than 2016, use this example. Note the double hash on both the OBJECT_ID check and the DROP statement:

IF OBJECT_ID('tempdb..##Sales_Global', 'U') IS NOT NULL
   DROP TABLE ##Sales_Global;
GO

8. List all temporary tables of the instance

First, to list all the temporary tables created on the SQL Server instance, use this T-SQL query against the modern sys.tables catalog view (recommended over the deprecated sys.sysobjects):

SELECT name, create_date, modify_date 
FROM tempdb.sys.tables
WHERE name LIKE '#%'
ORDER BY name;

Second, to list only the local temporary tables created on the SQL Server instance, use this T-SQL statement:

SELECT name, create_date 
FROM tempdb.sys.tables
WHERE name LIKE '#%' 
  AND name NOT LIKE '##%'
ORDER BY name;

Third, to list only the global temporary tables created on the current instance, use this T-SQL example:

SELECT name, create_date 
FROM tempdb.sys.tables
WHERE name LIKE '##%'
ORDER BY name;

9. Real-world example: using 4 temp tables for a complex sales transformation

For example, to create a very complex transformation using data from multiple tables and views where you only need to store the final result, temp tables are the right tool. For example, you need to pivot a table, then calculate an average, sum the values, and finally dispatch the data using a ratio across different dates. Then using intermediate temp tables is much easier than writing a very large and complex stored procedure with T-SQL in one shot.

We recommend to split the following code into steps inside one or multiple stored procedures. For example, let’s use these 4 local temporary tables:

• #temp_1_pivot — the raw sales data by product and year
• #temp_2_average — the average sales per product
• #temp_3_group_by_year — the total sales grouped by year
• #temp_4_ratio — the sales ratio per year compared to the total

-- Create temp table #temp_1_pivot
CREATE TABLE #temp_1_pivot (
    ProductID INT,
    Year INT,
    Sales INT
);

INSERT INTO #temp_1_pivot (ProductID, Year, Sales)
VALUES
    (1, 2020, 110),
    (1, 2021, 150),
    (1, 2022, 190),
    (2, 2020, 50),
    (2, 2021, 75),
    (2, 2022, 125),
    (3, 2020, 75),
    (3, 2021, 125),
    (3, 2022, 175),
    (4, 2020, 60),
    (4, 2021, 90),
    (4, 2022, 80);

-- Create temp table #temp_2_average
CREATE TABLE #temp_2_average (
    ProductID INT,
    AverageSales INT
);

-- Populate #temp_2_average with average sales by product ID
INSERT INTO #temp_2_average
SELECT 
    ProductID,
    AVG(Sales) AS AverageSales
FROM 
    #temp_1_pivot
GROUP BY 
    ProductID;

-- Create temp table #temp_3_group_by_year
CREATE TABLE #temp_3_group_by_year (
    Year INT,
    TotalSales INT
);

-- Populate #temp_3_group_by_year with total sales by year
INSERT INTO #temp_3_group_by_year
SELECT 
    Year,
    SUM(Sales) AS TotalSales
FROM 
    #temp_1_pivot
GROUP BY 
    Year;

-- Create temp table #temp_4_ratio
CREATE TABLE #temp_4_ratio (
    Year INT,
    SalesRatio FLOAT
);

-- Populate #temp_4_ratio with the sales ratio by year
INSERT INTO #temp_4_ratio
SELECT 
    t3.Year,
    (CAST(t3.TotalSales AS FLOAT) / (SELECT SUM(TotalSales) FROM #temp_3_group_by_year)) AS SalesRatio
FROM 
    #temp_3_group_by_year t3;

-- Display the results from the temp tables
SELECT * FROM #temp_1_pivot;
SELECT * FROM #temp_2_average;
SELECT * FROM #temp_3_group_by_year;
SELECT * FROM #temp_4_ratio;

-- Drop the temp tables
DROP TABLE #temp_1_pivot;
DROP TABLE #temp_2_average;
DROP TABLE #temp_3_group_by_year;
DROP TABLE #temp_4_ratio;

-- script provided by https://expert-only.com

10. Temp tables vs table variables vs CTE: when to use what?

One of the most common questions in T-SQL projects is when to use a temp table, when to use a table variable (@table), and when a CTE (Common Table Expression) is enough. Here is a quick decision matrix based on real-world usage:

Feature	Temp table (#)	Table variable (@)	CTE (WITH)
Storage	tempdb	tempdb (memory-first)	Inline, in-memory
Indexes supported	Yes (clustered + non-clustered)	Limited (PK and UNIQUE only)	No
Statistics	Yes	No	No
Best for	Large datasets, multi-step logic	Small datasets (< 100 rows)	Single query, recursive logic
Scope	Session (or batch for global)	Batch only	Single statement
Transaction support	Full	Limited (no rollback on data)	N/A

In summary: use a CTE for readable single-statement logic, a table variable for tiny lookup sets, and a temp table whenever you are working with thousands of rows or need indexes and statistics for the query optimizer to do its job properly.

11. Adding indexes to a temp table

One of the biggest performance advantages of temp tables over table variables is that they support indexes. Since SQL Server 2014, you can declare indexes inline in the CREATE TABLE statement. This is very useful in stored procedures where you want to optimize the joins on the temp table:

-- Create a temp table with a primary key
-- and a non-clustered index inline
CREATE TABLE #Sales_Indexed (
   SaleID    INT NOT NULL PRIMARY KEY CLUSTERED,
   ProductID INT NOT NULL,
   Amount    NUMERIC(10,2),
   INDEX IX_ProductID NONCLUSTERED (ProductID)
);
GO

-- script provided by https://expert-only.com

For older versions of SQL Server (pre-2014), you have to create the indexes after the CREATE TABLE statement, with regular CREATE INDEX commands.

12. Pros and cons of using SQL Server temporary tables

12.1 Benefits of temporary tables in T-SQL queries

This is a short list, not exhaustive, of the temporary table benefits:

• Store intermediate results for complex data operations and break down very large stored procedures into readable steps.
• Store the results of a repeatedly executed query. Caching data allows for one-time computation and storage and increases performance. It is therefore an ideal workspace for processing large amounts of data.
• Local temporary tables are created in the tempdb system database and are not visible outside the session in which they were created, which makes them safe for parallel execution.
• Temp tables support indexes, statistics, and parallel query plans, unlike table variables.
• The database engine automatically manages temporary tables and cleans them up when the session ends.

12.2 Limitations of temporary tables in SQL Server

Temp tables are not always the right tool. Here are the main limitations to keep in mind:

• Temp tables in SQL Server do not support foreign keys. If a foreign key is used in the creation script, the execution will not fail, but the key will not be created and the database engine will display a warning message.
• Heavy use of temp tables in many concurrent sessions can cause tempdb contention, especially on PFS, GAM, and SGAM allocation pages. The classic mitigation is to size tempdb with multiple data files (one per CPU core, up to 8) on production instances.
• Stored procedures using temp tables can suffer from statement-level recompilations when the row count changes significantly between executions. This is usually a feature, not a bug, but worth knowing for performance tuning.
• Global temp tables (##) have no security boundary: any session can read or write to them. Use them sparingly and prefer local temp tables in 99% of cases.

13. FAQ on SQL Server temp tables

What is the difference between # and ## in SQL Server?

A single hash # creates a local temp table, visible only to the session that created it. A double hash ## creates a global temp table, visible to every session on the instance until the last referencing session disconnects.

Where are SQL Server temp tables stored?

All temp tables, local and global, are stored in the tempdb system database, in the dbo schema. SQL Server appends a unique numeric suffix to local temp tables to avoid collisions between concurrent sessions.

Are temp tables faster than table variables?

For small datasets under about 100 rows, table variables (@table) are usually faster due to lower overhead. For larger datasets, temp tables win because they support indexes, statistics, and parallel query plans.

Do temp tables need to be dropped manually?

No. Local temp tables are dropped automatically when the session ends. Global temp tables are dropped when the last session referencing them disconnects. Manual DROP TABLE is only needed inside long-running scripts to free tempdb space, or before recreating a temp table with the same name in the same session.

Can a temp table have a primary key or index?

Yes. You can define PRIMARY KEY, UNIQUE, and non-clustered indexes on temp tables exactly like permanent tables. However, foreign keys are silently ignored by the SQL Server engine.

What is the maximum size of a SQL Server temp table?

Limited only by available tempdb space. There is no row or column limit beyond the standard SQL Server table limits of 1,024 columns and roughly 8 KB per row.

14. Conclusion on SQL Server temporary tables

In conclusion, temporary tables provide a convenient way to store and manage intermediate data during complex T-SQL operations. By utilizing the CREATE TABLE and INSERT INTO statements, it is possible to dynamically create and populate temporary tables in SQL Server, with full support for indexes and statistics that table variables and CTEs cannot offer.

The use of temporary tables can improve the efficiency and organization of T-SQL code, making it easier to maintain and debug. With the knowledge and techniques discussed in this blog post, you can now effectively manage temporary tables in your SQL Server projects, choose the right tool between temp tables, table variables and CTEs, and add indexes when performance matters.

This tutorial on how to manage SQL Server temporary tables, whether local or global, explains how to create them, insert data, drop them, and list them from system catalog views. Here is another tutorial on how to list all tables in a SQL Server database.

How To Get the List of All Tables in SQL Server ?