Manage SQL Server temporary tables using T-SQL code

22 August 2022

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.

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.

Temporary tables are deleted when you exit your SQL Server Management Studio session or when you execute DROP TABLE on them.

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
Create a local temporary table with a SQL Server script
Create a local temporary table with a SQL Server script

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.

Manage SQL Server 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
Create a global temporary table with a SQL Server script
Create a global temporary table with a SQL Server script

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:

  • #temp_1_pivot
  • #temp_2_average
  • #temp_3_group_by_year
  • #temp_4_ratio
-- 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.

Query to list all SQL Server tables

Be the first to comment

Leave a Reply

Your email address will not be published.


*