SQL temp tables and table variables

Tag:ar    use    sp    data    bs   sql    nbsp   c    information    

1. Why Use Table variables

Table variables are introduced from 2000, and Microsoft believes that table variables have the following advantages over local temporal tables:
A. As with other variables, table variables have a well defined range and are automatically cleared;
B. Using table variables in stored procedures reduces the occurrence of stored procedure recompilation;
C. Table variables require fewer lock requests and log resources;
D. Udf,uddt,xml can be used on table variables.

2. Limitations of Table variables

Table variables have the following drawbacks compared to temporary tables:
A. There are no statistics on the table variables, and the query optimizer chooses the execution plan based on a fixed pre-estimate, which in many cases causes the query optimizer to choose a poor execution plan;
B. You cannot create an index directly on a table variable, but you can create an index by creating a constraint (primary key, unique);
C. After declare, the table variable can no longer be changed;
D. You cannot execute an INSERT Exec,select into statement on a table variable;
E. Dynamic SQL statements that involve table variables cannot be executed through exec or sp_executesql, but can be if the table variable is defined within a dynamic SQL statement.

3. When can I use the table variable

To use a table variable should be judged according to the following rules:
A. The number of rows in the table;
B. The number of recompilation that can be reduced by using table variables;
C. The type of query and the degree of dependence on the index or statistical information;
D. When it is necessary to use Udf,uddt,xml.
In fact, we must start from the actual situation, according to the specific query, make specific choices. However, it is critical that, if the table has a very long number of rows, the use of table variables is actually more resource-consuming. It has been suggested that table variables can be used in cases where the number of rows is less (less than 1000 rows), and temporary tables if there are many rows (with tens of thousands of rows).

SQL temp tables and table variables