[Reprinted] differences between temporary tables and table variables in SQL Server

1,

Reprinted: http://database.ctocio.com.cn/tips/442/8206442.shtml

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.

 

2,

Reprinted: http://blog.csdn.net/lovehongyun/archive/2008/01/09/2031260.aspx

 

Question 1: Why do I need to introduce table variables when a temporary table already exists?

Solution 1: Compared with a temporary Table, Table variables have the following advantages: • as described in Table, Table variables (such as local variables) with a clearly defined range, these table variables are automatically cleared at the end of the range.
• Compared with temporary tables, table variables cause less re-Compilation of stored procedures.
• Transactions involving table variables only maintain the update duration on table variables. Therefore, when using Table variables, You need to lock and record resources in less cases. Because table variables have a limited range and are not part of a persistent database, transaction rollback does not affect them.
Question 2: What does it mean if the use of table variables is less than the use of temporary tables resulting in re-Compilation of stored procedures?

Answer 2: The following article discusses the reasons for re-compiling the stored procedure:

243586 (http://support.microsoft.com/kb/243586/) Stored Procedure recompilation troubleshooting
The "recompilation caused by some temporary table operations" section also lists the requirements that need to be met to avoid some problems (for example, re-compilation due to the use of temporary tables. These restrictions are not applicable to table variables.

Table variables are completely independent from the batches used to CREATE these table variables. Therefore, when the CREATE or ALTER statement is executed, no "re-parsing" will occur ", when a temporary table is used, "re-parsing" may occur ". The temporary table needs to be reparsed to reference the table from the nested stored procedure. Table variables completely avoid this problem. Therefore, stored procedures can use compiled plans to save resources for processing stored procedures.

Question 3: What are the defects of table variables?

Solution 3: Compared with a temporary table, it has the following drawbacks: • non-clustered indexes cannot be created on table variables (except for system indexes created for the PRIMARY or UNIQUE constraints ). Compared with temporary tables with non-clustered indexes, this may affect query performance.
• Table variables do not maintain statistical information as temporary tables do. In table variables, statistical information cannot be created automatically or using the create statistics statement. Therefore, when performing complex queries on large tables, the lack of statistical information may affect the optimizer's determination of the optimal query plan, thus affecting the query performance.
• The table definition cannot be changed after the initial DECLARE statement.
• Table variables cannot be used in insert exec or select into statements.
• Check constraints, default values, and calculated columns in the table type declaration cannot call user-defined functions.
• If a table variable is created outside the EXEC statement or sp_executesql stored procedure, you cannot use the EXEC statement or sp_executesql stored procedure to run a dynamic SQL Server query that references the table variable. Because table variables can only be referenced in their local scopes, EXEC statements and sp_executesql stored procedures are outside the scope of table variables. However, you can create table variables and execute all processing in the EXEC statement or sp_executesql stored procedure, because the local scope of the table variables will be in the EXEC statement or sp_executesql stored procedure.
Problem 4: Compared with a temporary or permanent table, the table variable only exists in the memory structure to ensure better performance, is it because they are maintained in databases residing on physical disks?

Solution 4: The table variable does not exist only in the memory structure. Because the table variables may retain a large amount of data and cannot accommodate the data in the memory, it must have a location on the disk to store the data. Similar to a temporary table, table variables are created in the tempdb database. If enough memory is available, both the table variables and temporary tables are created and processed in the memory (data cache.

Question 5: Must I use table variables instead of temporary tables?

Answer 5: The answer depends on three factors: • number of rows inserted into the table.
• The number of times the query is recompiled.
• Query types and their dependence on performance indexes and statistical information.
In some cases, you can split a stored procedure with a temporary table into multiple smaller stored procedures for recompilation on smaller units.

Table variables should be used whenever possible unless the data volume is large and the table needs to be reused. In this case, you can create an index on a temporary table to improve query performance. However, different solutions may be different. Microsoft recommends that you perform a test to verify whether table variables are more effective for specific queries or stored procedures than temporary tables.