[SQL] Table variables and temporary tables

1. Why use table variables?

 

Table variables are introduced from 2000. Microsoft believes that compared with local temporary tables, table variables have the following advantages:

A. Like other variables, table variables have a well-defined range and will be automatically cleared;
B. Using table variables in a stored procedure will reduce the re-Compilation of the stored procedure;
C. Table variables require fewer lock requests and log resources;
D. You can use UDF, UDDT, and XML on table variables.

2. Table variable restrictions

Compared with temporary tables, table variables have the following Disadvantages:
A. There is no statistical information on the table variables. The query optimizer selects an execution plan based on a fixed estimated value. In the case of a large amount of data, the query optimizer selects a poor execution plan;
B. You cannot create indexes directly on table variables, but you can create indexes by creating constraints (primary key and unique;
C. After DECLARE, you cannot change the table variables;
D. The insert exec statement cannot be executed on the table variables. select into Statement (only for versions earlier than 05 );
E. You cannot use EXEC or sp_executesql to execute dynamic SQL statements that involve table variables. However, if the table variables are defined in dynamic SQL statements, you can.

3. When can I use table variables?

Table variables should be determined based on the following rules:
A. number of rows in the table;
B. Use table variables to reduce the number of recompilation times;
C. The type of the query and the degree of dependency on the index or statistical information;
D. When UDF, UDDT, and XML need to be generated.
In fact, you have to make a specific choice based on the actual query. However, the key point is that if the number of rows in a table is large, the use of table variables is actually more resource-consuming. Someone suggested that you use table variables when the number of rows is small (less than 1000 rows). If the number of rows is large (tens of thousands of rows), use a temporary table.

Therefore, in actual development, we should use temporary tables or table variables for comparison before making a decision.

The following is an example. There are more than 0.2 million rows of data inserted into the temporary table and table variables. We can see that the time used for temporary tables is 1/5 of the time spent on table variables.

Table 'salesorderheader '. Scan count 3, logical reads 130 times, physical reads 9 times, pre-reads 43 times, lob logic reads 0 times, lob physical reads 0 times, and lob pre-reads 0 times.
Table '# SalesOrderDetail ___________________________________________________________________________________________________________ 00000000001F '. Scan count 3, logical reads 12331, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
Table 'worktable '. Scan count 0, logical read 0, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.

SQL Server execution time:
CPU time = 2281 milliseconds, occupied time = 19726 milliseconds.
Select with temporary table: 20140 MS

**************************************** ****************************************

Table 'salesorderheader '. Scan count 0, logical read 764850, physical read 17, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.
Table '#4E88ABD4 '. Scan count 1, logical reads 12331, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.

SQL Server execution time:
CPU time = 4375 milliseconds, occupied time = 107160 milliseconds.
Select with table variable: 107160 MS

4. misunderstandings about using Table Variables

Many people think that, like other variables, table variables only exist in memory. In fact, this is incorrect, and table variables also exist in tempdb. You can use the following example for comparison.

Create table # TempTable (TT_Col1 INT)

DECLARE @ TableVariable TABLE (TV _Col1 INT)

Select top 2 *

FROM tempdb. sys. objects

Order by create_date DESC

Name
-- Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# 03317E3D
# TempTable ______________________________________________________________________________________________________________ 000000000003

# 03317E3D is the created table variable;

5. Others

Table variables are not affected by rollback, which may damage data integrity in some cases.

Create table # TempTable (TT_Col1 INT)
DECLARE @ TableVariable TABLE (TV _Col1 INT)
INSERT # TempTable VALUES (1)
INSERT @ TableVariable VALUES (1)
BEGIN TRANSACTION
INSERT # TempTable VALUES (2)
INSERT @ TableVariable VALUES (2)
ROLLBACK
SELECT * FROM # TempTable

/*
TT_Col1
-------
1
*/

SELECT * FROM @ TableVariable
-- Two records are returned.
/*
TV _Col1
-------
1
2
*/