Share via

Queries with table variables are slow in SQL2005 compared with SQL2000 (Table Variables (VS) Temp Tables)

  • Article

Table Variables (VS) Temp Tables

SQLServer2005 caches temp tables and temp variables only under some conditions.
Scenarios where temp table/variable are not cached (see below) may cause performance degradation as compared to SQLServer2000.

Following are scenarios where temp table/variable are not cached:
1. select into #t
2. alter table #t
3. create index on #t
4. Global temp tables (##t)
5. Local temp tables on adhoc level (nest level 0)
6. table variables are also not cached for dynamic SQL.

 What are some of the drawbacks of table variables?

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

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 temporary or permanent tables
are maintained in a database that resides on the physical disk and also logged?

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 and are they joined with other tables.
The number of recompilations the query is saved from.
The type of queries and their dependency on indexes and statistics for performance.

Regards

Karthick P.K

Comments