In this article, we will learn the essentials of the temp tables in SQL Server and we will also discuss some performance tips about temporary tables.
What is a temp table?
As its name indicates, temporary tables are used to store data temporarily and they can perform CRUD (Create, Read, Update, and Delete), join, and some other operations like the persistent database tables. Temporary tables are dropped when the session that creates the table has closed, or can also be explicitly dropped by users. At the same time, temporary tables can act like physical tables in many ways, which gives us more flexibility. Such as, we can create constraints, indexes, or statistics in these tables. SQL Server provides two types of temporary tables according to their scope:
- Local Temporary Table
- Global Temporary Table
How to create a local temp table
The accessible scope of local temporary tables is limited by the connection in which they were created. In other words, the local temporary tables are visible for the duration of the connection and when the session is closed the local temporary table are dropped automatically. Local temporary tables can be created using the “CREATE TABLE” syntax but a single hashtag (#) sign must be added in front of their names. The following example script will create a new local temporary table named TempPersonTable.
|
1 2 3 4 5 6 |
CREATE TABLE #TempPersonTable ( PersonID int PRIMARY KEY IDENTITY(1,1), LastName varchar(255), FirstName varchar(255), City varchar(255) ) |
When we query the TempPersonTable, it will return an empty result set.
|
1 |
SELECT * FROM #TempPersonTable |
After creating the table, we can insert data into it as the persisted tables.
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO #TempPersonTable VALUES ( 'Watson', 'Juan', 'Cleveland'), ( 'Baker', 'Dwayne', 'Fort Wayne'), ( 'Walker', 'Eric', 'Tucson'), ( 'Peterson', 'Bob', 'Indianapolis'); SELECT * FROM #TempPersonTable; |
At the same time, we can create a temporary table using the SQL SELECT INTO statement command. The following query will create a local temporary copy of the Orders table.
|
1 2 3 4 |
SELECT * INTO #TempOrdersTable FROM Orders SELECT * FROM #TempOrdersTable |
The local temporary tables are created in the tempdb database with a unique name because they can be created with the same name by the other connections. In this way, SQL Server prevents the same name conflict.
How to create a global temp table
The global temporary tables are created using the CREATE TABLE statement and their names must be prefixed with the double hashtag (##) sign. These tables can be accessed by all other sessions, unlike local ones. The following code will create a global temporary table.
|
1 2 3 4 5 |
CREATE TABLE ##Customers (CustomerId INT IDENTITY(1,1) PRIMARY KEY, CustomerFullName VARCHAR(50), EMail VARCHAR(50), CustomerAddress VARCHAR(50), Country VARCHAR(50)) |
After creating the temporary table, we can populate and query it.
|
1 2 3 4 5 6 7 |
INSERT INTO ##Customers VALUES('Olivia Rodriguez','hlvdw.wvmqi@rkx.co','66 Elizabeth Street','Canada'), ('Riley Hall','efkfu.ngtpgi@rgg.org','23 Brookside Drive','U.K.') , ('Allison Lopez','ygpnm.wdvyufu@bevl.com','11 Church Road','South Africa'), ('Gianna King','nklwjv.wimmhzr@ncni.co','193 Park Place','Belgium') SELECT * FROM ##Customers |
Global table names are not transformed into a unique name.
Global temporary tables can be dropped explicitly with the DROP TABLE statement on any connection or automatically dropped when the session is closed that creates the table. However, this global table should not be actively in use by the other sessions. If it is actively in use by the other sessions, SQL Server waits until the completion of the last SQL statement activities that use the global temporary table, and then the global table is dropped. Let’s illustrate this interesting characteristic of the global temporary tables using a very simple example:
Firstly, we will create a global temporary table and populate it with some rows.
|
1 2 3 4 5 6 |
INSERT INTO ##TestTempTable VALUES ('TextACol1','TextACol2') , ('TextBCol1','TextBCol2') , ('TextCCol1','TextCCol2') SELECT * FROM ##TestTempTable |
As a second step, we will open a new query window and start a new transaction to delete all the data. However, we do not commit or rollback the transaction so, the table will be actively in use by the connection.
|
1 2 |
BEGIN TRAN DELETE FROM ##TestTempTable |
We close the first connection that has created the table and then we check the existence of the global temporary table.
|
1 |
SELECT * FROM ##TestTempTable |
As we can see, SQL Server does not drop the temporary table if it is actively used by any session. After committing or rollbacking the transaction, the global table will be dropped and invisible for the same connection.
|
1 2 |
COMMIT TRAN SELECT * FROM ##TestTempTable |
Temp table performance tuning tips
The temporary tables might be very useful when we are required to transform or modify the big datasets. In SQL Server, nothing comes without a price therefore we need to take into account performance considerations about the temporary tables when we decide to use it.
- Tune the tempdb database: All temp tables are created in the tempdb database, for this reason, we have to ensure all performance settings are properly applied to the tempdb database
- Only insert the required columns and data: After creating the temporary table, we need to insert the necessary data so that we can increase the data insert performance
- SELECT INTO vs INSERT INTO: In order to populate data into the temporary tables we can use the SELECT INTO and INSERT INTO statements and these two statements offer similar functionalities. In the context of performance, these two statements may have some differences. For this reason, making a test in your database is the best solution to decide which one gains a performance advantage over another one
- Drop temp tables quickly: As possible we need to drop temporary tables explicitly and we should not wait for automatic deletion when the connection is ended. So that, we can free up the tempdb resources more quickly
- Use indexes if you are sure they are needed: Creating an index is a resource-consuming process for SQL Server so we must make sure there will be performance improvements when we decide to create them. Related to this issue, creating a clustered index can be the first option, and then potential nonclustered indexes can be created
The tough opponent
The main purpose of the temporary tables is to store data temporarily. On the other hand, in-memory optimized tables have been entered our data life with SQL Server 2014, and schema only optimized tables can store the data until the database restart. In this context, the temp tables and schema only memory-optimized tables have similar functionalities in terms of storing data temporarily. The main advantage of using the memory-optimized tables instead of the temporary tables is to remove I/O activities so it has more performance but we have to notice that they consume memory. In the following example, we will compare the temp table performance against the memory-optimized table. At first, we will create the required filegroup and files for the memory-optimized tables.
|
1 2 3 4 5 6 7 8 |
ALTER DATABASE AdventureWorks2017 ADD FILEGROUP AdventureWorks2017_MemoryOptFileGroup CONTAINS MEMORY_OPTIMIZED_DATA; GO ALTER DATABASE AdventureWorks2017 ADD FILE (name='AdventureWorks2017_MemoryOptFile', filename='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017Mem.ldf') TO FILEGROUP AdventureWorks2017_MemoryOptFileGroup |
Now we will create a memory-optimized table and insert 10 million rows into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SET STATISTICS TIME ON GO CREATE TABLE TestMemOptTable( [OrderId] [int] NOT NULL , [CustomerName] [varchar](50) NULL, [OrderDate] [datetime] NULL, [ProductName] [varchar](50) NULL, [Amount] [int] NULL PRIMARY KEY NONCLUSTERED HASH (OrderId) WITH (BUCKET_COUNT = 10000000) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); INSERT INTO TestMemOptTable SELECT * FROM Orders |
This insert operation has completed about 11 seconds when we use a schema only memory-optimized table.
As we mentioned, the memory-optimized tables consume memory, we can analyze the memory consumption for this table with help of the Memory Usage By Memory Optimized Objects report.
In this report, we can see that TestMemOptTable consumes 762 MB of memory.
Now we will repeat the same insert operation for a local temp table.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE #TestTempTable( [OrderId] [int] NOT NULL PRIMARY KEY NONCLUSTERED, [CustomerName] [varchar](50) NULL, [OrderDate] [datetime] NULL, [ProductName] [varchar](50) NULL, [Amount] [int] NULL ) INSERT INTO #TestTempTable SELECT * FROM Orders |
The insert operation has completed about 35 seconds for the temporary table.
In this small test, we saw that there is a dramatic performance difference between the memory-optimized and temporary tables. As the last test, we will not index the temporary table and use the TABLOCKX hint for the temporary table.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE #TestTempTableWithoutIndex( [OrderId] [int] NOT NULL , [CustomerName] [varchar](50) NULL, [OrderDate] [datetime] NULL, [ProductName] [varchar](50) NULL, [Amount] [int] NULL ) INSERT INTO #TestTempTableWithoutIndex WITH(TABLOCKX) SELECT * FROM Orders |
The result of this test is very interesting because the insert operation was completed in only 6 seconds and this time is smaller than the memory-optimized table. This performance gain explanation is hidden in the execution plan of the insert query. When we analyze the execution plan of the query, we can see a parallel execution plan.
SQL Server has generated a parallel execution plan so it has performed the insert operations in a parallel manner. The parallel insert operations can be used by the database engine when the destination table is a heap and TABLOCKX is used in the insert statement.
Conclusion
In this article, we learned how we can create temp tables and also explored some interesting performance tips about them.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023