By: Armando Prato
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.
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.
