Differences between SQL Server table variables and temporary tables

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:
• Table variables (such as local variables) have clearly defined ranges, as described in the SQL Server books online table, 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 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.
• 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 the following 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.