Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Temporary Tables and Table Variables



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






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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools