How to create and manage SQL Server temporary tables with T-SQL code?
SSMS and T-SQL provides the options to manage SQL Server temporary tables. These objects can be used to store intermediate results during complex T-SQL code execution. Temporary tables can be a valuable tool for optimizing performance and simplifying code, but managing them can be challenging.
In this blog post, we will explore how to effectively manage temporary tables using T-SQL code, including creation, population, manipulation, and deletion of these tables. Hence to provide a better understanding of the benefits and limitations of temporary tables and the T-SQL techniques required to effectively use them database operations.
Table of Contents
1. What is a SQL Server temporary table?
SQL Server temp tables are temporary tables that are used for storing data temporarily. They are created with the CREATE TABLE command in T-SQL. In MS SQL tutorial, we will learn about SQL Server temp tables and what they are used for. There are two types of temp tables, the local temporary tables, and the global temporary tables. We will also go over how to create them and 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 in the database, 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.
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, 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 are dropped after the end of a stored procedure, or after the session is closed or after all tasks referencing the table is 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 results sets are not stored on disk and in memory. 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.
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 at the same time without interference. The MS SQL database engine distinguish the tables by adding a numeric suffix at the end of the table name, the numeric suffix is listed in the sys.sysobjects system table. 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 are created in the dbo schema so it is useless to indicate a specific schema. 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. Then there is 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 in the database, even if they are not permanent.
4. Drop a local temporary table
To drop a local temporary table, use this syntax:
DROP TABLE IF EXISTS #Sales_Temp; GO
5. Drop a local temp table with a SQL Server version before 2016
For the SQL server version before the 2016 release, use this statement, because the DROP TABLE IF EXISTS was introduced in the MS SQL 2016 version :
IF OBJECT_ID('#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.
6. Global temporary tables overview
On the other hand, the global temporary tables are only dropped after all tasks on the table are finished. In other words, it will be deleted from the system after the last T-SQL query is finished on the same session that created the table.
7. 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 -- fournit par https://expert-only.com
8. 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:
DROP TABLE IF EXISTS ##Sales_Global ; GO
9. Drop a global temp table with SQL Server version before 2016
To check if the table exists and drop it in versions anterior to 2016, use this example:
IF OBJECT_ID('##Sales_Global','U') IS NOT NULL DROP TABLE #Sales_Global; GO
10. List all temporary tables of the instance
First, to list all the temporary table created on the SQL Server instance, use this T-SQL query:
SELECT * FROM tempdb.sys.sysobjects WHERE [name] like '#%' ORDER BY [name];
Second, to list only the local temporary table created on the SQL Server instance, use this T-SQL statement:
SELECT * FROM tempdb.sys.sysobjects WHERE [name] like '#%' AND [name] not like '##%' ORDER BY [name];
Third, to list only the global temporary table created on the current instance, use this T-SQL example:
SELECT * FROM tempdb.sys.sysobjects WHERE [name] like '##%' ORDER BY [name];
11. Complete use case using multiple temporary tables
For example, to create a very complex transformation using data multiple tables and views, and you only need to store the result. For example, you need to pivot a table, then calculate an average and sum them up, and then dispatch the data using a ratio on different dates. Then using intermediate table is easier than a very large and complex stored procedure with T-SQL in one shot.
We recommend to split the following code code into steps inside one or multiple stored procedures. For example, let’s use these 4 local temporary tables:
-- 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, (t3.TotalSales / (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;
12. Pros and Cons of using SQL Server temporary tables
12.1 Benefits of temporary tables in T-SQL queries
This a short list, not exhaustive of the temporary table benefits:
- Store intermediate results for complex data operations.
- 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.
- The database engine automatically manages temporary tables.
12.2 Limitations of temporary tables in SQL Server
The 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.
13. 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.
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.
This tutorial on how to manage SQL Server temporary tables, whether local or global, explains how to create them, insert data, delete them and list them from system tables. Here is another tutorial on how to list all tables in a SQL Server database.