Temporary Tables, Table Variables, Table Expressions - Part 2/4

Table Variables
Many would agree that table variables are some of the least understood T-SQL elements. There are manny myths asociated with their use such as table variables being purely memory-resident (not having physical representation) and that/therefore are always preferable to temporary tables. I'd like to explain why these myths are unfounded and explain some situations where you might want to use or not use table variables.

Limitations
Unlike physical abd temporary tables there ae certain limitations placed on table variables.

• you cannot make explicit indices on table variables, only PRIMARY KEY, and UNIQUE constraints (CREATE UNIQUE INDEX). In fact you can'tcreate non-unique indices.
• you cannot change the structure or table definition once it is declared
• you cannot issue SELECT INTO statement against a table variable( however you can use INSERT EXEC).
• you cannot qualify a column name with a table variable name
• in queries that modify table variables, parallel plans will not be used.

tempdb
Despite popular belief, table variables do have physical representation in tempdb, similar to temporary tables.

Scope and Visibility
The scope of table variables is very well defined, and it is the same as any other variable: the current level and within the current batch only. You cannot access a table variable within innr levels of the call stack, and not even within the other batches within the same level. The limited scope will likely be an important deciding factor when determining whether or not to use temporary tables or table variables.

Transaction Context
Unlike a temporary table, a table variable is not part of an outer transaction; rather the transaction scope is limited to the statement level in order to support statement rollback. When you modify a table variable and the statement is aborted, the paticular statement is undone. However, if the outer transaction for tht statement is undone after the statement is finished, the changes will not be undone. Table variables are pretty unique in this respect and we can use this property to our advantage. Because table variables require less locking and logging there are obvious performance benefits

Statistics
The main factor in choosing wheter or not to use table variables is that the query optimizer does not create distribution statistics or maintain accurate cardinality information. Therefore queries against the table variable, will not use an efficient plan which will obviously be a big problem when you work with larger tables. The upside to using table variables is the loss of overhead from calculating these statistics and having to deal with triggered recompilation.

In the next article I'll discuss table expressions, how they work and when you'll want to use these instead of temporary tables and table variables.

Temporary Tables (#Tables)
Table Variables (@Tables)
Table Expressions (CTEs)