What are the advantages and disadvantages of table variables and temporary tables?

When can I use table variables? Under what circumstances do I use a temporary table?

Table variables:

Declare @ TB table (ID int identity (1, 1), name varchar (100 ))

Insert @ TB

Select ID, name from mytable where name like 'zhang %'

 

Temporary table:

Select name, address
Into # Ta from mytable
Where name like 'zhang %'

(If exists (select * From tempdb. DBO. sysobjects where id = object_id (N 'tempdb .. # Ta') and type = 'U ')
Drop table # Ta)

 

Comparison between table variables and temporary tables:

• Temporary tables use hard disks (tempdb database), and table name variables occupy memory. Therefore, small data volumes are faster than table variables in memory. When there is a large amount of data, you cannot use table variables, which consumes too much memory. Temporary tables are suitable for large data volumes.
• Table variables are stored in the memory by default, which is fast. Therefore, if the data volume in the trigger and stored procedure is small, table variables should be used.
• The temporary table uses the hard disk by default. Generally, the speed is relatively slow. Is there no need to use the temporary table? No. When the data volume is large, if the table variable is used up, the memory will be exhausted and the tempdb space will be used up. In this way, the hard disk space will be used up, but the memory will be basically exhausted at the same time, increases the chance of memory calling and calling, but reduces the speed. In this case, we recommend that you allocate appropriate space for tempdb and then use a temporary table.
• For temporary tables, the table variables mainly take longer I/O time, but are less occupied by memory resources. When the data volume is large, the use of temporary tables has better performance than the table variables due to the low consumption of memory resources.
• Suggestion: Use table variables for triggers and user-defined functions. Most table variables are used for stored procedures. Temporary tables are used for special applications and large data volumes.
• Table variables have a clear scope. When the function, stored procedure, or batch processing of the table variables are defined, the table variables are automatically cleared.
• Using table variables in a stored procedure reduces the amount of recompilation in a stored procedure compared to using a temporary table.
• Transactions involving table variables only exist during table variable update. This reduces the need for table variables to lock and record resources.
• Table variables need to know the table structure in advance. For normal temporary tables, you can only use the same into as the table variables in the current session for convenience. Global temporary tables: you can use the drop to be displayed in temp in multiple sessions. (It is easier to create a temporary table without knowing the table structure)
• The global temporary table function cannot be achieved by table variables.
• Table variables do not need to be deleted, so there will be no naming conflicts. Temporary tables, especially global temporary tables, must be used to resolve naming conflicts.
• Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
• When creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid logs and increase the speed. If the data volume is small, in order to ease the system table resources, we recommend that you first create table and then insert.
• If the temporary table has a large amount of data and requires an index, you should place the process of creating a temporary table and creating an index in a single sub-storage process, in this way, the system can use the index of the temporary table.
• If a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.
• Exercise caution when using large temporary tables to connect to other large tables for query and modification, reducing the burden on the system table, because this operation will use the tempdb system table multiple times in a statement.

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

Solution 1:Compared with temporary tables, table variables have the following advantages:

•	As described in the SQL Server books online "tables" article, table variables (such as local variables) have clearly defined ranges and are automatically cleared when the range ends.
•	Compared with temporary tables, table variables cause less re-Compilation of stored procedures.
•	Transactions involving table variables only maintain the update duration on the 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?

Solution 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 defects:

•	Non-clustered indexes cannot be created on table variables (except system indexes created for 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.
•	The check constraints, default values, and calculated columns in the table type declaration cannot call user-defined functions.
•	If the table variable is in the exec statement orSp_executesqlThe exec statement orSp_executesqlStored Procedures to run the dynamic SQL Server query that references the table variable. Because table variables can only be referenced in their local scopes, exec statements andSp_executesqlStored Procedures will be out of the scope of table variables. However, in the exec statement orSp_executesqlCreate Table variables and execute all the processing in the stored procedure, because the local scope of the table variables will be in the exec statement orSp_executesqlStored in the process.

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, the table variable is inTempdbCreated in the 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?

Solution 5:The answer depends on the following three factors:

•	The number of rows inserted to the table.
•	The number of times the query is recompiled.
•	Query type and its dependence on performance index 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.