Temporary tables and table variables in SQL Server

Author: drillchina, source: blog, responsible editor: Li shuqin, in SQL Server performance tuning, there is an incomparable problem: that is, how to take a long time Code Or are temporary datasets processed in frequently called code? Table variables and temporary tables are two options.

In SQL server performance tuning, there is an incomparable problem: that is, how to process temporary datasets in a code that requires a long time or frequently called code? Table variables and temporary tables are two options. I remember seeing a large number of temporary data set processing requirements when I evaluated and optimized SQL Server application performance for a leading Chinese shipping company, their developers cannot determine when to use temporary tables and when to use table variables, so they simply use temporary tables. In fact, both temporary tables and table variables have specific application environments.

First, let's get some basic knowledge:

Table Variables

All variables are prefixed with "@" or "@". Table variables are one type of variables. Another variable is called a scalar (which can be understood as a standard variable, which is a variable of the standard data type, for example, integer Int or datetime ). The table variables with the @ prefix are local, so they can only be accessed in the current user session. The table variables with the @ prefix are global and usually system variables, for example, @ error indicates the error number of a recent T-SQL statement. Of course, because a table variable is a variable first, it can only survive in one batch, that is, the boundary we call. If the boundary is exceeded, the table variable will die.

Table variables are stored in the memory, because SQL server does not need to generate logs when all users access table variables. At the same time, variables do not need to consider other session access issues, so they do not need the lock mechanism. For very busy systems, avoiding the use of the lock can reduce part of the system load.

Another restriction on table variables is that indexes cannot be created. Of course, there is no statistical data problem, therefore, when users access table variables, there is no Execution Plan Selection Problem (that is, they think there is no optimization stage after the compilation stage). This feature is a good thing sometimes, in some cases, it may cause some trouble.

Temporary table

Temporary objects are prefixed with # Or #. Temporary tables are temporary objects and temporary objects such as temporary stored procedures and temporary functions, temporary objects are stored in tempdb. Temporary tables with a prefix of # are local, so they can only be accessed in the current user session, while temporary tables with a prefix of # are global, so all user sessions can be accessed. A temporary table uses sessions as the boundary. As long as the session for creating a temporary table is not completed, the temporary table will continue to exist. Of course, you can use the drop table command to destroy the temporary table in the session.

As we have mentioned before, temporary tables are stored in tempdb, so access to temporary tables may cause physical Io. Of course, logs need to be generated during modification to ensure consistency, the lock mechanism is also indispensable.

Another notable difference with table variables is that temporary tables can create indexes or define statistical data. Therefore, SQL Server needs to consider execution plan optimization when processing statements that access temporary tables.

Table variable vs. Temporary table

	Table Variables	Temporary table
Data Set storage location	Memory (not considering switching to page files)	Disk (cache to memory after access is not considered)
Logs required?	No	Yes
Can I create an index?	No	Yes
Can statistics be used?	No	Yes
Whether it can be accessed in multiple sessions	No	Yes
Lock required?	No	Yes

Conclusion

In summary, we will find that there are many differences in the underlying processing mechanism between temporary tables and table variables.

To sum up, we recommend that you use table variables for small temporary computation datasets. If the dataset is large, if it is used for temporary computing in the code, and such temporary use is always a simple full dataset scan without any optimization, for example, table variables can be used if no group or few groups are aggregated (such as Count, sum, average, and Max. Another consideration for using Table variables is the memory pressure in the application environment. If there are many running instances of code, pay special attention to the memory consumption caused by memory variables.

We recommend that you use temporary tables for large datasets, create indexes at the same time, or use the statisitcs automatic creation and maintenance function to optimize access to SQL statements. If you need to exchange data between multiple user sessions, the temporary table is the only choice. It should be mentioned that the temporary tables are stored in tempdb, so you should pay attention to the optimization of tempdb.

SQL Server temporary tables and table Variables

Today, when I talked to a software company developer about database design optimization, I also discussed table variables and temporary tables. I think this is indeed a highly controversial issue.

In fact, from the last post that posted table variables and temporary tables. 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.

Temporary tables cannot be supported in functions because the function cannot make permanent changes to the resource status outside the function scope, which is also called sideeffect in sqlserver ). 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 example, the following code: (of course, this is not much practical)

Declare @ M nvarchar (max)

Set @ M = N "declare @ t table (id int); insert into @ tvalues (1); select * From @ t"

Exec sp_executesql @ M

Author: drillchina

From:

Http://database.ctocio.com.cn/tips/442/8206442.shtml

Http://it.hexun.com/2008-07-09/107302733.html