Comparison between table variables and temporary tables

Many people are very confused about what table variables are (not the same as table variables) and comparison with temporary tables. Although I already have a lot of articles about them online, I have not found a comprehensive article. In this article, we will explore what table variables and temporary tables are (and not), and then decrypt them by using temporary tables and table variables.

 

Table Variables

Table variables are introduced in SQL Server 2000 for the first time. What are table variables? Microsoft defines it as a variable of the table type in BOL (declare @ local_variable. Its specific definitions include column definitions, column names, data types, and constraints. The constraints that can be used in table variables include primary key constraints, unique constraints, null constraints, and check constraints (foreign key constraints cannot be used in table variables ). statements defining table variables are a subset of table statements that are normally defined using create table. Only the table variables are defined using the declare @ local_variable statement.

 

You can see through reference 1:

1) Table variables have specific scopes (in the current batch processing statement, but not in any stored procedures and functions called by the current batch processing Statement ), table variables are automatically cleared after the batch processing is completed.

 

2) refer to the "recompilations due to certain temporary table operations" section in section 6 to discuss various causes for temporary tables to force repeated compilation of stored procedures, but these causes are not applicable to table variables. Compared with temporary tables, table variables produce less stored procedure re-compilation.

 

3) transactions targeting table variables take effect only when updating data, so the number of locks and logs generated is less.

 

4) because the scope of table variables is so small and does not belong to the persistent part of the database, transaction rollback does not affect table variables.

 

Table variables can be used in the same scope as normal tables. More specifically, table variables can be used in select, delete, update, and insert statements as normal tables or table expressions. However, table variables cannot be used in statements such as "select select_list into table_variable. In SQL Server 2000, table variables cannot be used in statements such as "insert into table_variable exec stored_procedure.

 

Table variables cannot do the following:

1. Although a table variable is a variable, it cannot be assigned to another variable.

2. Check constraints, default values, and calculation Columns cannot reference user-defined functions.

3. constraints cannot be named.

4. Cannot truncate table Variables

5. explicit values cannot be inserted into the ID column (that is, the table variable does not support set identity_insert on)

Temporary table

Before going into the temporary table, we should first discuss sessions. A session is just a connection from the client to the Data Engine. In SQL Server Management Studio (SSMs), each query window is connected to the database engine. An application can establish one or more connections to the database. In addition, the application may establish connections and never release until the application ends, you may also need to establish a connection when releasing the connection.

So what is a temporary table? In the create table, we can know that the temporary table and the table created with the create table statement have the same physical structure, but the temporary table and the normal table have the following differences:

 

1) The name of a temporary table cannot exceed 116 characters. This is because the database engine automatically adds a string after the name of a temporary table to identify different sessions for creating different temporary tables.

 

2) The scope of a local temporary table (named after "#") is only in the current connection. From the perspective of creating a local temporary table in the stored procedure, partial temporary tables are dropped in the following situations:

A. explicitly call the drop TABLE statement

B. When a local temporary table is created in the stored procedure, the stored procedure ends, which means that the local temporary table is dropped.

C. When the current session ends, all local temporary tables created in the session will be dropped.

 

3) The global temporary table (named after "#") is visible in all sessions. Therefore, check whether the global temporary table exists before creating it. Otherwise, if it already exists, you will get the error of repeated object creation.

A. The global temporary table is dropped after the session is created. Other sessions cannot reference the global temporary table.

B. The reference is performed at the statement level, for example, the following example:

I. Create a New query window and run the following statement:

     create table ##temp (RowID int)

Ii. enable a new query creation again. Use the following statement to reference the global temporary table every 5 seconds.

     while 1=1 begin       select * from ##temp       waitfor delay ‘00:00:05‘     end

Iii. Return to the first window and close the window.

Iv. Errors will occur when the global temporary table is referenced in the next loop

 

4) temporary tables cannot be partitioned.

 

5) foreign key constraints cannot be added to temporary tables.

6) The data type of columns in the temporary table cannot be defined as not custom data type in tempdb (the custom data type is a database-level object, and the temporary table belongs to tempdb ), since tempdb is automatically created after each SQL Server restart, you must use startup stored procedure to create a custom data type for tempdb. You can also achieve this goal by modifying the model database.

 

7) the XML Column cannot be defined as an XML set, unless the set has been defined in tempdb.

 

You can use the create table statement or the select <select_list> into # TABLE statement to create a temporary table. You can also use statements such as "insert into # Table exec stored_procedure" for temporary tables.

Temporary tables can have naming constraints and indexes. However, when two users call the same stored procedure at the same time, the following error occurs: "There is already an object named '<objectname>' in the Database. Therefore, the best practice is to use the system-assigned unique tempdb instead of naming the created object. 6

Refer to 6. I have discussed many reasons for the re-Compilation of stored procedures due to temporary tables and the methods to avoid them.

 

Misunderstanding

Misunderstanding 1. Table variables are only in memory.

Misunderstanding 2. Temporary tables are only stored in physical media

These two points of view are both obvious misunderstandings. refer to section q4. Table variables are created in the tempdb database because the data stored in Table variables may exceed the physical memory. In addition, we found that as long as the memory is sufficient, the table variables and temporary tables will be created and processed in the memory. They can also be stored in the disk at any time.

How can we prove this? See the following code (valid in SQL Server 2000 to 2008)

-- make a list of all of the user tables currently active in the

 -- TempDB database

 if object_id(‘tempdb..#tempTables‘) is not null drop table #tempTables

 select name into #tempTables from tempdb..sysobjects where type =‘U‘

 -- prove that even this new temporary table is in the list.

 -- Note the suffix at the end of it to uniquely identify the table across sessions.

 select * from #tempTables where name like ‘#tempTables%‘

 GO

 -- create a table variable

 declare @MyTableVariable table (RowID int)

 -- show all of the new user tables in the TempDB database.

 select name from tempdb..sysobjects

  where type =‘U‘ and name not in (select name from #tempTables)

 

There are also some "Proofs" that temporary tables only exist in the memory. Here I will point out one of them:

Note that the table variable name is assigned by the system. The first character "@" of the table variable is not a letter, so it is not a valid variable name. The system will create a system assigned name for the table variable in tempdb, so any method for searching the table variable in sysobjects or SYS. Tables will fail.

The correct method should be the method in my previous example. I have seen many people use the following query table variables:

 select * from sysobjects where name like‘#tempTables%‘

The above Code seems to be very useful, but it may cause problems for multiple users. You create two connections, create a temporary table in the first connection, and run the preceding statement in the second window to see the temporary table created in the first connection, if you try to operate this temporary table in the second connection, an error may occur because this temporary table does not belong to your session.

 

Misunderstanding 3. Table variables cannot have indexes.

This misunderstanding is also wrong. Although once you create a table variable, you cannot perform DDL statements on it, including the create index statement. However, you can create an index for a table when defining a variable.

declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED) 

This statement creates a table variable with a clustered index. Because the primary key has a corresponding clustered index, a system-named index will be created in the rowid column.

The following example shows how to create a unique constraint on the column of a table variable and how to create an index.

 declare @temp TABLE (   RowID int NOT NULL,   ColA int NOT NULL,   ColB char(1)UNIQUE,   PRIMARY KEY CLUSTERED(RowID, ColA))

1) SQL cannot create statistical information for table variables, just as it can create statistical information for temporary tables. This means that the execution engine considers the table variables to have only one row, which means that the execution plan for the table variables is not optimal. Although the estimated execution plan for both the table variables and the temporary table are 1, the actual execution plan for the temporary table will change according to the re-Compilation of each stored procedure (see reference 1, q2 ). if the temporary table does not exist, an error occurs when an execution plan is generated.

 

2) As mentioned above, you must create a table variable before you can perform DDL statements on it. Therefore, if you need to create an index for the table or add a column, you need a temporary table.

 

3) Table variables cannot use select... Into statement, while the temporary table can

 

4) in SQL Server 2008, you can pass table variables as parameters to the stored procedure. But the temporary table does not work. In SQL Server 2000 and 2005, table variables do not work either.

 

5) Scope: The table variables are only valid in the current batch and invisible to any nested stored procedures. The local temporary table is only valid in the current session, which also includes nested stored procedures. But it is invisible to the parent stored procedure. The global temporary table can be seen in any session, but will be dropped as the created session ends. Other sessions cannot reference the global temporary table.

 

6) sorting rules: Table variables use the sorting rules of the current database, and temporary tables use the sorting rules of tempdb. If they are not compatible, you also need to specify them in the query or table definition (refer to 7. Table variables and temporary tables)

 

7) If you want to use table variables in dynamic SQL, you must define table variables in dynamic SQL. Temporary tables can be defined in advance and referenced in dynamic SQL.

 

So many times, how should I choose?

Microsoft recommends using Table variables (see reference 4). If the number of rows in the table is very small, the table variables are used. Many "network experts" will tell you that 100 is a demarcation line, because this is the beginning of the efficiency of statistical information to create a query plan. However, I still want to tell you how to test temporary tables and table variables based on your specific needs. Many people use table variables in user-defined functions. If you need to use primary keys and unique indexes in Table variables, you will find that table variables containing thousands of rows still have excellent performance. However, if you need to join table variables with other tables, you will find that the performance is often very poor due to inaccurate execution plans.

To prove this, please refer to the attachment in this article. The code in the attachment Creates table variables and temporary tables, and loads the sales. salesorderdetail table of the adventureworks database. To get enough test data, I inserted the data in this table 10 times. Then, the temporary table and table variables are joined with the original sales. salesorderdetail table using the modifieddate column as the condition. Io is significantly different from the statistical information. From the perspective of time, it took more than 50 seconds to join table variables, while 8 seconds for temporary tables.

If you need to perform DLL operations on the table after the table is created, select a temporary table.

There are many similarities between temporary tables and table variables. So sometimes there is no specific rule on which one to choose. For any specific situation, you need to consider their respective advantages and disadvantages and perform some performance tests. The following table provides a more detailed reference for better performance.

 

Summary

Features	Table Variables	Temporary table
Scope	Current Batch Processing	Current session, nested stored procedure, Global: All sessions
Use Cases	Custom functions, stored procedures, and Batch Processing	Custom functions, stored procedures, and Batch Processing
Creation Method	Declare statement only. It can only be created using the decleare statement.	Create table statement Select into statement.
Table Name Length	Up to 128 bytes	Up to 116 bytes
Column Type	You can use Custom Data Types You can use an XML collection.	Custom Data Types and XML sets must be defined in tempdb
Collation	String sorting rules inherit from the current database	String sorting rules are inherited from the tempdb database.
Index	The index must be created when the table is defined.	Indexes can be created after a table is created.
Constraints	Primary Key, unique, null, check constraints can be used, but must be declared at table Creation	Primary Key, unique, null, check. constraints can be used. They can be added after any time, but cannot have foreign key constraints.
Use DDL (index, column) after the table is created)	Not Allowed	Allowed.
Data insertion method	Insert Statement (SQL 2000: insert/exec cannot be used ).	Insert statement, including insert/exec. Select into statement.
Insert explicit values into identity columns (set identity_insert ).	The Set identity_insert statement is not supported.	Supports the set identity_insert statement.
Truncate table	Not Allowed	Allow
Structure Analysis	Automatic Analysis after batch processing	Explicitly call the drop TABLE statement. Automatic Analysis of the current session end (Global temporary table: also includes when other session statements are not referenced in the Table .)
Transactions	Transactions only occur when updating the table. The duration is shorter than that of the temporary table.	Normal transaction length, longer than the table variable
Stored Procedure re-Compilation	No	Will cause recompilation
Rollback	Will not be affected by rollback	Will be affected by rollback
Statistical data	No statistical data is created, so all the estimated rows are 1, so generating the execution plan is inaccurate.	Create statistical data and generate an execution plan based on the actual number of rows.
Pass the stored procedure as a parameter	Only in SQL server2008, and user-defined table type must be predefined.	Not Allowed
Explicitly named object (index, constraint ).	Not Allowed	Yes, but pay attention to the problem of multiple users.
Dynamic SQL	Table variables must be defined in dynamic SQL	You can define a temporary table before calling dynamic SQL.

Comparison between table variables and temporary tables