The difference between a [MSSQL] table variable and a temporary table

First, table variables

Table variables were first introduced in SQL Server 2000. The specific definitions of table variables include column definitions, column names, data types, and constraints. 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 that define table variables are and normally use the CREATE table to define a subset of table statements. Only table variables are defined by DECLARE @local_variable statements.

Characteristics of Table variables:

Table variables have a specific scope (in the current batch statement, but not in the stored procedures and functions that are called by any current batch statement), and the table variable is automatically cleared after the batch finishes .

Table variables produce fewer stored procedure recompilation than temporary tables.

A transaction against a table variable takes effect only when the data is updated, so the number of locks and logs is generated less.

Because the scope of a table variable is so small and not part of the persistence of the database, a transaction rollback does not affect the table variable.

Table variables can be used as normal tables within their scope. Rather, a table variable can be used as a normal table or table expression in a Select,delete,update,insert statement, but the table variable cannot be in a similar "select select_list into table_ Variable "is used in statements like this. In SQL Server2000, table variables cannot be used in statements such as insert into Table_variable EXEC stored_procedure.

The table variable cannot do the following things:

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

Check constraints, default values, and computed columns cannot reference custom functions.

You cannot name a constraint.

You cannot truncate a table variable.

You cannot insert an explicit value into an identity column (that is, the table variable does not support set IDENTITY_INSERT on)

Let's play table variables below.

Define a table variable, insert a piece of data, and then query:

DECLARE @tb1 Table(Idint, Namevarchar( -), ageint)INSERT  into @tb1 VALUES(1,'Liu Bei', A)SELECT *  from @tb1

The output results are as follows:

　　

Try some non-conforming scenarios, such as adding a table variable, adding a constraint, and naming the constraint:

ALTER TABLE @tb1    ADD CONSTRAINT cn_accountage     CHECK >) ;          --insertion age must be greater than

The SQL Server prompt error is as follows:

　　

SQL Server does not support defining a table variable when it is named constraint, nor does it support the definition of a table variable after it is built constraint.

More not allowed, please check the above requirements.

Second, temporary table

Before we dive into the temporal table, we need to know about the session, a session that is just a client-to-data engine connection. In SQL Server Management Studio, each query window establishes a connection to the database engine. An application can establish one or more connections to the database, and in addition, the application may establish a connection after it has not been released knowing that the application is finished, or it may establish a connection when the release connection is needed.

The table created by the temporary table and the CREATE TABLE statement has the same physical engineering, but the temporary table differs from the normal table:

1. The name of a temporary table cannot exceed 116 characters, because the database engine automatically appends a string to the name of the temporary table in order to identify different temporary tables for different sessions.

2, the Local temporary table (named after "#") scope only within the current connection , from the point of view of establishing a local temporary table in the stored procedure, the local temporary table will be drop in the following cases:
A. Show call to drop TABLE statement
B, authorities temporary tables are created within a stored procedure, and the end of the stored procedure means that the local temporary table is drop.
C, at the end of the current session, all local temporary tables created within the session will be drop.

3, the Global temporary table (named after "# #") is visible in all sessions, so before creating a global temporary table, check if it exists, or if it already exists, you will get an error creating the object repeatedly.
A, the global temporary table will be drop,drop after the session at which it was created, and other sessions will not be able to reference the global staging table.
b, references are made at the statement level, such as:
1. Create a new query window and run the statement:

CREATE TABLE # #tempint)  INSERT into # #tempVALUES( 3)

--Create a local temporary table
CREATE TABLE #tmpStudent (Tid int,name varchar (), age int)
INSERT into #tmpStudent values (' Xiaowang ',+)

SELECT * FROM #tmpStudent

--Create a local temporary table another way of writing
SELECT * to #tmpStudent from student
SELECT * FROM #tmpStudent

--Another way to create:

CREATE table tempdb. mytemptable (Tid int)--You can write this with a corresponding permission

--Delete

drop table #tmpStudent

2. Create a new query window again, referencing the global temporary table every 5 seconds

 while 1 = 1 BEGIN SELECT *  from # #tempWAITFOR'00:00:05'END 　　　　　　　　

3. Go back to the first window and close the window.
4. The next time a second window is referenced, an error is generated.

4. The staging table cannot be partitioned.

5. You cannot add foreign key constraints to temporary tables.

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

7. An XML column cannot be defined as a form of an XML collection unless the collection is already defined in tempdb.

Temporary tables can be created either through the CREATE TABLE statement or through the Select <select_list> into #table statement. You can also use the "INSERT into #table EXEC stored_procedure" statement for temporary tables.
Temporary tables can have named constraints and indexes. However, when two users call the same stored procedure at the same time, an error such as "There is already a object named ' <objectname> ' in the database" will be generated. Therefore, it is best not to name the established objects, but to use the system-assigned unique in tempdb.

Third, the misunderstanding

Myth 1. Table variables are only in memory.

Myth 2. Temporary tables are stored only on physical media.

Both of these views are wrong, and only enough memory is available for table variables and temporary tables to be created and processed in memory. They can also be saved to disk at any time.

Note that the name of the table variable is system-assigned, and the first character of the table variable "@" is not a letter, so it is not a valid variable name. A system-assigned name is created for the table variable in tempdb, so any method that looks up a table variable in sysobjects or sys.tables fails.

The correct method should be the method in my previous example, and I see many people using the following query to look up the table variables:

Select *  from where  like ' #tempTables% '

The code looks good, but it creates a problem with multiple users. You build two connections, create a temporary table in the first connection, run the above statement in the second window to see the temporary table created by the first connection, and if you try to manipulate the temporary table in the second connection, you might get an error because the temporary table is not part of your session.

　　Myth 3. A table variable cannot have an index.

This myth is equally wrong. Although once you create a table variable, you cannot make DDL statements for it, which includes the CREATE INDEX statement. You can, however, create an index for a table variable when it is defined, such as the following statement.

Declare @MyTableVariable Table KEY CLUSTERED

This statement will create a table variable that has a clustered index. Because the primary key has a corresponding clustered index, a system-named index is created on the ROWID column.

The following example shows that you can create a unique constraint on a column of a table variable and how to create a composite index.

 declare   @temp  table   (RowID  int  not  null  , ColA  int  not  null   char  (1 ) unique   primary  key  clustered  (RowID, ColA)) 

1) SQL does not establish statistics for table variables, just as it can create statistics for temporary tables. This means that for a table variable, the execution engine thinks it has only 1 rows, which means that the execution plan for the table variable is not optimal. Although the estimated execution plan is 1 for both table variables and temporary tables, the actual execution plan is changed for temporary tables based on the recompilation of each stored procedure. If the temporary table does not exist, an error occurs when the execution plan is generated.

2) Once a table variable is established, it cannot be manipulated by DDL statements. So if you need to index a table or add a column, you need a temporary table.

3) Table variables cannot use the SELECT ... into statement, while temporary tables can.

4) in SQL Server 2008, you can pass a table variable as a parameter to a stored procedure. But the temp table doesn't work. Table variables do not work in SQL Server 2000 and 2005.

5) Scope: The table variable is only valid in the current batch and is not visible to any stored procedures that are nested within it. Local temporary tables are only valid in the current session, which also includes nested stored procedures. However, the parent stored procedure is not visible. The global temporary table can be visible in any session, but it will drop as the session that created it terminates, and other sessions will no longer be able to reference the global staging table.

6) Collation: Table variables use the collation of the current database, and temporary tables use the collation of tempdb. If they are incompatible, you also need to specify them in the query or table definition.

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.

Iv. How to choose

Microsoft recommends using table variables, or table variables if the number of rows in the table is very small. Many "network experts" will tell you that 100 is a dividing line, because this is the beginning of the efficiency of the Statistical Information creation query plan. But I still want to tell you to test temporary tables and table variables for your specific needs. Many people use table variables in their custom functions, and if you need to use primary keys and unique indexes in table variables, you will find that table variables with thousands of rows still perform well. But if you need to join table variables with other tables, you will find that performance is often poor due to inaccurate execution plans.

To prove this, please see the annex to this article. The code in the attachment creates table variables and temporary tables. The Sales.SalesOrderDetail table for the AdventureWorks database is loaded. In order to get enough test data, I inserted the data in this table 10 times. The ModifiedDate column is then used as a condition to join the temporary table and table variables with the original Sales.SalesOrderDetail table, and the IO differs significantly from the statistics. It took more than 50 seconds to make a join from the time table variable, and the temporary table took only 8 seconds.

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

Temporary tables and table variables have a lot in the same place. So sometimes there are no specific rules on how to choose which one. For any particular situation, you need to consider their pros and cons and do some performance testing. The table below will give you a more detailed reference to the advantages.

V. Summary

Characteristics	Table variables	Temp table
Scope	Current batch Processing	Current session, nested stored procedure, global: all Sessions
Usage Scenarios	Custom functions, stored procedures, batch processing	Custom functions, stored procedures, batch processing
How to create	DECLARE statement only. Can only be created by Decleare statements	CREATE TABLE Statement SELECT into statement.
Table name length	Up to 128 bytes	Up to 116 bytes
Column type	You can use the custom data type You can use the XML collection	Custom data types and XML collections must be defined within tempdb
Collation	String collation inherits from the current database	String collation inherits from the tempdb database
Index	The index must be established when the table is defined	Indexes can be established after a table is created
Constraints	PRIMARY KEY, UNIQUE, NULL, check constraint can be used, but must be declared when the table is established	PRIMARY KEY, UNIQUE, NULL, CHECK. Constraints can be used, can be added after any time, but cannot have foreign key constraints
Use DDL (index, column) after table creation	Not allowed	Allow.
How data is inserted	INSERT Statement (SQL 2000: Cannot use insert/exec).	INSERT statements, including Insert/exec. SELECT into statement.
Insert explicit values into identity columns (SET identity_insert).	SET IDENTITY_INSERT statements are not supported	Support for SET IDENTITY_INSERT statements
Truncate table	Not allowed	Allow
Destruction mode	Automatic destruction after batch finishes	Explicitly call the DROP TABLE statement. Current session End auto-destructor (Global temp table: Also included when other conversational sentences are not in the reference table.)
Transaction	Only have a transaction when the table is updated, the duration is shorter than the temporary table	Normal transaction length, longer than table variable
Stored Procedure re-compilation	Whether	can cause recompilation
Rolling back	Will not be affected by rollback	Will be rolled back affect
Statistical data	No statistics are created, so all estimated rows are 1, so generating an execution plan is not accurate	Create the statistics to generate the execution plan from the actual number of rows.
Incoming stored procedure as a parameter	Only in SQL Server2008, and the user-defined table type must be predefined.	Not allowed
Explicitly name an object (index, constraint).	Not allowed	Allow, but be aware of problems with multiple users
Dynamic SQL	Table variables must be defined in dynamic SQL	You can define a temporary table before calling dynamic SQL

The difference between a [MSSQL] table variable and a temporary table