Table Variables

Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. In many cases a table variable can outperform a solution using a temporary table, although we will need to review the strengths and weaknesses.

DECLARE @ProductTotals TABLE
(
  ProductID int, 
  Revenue money
)

INSERT INTO @ProductTotals (ProductID, Revenue)
  SELECT ProductID, SUM(UnitPrice * Quantity)
    FROM [Order Details]
    GROUP BY ProductID
UPDATE @ProductTotals
  SET Revenue = Revenue * 1.15
WHERE ProductID = 62
 
DELETE FROM @ProductTotals
WHERE ProductID = 60
 
SELECT TOP 5 * 
FROM @ProductTotals
ORDER BY Revenue DESC

Scope

Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter. In fact, a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits – there will be no table to clean up with a DROP statement.

Although you cannot use a table variable as an input or output parameter, you can return a table variable from a user-defined function – we will see an example later in this article. However, because you can’t pass a table variable to another stored procedure as input – there still are scenarios where you’ll be required to use a temporary table when using calling stored procedures from inside other stored procedures and sharing table results.

The restricted scope of a table variable gives SQL Server some liberty to perform optimizations.

Performance

Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit. For more information on why stored procedures may recompile, look at Microsoft knowledge base article 243586.

Choosing Between Temporary Tables and Table Variables

Now you’ve come to a stored procedure that needs temporary resultset storage. Knowing what we have learned so far, how do you decide on using a table variable or a temporary table?

First, we know there are situations that which demand the use of a temporary table. This in-cludes calling nested stored procedures which use the resultset, certain scenarios using dy-namic SQL, and cases where you need transaction rollback support.

Secondly, the size of the resultset will determine which solution to choose. If the table stores a resultset so large you require indexes to improve query performance, you’ll need to stick to a temporary table. In some borderline cases try some performance benchmark testing to see which approach offers the best performance. If the resultset is small, the table variable is always the optimum choice.