SQL Server Temp Table vs Table Variable


By:
Overview

As you craft your queries you find that, for performance reasons, you may have to store intermediate results into a temporary structure. SQL Server provides local and global temp tables as well as table variables to store intermediate results.

Explanation

There's no true answer to which of these types are the best type to use as it depends on the task. It's very important to understand the differences among them before you decide on using one over the other. Local temp tables (i.e. #temp) are local to your connection and are dropped when no longer in use. They work and act like regular tables. They can be indexed, have constraints added, and carry statistics. Global temp tables (i.e. ##temp) work similarly to local ones except they are available to be shared across connections. Temp tables are also subject to recompiles.

In contrast, table variables are declared as opposed to created. Other ways how table variables differ from temp tables: they can't be indexed via CREATE INDEX, can't be created using SELECT/INTO logic, can't be truncated, and don't carry statistics. They do allow indexes to be created via PRIMARY KEY and UNIQUE constraints declared within the variable definition and these can be used by queries. The fact that table variables don't carry statistics is very important to note as it can potentially lead to less than optimal queries when working with large sets of data. This may end up influencing your decision on which to use. There is no absolute here and testing will ultimately bear out which type makes most sense.

Here's an example of a data selected from a table variable with indexes assigned via primary key and unique constraints

declare @temp table(ProductID int, SalesOrderID int, SalesOrderDetailID int, OrderQty smallint,    
                    primary key clustered (SalesOrderID,SalesOrderDetailID),
                    unique nonclustered (ProductID,SalesOrderID,SalesOrderDetailID))
insert into @temp (ProductID, SalesOrderID, SalesOrderDetailID, OrderQty)
select ProductID, SalesOrderID, SalesOrderDetailID, OrderQty
from Sales.SalesOrderDetail
select temp.SalesOrderID, temp.SalesOrderDetailID, temp.ProductID, temp.OrderQty
from @temp as temp
where temp.SalesOrderID = 43661
go

The execution plan does yield a clustered index seek in this case. As queries get more complex and the amount of data to process grows, the table variable may or may not perform poorly due to the lack of statistics.

The execution plan does yield a clustered index seek in this case

It has been a common myth for some time that table variables only work in memory. Temp tables and table variables are instantiated in tempdb and SQL Server will try to keep them in memory. However, memory pressure on the server can cause the contents of both to be pushed to disk.

Additional Information





Comments For This Article




Wednesday, September 5, 2018 - 7:42:56 AM - Greg Robidoux Back To Top (77367)

Hi Philip, just did a test with this on SQL Server 2017 and you can see the execution plan does use the indexes.

-Greg


Wednesday, September 5, 2018 - 7:06:53 AM - Philip van Gass Back To Top (77366)

In your example you have defined the table variable as follows:

declare @temp table(ProductID int, SalesOrderID int, SalesOrderDetailID int, OrderQty smallint, primary key clustered (SalesOrderID,SalesOrderDetailID), unique nonclustered (ProductID,SalesOrderID,SalesOrderDetailID))

But in Tim Ford's article he clearly states that table variables are not allowed to have non-clustered indexes defined on them, which conflicts with your definition above. Please explain ?















get free sql tips
agree to terms