How to create and manage temporary tables with SQL Server?
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.
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).
Advantages 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.
For example, you need 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 code into steps inside the stored procedures. For example:
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:
Local temporary tables
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.
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.
Drop a local temporary table
To drop a local temporary table, use this syntax:
DROP TABLE IF EXISTS #Sales_Temp; GO
Drop a local temporary 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.
Global temporary tables
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.
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
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
Drop a global temporary 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
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];
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.
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.
This tutorial on temporary tables, 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.