Questions and answer on Table variables

Why were table variables introduced when temporary tables were already available?

=> Table variables have the following advantages over temporary tables:

• Table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
• Table variables result in fewer recompilation of a stored procedure as compared to temporary tables.
• Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

What does it mean by saying that table variables result in fewer recompilations of a stored procedure than when temporary tables are used?

=> The following article discusses some reasons when stored procedures are recompiled:

The "Recompilations Due to Certain Temporary Table Operations" section also lists some requirements to avoid such as a recompilation because of temporary tables. These restrictions do not apply to table variables.

Table variables are completely isolated to the batch that creates them so no 're-resolution' has to occur when a CREATE or ALTER statement takes place, which may occur with a temporary table. Temporary tables need this 're-resolution' so the table can be referenced from a nested stored procedure. Table variables avoid this completely so stored procedures can use plan that is already compiled, thus saving resources to process the stored procedure.

What are some of the drawbacks of table variables?

=> These are some of the drawbacks as compared to temporary tables:

• Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
• Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
• The table definition cannot be changed after the initial DECLARE statement.
• Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
• CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
• You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

=> A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Do I have to use table variables instead of temporary tables?

=> The answer depends on these three factors:

•  The number of rows that are inserted to the table.
• The number of recompilations the query is saved from.
• The type of queries and their dependency on indexes and statistics for performance.

In some situations, breaking a stored procedure with temporary tables into smaller stored procedures so that recompilation takes place on smaller units is helpful.

In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure.

About Author:

Pushkar Rathod is a consultant in Systems Plus Pvt. Ltd. Within Systems Plus, he actively contributes to the areas of Technology and Information Security. He can be contacted at pushkar.r@spluspl.com