SQL Server temporary tables and table variables (2)

In fact, there are some differences between table variables and temporary tables, but the most fundamental difference is that

Storage Requirements: both table variables and temporary tables consume storage space in tempdb. However, when data is updated, table variables do not write logs, while temporary tables write logs. (This is a script test. Table variables are not written in memory as we think, but not in tempdb .)

Optimization support: Table variables do not support indexes and statistical data, while temporary tables support indexes and statistical data.

Table variables or temporary tables are usually needed to support the temporary computing result set, so there are some common situations:

If the temporary result set only needs to write data to it, for example, you can use a loop to search for the relevant data multiple times and synthesize a temporary result set, then you can use table variables. (Some people mentioned that sorting is required when the returned result set is returned, but table variables do not support indexing. In fact, this does not matter. Although Table variables do not support indexes, table variables support primary keys. Therefore, you can use primary keys to replace indexes .)

If the temporary result set does not need to be changed much, but serves as a temporary associated dataset to join various datasets ), then indexes and statistical data may be more suitable (of course, this temporary result set must be large enough so that the cost of indexes and statistical data can be compensated ).

Because table variables do not support statistical data, using Table variables in a stored procedure can reduce recompilation problems caused by data changes.

Of course, in addition to the obvious restrictions on indexes and statistical data, table variables do not support parallel execution plans. Therefore, table variables are not a good choice for large temporary result sets.

In the previous post on table variables and temporary tables, a friend of robi_xu mentioned the problem of selecting table variables and temporary tables.

The temporary table cannot be supported in functions because the function cannot permanently change the resource status outside the function scope, which is also called side effect in SQL Server ). However, it is not recommended to use a large temporary result set in a function, because placing such a function in a query may cause obvious performance problems, therefore, Batch scripts such as stored procedures are generally used in this case.

The reason why dynamic scripts do not support table variables is that the stored procedure does not accept table-type parameters. However, if the declaration and value assignment of table variables are in the sp_executesql parameter, sp_executesql can be executed, because the table variables are stored in the stmt parameter of sp_executesql and do not need to be passed in, for exampleCode(Of course, this is not much practical)

Declare @ M nvarchar (max)

Set @ M = n' declare @ t table (id int); insert into @ T values (1); select * From @ t t'

Exec sp_executesql @ M