A
Table Variable, like any other
variable, the scoping rules of the table variable are similar to any other
programming variables. For example, if you define a variable inside a stored
procedure, it can’t be accessed outside the stored procedure. A table variable
can be very powerful when used with stored procedures to pass it as
input/output parameters (new functionality available starting with SQL Server
2008) or to store the result of a table valued function.
·
The
table variable is NOT necessarily memory resident. Under memory pressure, the
pages belonging to a table variable can be pushed out to tempdb.
·
When
you create a table variable, it is like a regular DDL operation and its
metadata is stored in system catalog.
·
Table
variables don’t participate in transactions or locking.
·
The
operations done on table variable are not logged.
·
No
DDL is allowed on table variables. So if you have a large rowset which needs to
be queried often, you may want to use #table when possible so that you can
create appropriate indexes. You can get around this by creating unique
constraints when declaring table variable.
·
Finally,
no statistics is maintained on table variable which means that any changes in
data impacting table variable will not cause recompilation of queries accessing
table variable.
Difference between table variable and temp table:
·
In
functions it is only possible to use a table variable and if you need to write
to the table in a child scope then only a #temp table will do (table valued
parameters allow readonly access).
·
Indexes: If you need an index that cannot be
created implicitly through a UNIQUE or PRIMARY KEY constraint then you need a #temporary
table as it is not possible to create these on table variables. (Examples of such
indexes are non unique ones, filtered indexes or indexes with INCLUDEd
columns). NB: SQL Server 2014 will allow non unique indexes to be declared
inline for table variables.
·
If
you will be repeatedly adding and deleting large numbers of rows from the table
then use a #temporary table. That supports TRUNCATE which is more efficient
than DELETE and additionally subsequent inserts following a TRUNCATE can have
better performance than those following a DELETE as illustrated here.
·
If
the optimal plan using the table will vary dependent on data then use a #temporary
table. That supports creation of statistics which allows the plan to be
dynamically recompiled according to the data (though for cached temporary
tables in stored procedures the recompilation behaviour needs to be understood
separately).
·
If
the optimal plan for the query using the table is unlikely to ever change then
you may consider a table variable to skip the overhead of statistics creation
and recompiles (would possibly require hints to fix the plan you want).
·
If
the source for the data inserted to the table is from a potentially expensive SELECT
statement then consider that using a table variable will block the possibility
of this using a parallel plan.
·
If
you need the data in the table to survive a rollback of an outer user
transaction then use a table variable. A possible use case for this might be
logging progress of different steps in a long SQL batch.
·
When
using a #temp table within a user transaction locks are held longer than for
table variables and also it can prevent truncation of the tempdb transaction
log until the user transaction ends. So this might favour the use of table
variables.
·
Within
stored routines both table variables and temporary tables can be cached. The
metadata maintenance for cached table variables is less than that for #temporary
tables. Bob Ward points out in his tempdb presentation that this can cause
additional contention on system tables under conditions of high concurrency.
Additionally when dealing with small quantities of data this can make a
measurable difference to performance.
References:
