Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server Temp Table vs Table Variable



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.


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

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

Last Update: 9/10/2011

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Wednesday, September 05, 2018 - 7:42:56 AM - Greg Robidoux Back To Top

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


Wednesday, September 05, 2018 - 7:06:53 AM - Philip van Gass Back To Top

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 ?

Learn more about SQL Server tools