"T-SQL Series" Temp table, table variable

Original: "T-SQL Series" Temp table, table variable

Temp table
A temporary table is similar to a permanent table, except that it is created in tempdb and only disappears after the end of a database connection or by a SQL command drop, otherwise it will persist. Temporary tables generate the system logs of SQL Server when they are created, although they are in tempdb and are allocated in memory, they also support physical disks, but the user cannot see the files on the specified disk.
Temporary tables are both local and global, and the name of the local temporary table is prefixed with "#", which is visible only in the local current user connection and is deleted when the user disconnects from the instance. The names of global temporary tables are prefixed with "# #" and are visible to any user after they are created, and are deleted when all users referencing the table are disconnected.
Temporary tables can create indexes or define statistics, so you can use Data Definition language (DDL) declarations to block restrictions, constraints, and referential integrity, such as primary key and foreign key constraints, that temporary tables Add.
Temporary tables can modify many of the defined options after they are created, including:
1) Add, modify, and delete columns. For example, the name, length, data type, precision, scale, and nullability of a column can be modified, but there are some limitations.
2) You can add or remove primary key and foreign key constraints.
3) You can add or remove UNIQUE and CHECK constraints and DEFAULT definitions (objects).
4) You can use the IDENTITY or ROWGUIDCOL property to add or remove identifier columns. Although the ROWGUIDCOL property can be added to an existing column or deleted from an existing column, there can be only one column in the table that can have that property at any time.
5) The columns selected in the table and table are registered as full-text indexes.
Comparing temporary tables and table variables can be done through SQL selection, insert, UPDATE, and DELETE statements, and their differences are mainly reflected in the following:
1) The table variable is stored in memory, when the user accesses the table variable, SQL Server does not generate the log, and in the temporary table is generated log;
2) in a table variable, a nonclustered index is not allowed;
3) The table variable is not allowed to have default defaults, and does not allow constraints;
4) The statistical information on the temporary table is sound and reliable, but the statistics on the table variable are unreliable;
5) There is a locking mechanism in the temporal table, and there is no mechanism for locking in the table variable.

Table Variables
Basic principle: can use table variable to use table variable. Not really. Use temporary tables
Table variables are primarily memory for overhead systems, while temporary tables use tempdb. Table variables can be used for intermediate data storage of small amounts of data, and temporary tables are recommended when data that needs to be temporarily saved is large.
A table variable creates a syntax similar to a temporary table, and the difference is that it must be named when it is created. Table variables are one of the variables, the table variable is also divided into local and global two, the name of the surface variable is "@" prefix, only in the local current user connection can be accessed. The name of the global table variable is prefixed with "@@", which is generally the global variable of the system, as we commonly use, such as @ @Error represents the wrong number, @ @RowCount represents the number of rows affected.
1. Why to use Table variables
Table variables are introduced from 2000, and Microsoft believes that table variables have the following advantages over local temporal tables:
A. As with other variables, table variables have a well defined range and are automatically cleared;
B. Using table variables in stored procedures reduces the occurrence of stored procedure recompilation;
C. Table variables require fewer lock requests and log resources;
D. Udf,uddt,xml can be used on table variables.
2. Limitations of Table Variables
Table variables have the following drawbacks compared to temporary tables:
A. There are no statistics on the table variables, and the query optimizer chooses the execution plan based on a fixed pre-estimate, which in many cases causes the query optimizer to choose a poor execution plan;
B. You cannot create an index directly on a table variable, but you can create an index by creating a constraint (primary key, unique);
C. After declare, the table variable can no longer be changed;
D. Cannot perform insert EXEC on table variable, SELECT into statement (only for versions prior to 05);
E. Dynamic SQL statements that involve table variables cannot be executed through exec or sp_executesql, but can be if the table variable is defined within a dynamic SQL statement.
3. When can I use the table variable
To use a table variable should be judged according to the following rules:
A. The number of rows in the table;
B. The number of recompilation that can be reduced by using table variables;
C. The type of query and the degree of dependence on the index or statistical information;
D. When it is necessary to use Udf,uddt,xml.
In fact, we must start from the actual situation, according to the specific query, make specific choices. However, it is critical that, if the table has a very long number of rows, the use of table variables is actually more resource-consuming.It has been suggested that table variables can be used in cases where the number of rows is less (less than 1000 rows), and temporary tables if there are many rows (with tens of thousands of rows).
Therefore, in the actual development, the decision should be made by comparing the use of temporary tables or table variables, respectively.
4. Mistakes in using table variables
For table variables, many people think that table variables, like other variables, exist only in memory, which is not true, and table variables exist in tempdb. This can be compared using the example below.

CREATE TABLE INT )DECLARE@TableVariableTABLEINT  )SELECTTOP  2        *from    tempdb.sys.objectsORDERby  DESCDROPTABLE #TempTable

5. Other
Because the scope of a table variable is limited and is not part of a persistent database, it is not affected by transaction rollback.
Table variables are not affected by rollback, and in some cases the integrity of the data is compromised.

CREATE TABLE#TempTable (tt_col1INT )DECLARE @TableVariable TABLE(Tv_col1INT )INSERT#TempTableVALUES(1 )INSERT  @TableVariableVALUES(1 )BEGIN TRANSACTION INSERT#TempTableVALUES(2 )INSERT  @TableVariableVALUES(2 )ROLLBACK SELECT  * from#TempTableSELECT  * from    @TableVariableDROP TABLE#TempTable

"T-SQL Series" Temp table, table variable