Differences Between SQL Server table variables and temporary tables (detailed additions) _mssql

One, table variables

Table variables were introduced for the first time in SQL Server 2000. The specific definition of a table variable includes a column definition, a column name, a data type, and a constraint. 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). The statement that defines the table variable is a subset of the table statements defined by the normal use of the CREATE table. Only table variables are defined by DECLARE @local_variable statements.

Characteristics of Table variables:
1. Table variables have specific scopes (in the current batch statement, but not in stored procedures and functions that are invoked by any of the current batch statements), and table variables are automatically purged after the batch process ends.
2. Table variables produce fewer stored procedure recompilation than temporary tables.
3. Transactions against table variables take effect only when data is updated, so fewer locks and logs will be generated.
4. Because the scope of the table variable is so small and does not belong to a persistent part of the database, the transaction rollback does not affect the table variable.

A table variable can be used in its scope as if it were a normal table. More specifically, table variables can be used in select,delete,update,insert statements as normal tables or table expressions, but table variables cannot be in a similar "SELECT select_list into Table_variable" Used in such a statement. In SQL Server2000, table variables cannot be used in statements such as insert INTO Table_variable EXEC stored_procedure.

A table variable cannot do the following things:
1. Although the table variable is a variable, it cannot be assigned a value to another variable.
2.check constraints, default values, and computed columns cannot refer to custom functions.
3. You cannot name a constraint.
4. Cannot truncate the table variable.
5. 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 a table variable here.

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

DECLARE @tb1 Table
(
 Id int,
 Name varchar, age
 int
)

inserts into @tb1 VALUES (1, ' Liu Bei

SELECT * from @tb1

The output results are as follows:

　　

Try something that doesn't meet your requirements, such as adding a table variable, adding a constraint, and naming the constraint:

Try something that doesn't meet your requirements, such as adding a table variable, adding a constraint, and naming the constraint:

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

SQL Server prompts an error as follows:

SQL Server does not support naming constraint when defining table variables, nor does it support the definition of table variables after they are constraint.

More is not allowed, please check the above requirements.

Second, temporary table

Before we dive into the temporary table, we need to understand the session, a session is just a client to the 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 a database, and in addition, an application may establish a connection and never release it until the application is finished, or it may use the release connection when it is needed.

Tables created by temporary tables and CREATE TABLE statements have the same physical engineering, but temporary tables differ from normal tables:

1, the name of the temporary table can not exceed 116 characters, this is because the database engine to identify different sessions to create a different temporary table, so automatically after the name of the temporary table appended a string.

2, 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 is drop in the following cases:
A, Show call drop TABLE statement
b, the temporary table of authorities when created within a stored procedure, the end of the stored procedure means that the local temporary table is drop.
C, 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 check to see if it exists before creating a global temporary table, or if it already exists, you will get the error of creating the object repeatedly.
A, global temporary tables will not be referenced to global temporary tables after they are drop,drop by the end of the session in which they were created.
b, references are made at the statement level, such as:
1. New Query window, run statement:

Copy Code code as follows:

CREATE TABLE # #temp (RowID int)
INSERT into # #temp VALUES (3)

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

While 1=1 
BEGIN
SELECT * "# #temp
WAITFOR delay ' 00:00:05 '
end

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

4, the temporary table can not be partitioned.
5, the temporary table can not be added foreign key constraints.
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 (a custom data type is a database-level object, and a temporary table belongs to tempdb). Because tempdb is created automatically every time SQL Server restarts, 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 statements such as INSERT into #table EXEC stored_procedure for temporary tables.
A temporary table can have named constraints and indexes. However, when two users invoke the same stored procedure at the same time, an error such as "There is already a object named ' <objectname> ' in the database" will result. So the best thing to do is not to name the objects that are created, but to use the system-assigned unique in tempdb.

Third, misunderstanding

Myth 1. Table variables are in memory only.

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

Both views are wrong, only enough memory, table variables and temporary tables will be created and processed in memory. They can also be deposited at any time in the disk.
Note the name of the table variable is assigned by the system, 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 table variables in tempdb, so any method that sysobjects or Sys.tables lookup table variables will fail.

The correct approach should be the one in my previous example, and I see a lot of people using the following query to look up the table variables:

　　SELECT * from sysobjects where name like ' #tempTables% '

The code looks good, but it can create multiple user problems. You build two connections, creating a temporary table in the first connection, running the above statement in the second window, sees the temporary table created by the first connection, and if you try to manipulate the temporary table in the second connection, you may have an error because the temporary table does not belong to 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 a DDL statement on it, including the CREATE INDEX statement. However, you can create an index for a table variable when it is defined, such as the following statement.

　　Declare @MyTableVariable table (RowID intprimary 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 set up a composite index.

DECLARE @temp TABLE (
 RowID int not null,
 Cola int not null,
 Colb char (1) UNIQUE,
 PRIMARY KEY clust Ered (RowID, cola))

1 SQL does not create statistical information 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 changes for temporary tables based on the recompilation of each stored procedure. If a temporary table does not exist, an error occurs when the execution plan is generated.

2 Once the table variable is established, it cannot be manipulated by DDL statements. So if you need to index or add a column to a table, 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 are also not allowed in SQL Server 2000 and 2005.

5 scope: Table variables are valid only in the current batch, and are not visible to any stored procedures nested within them. A local temporary table is valid only in the current session, which also includes nested stored procedures. It is not visible to the parent stored procedure. A global temporary table can be visible in any session, but will drop as the session that created it terminates, and other sessions will no longer be able to reference the global temporary table.

6 Collation: The table variable uses the collation of the current database, and the temporary table uses the collation of tempdb. If they are not compatible, you will 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 the table variables in dynamic SQL. Temporary tables can be defined in advance and referenced in dynamic SQL.

Four, how to choose

Microsoft recommends using table variables and table variables if the number of rows in the table is very small. A lot of "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 custom functions, and if you need to use a primary key and a unique index in a table variable, you'll find that a table variable with thousands of rows is still excellent. But if you need to join a table variable with another table, you'll find that performance tends to be very poor due to imprecise execution plans.

To prove this, please see the annex to this article. The code in the attachment creates a table variable and a temporary table. and loads the Sales.SalesOrderDetail table of the AdventureWorks database. To get enough test data, I inserted the data from this table 10 times. Then, with the ModifiedDate column as a condition, temporary tables and table variables are join operations with the original Sales.SalesOrderDetail table, and the IO difference is statistically significant. It took more than 50 seconds to make a join from the time table variable, while the temporary table took only 8 seconds.

If you need to dll the table after the table is established, select the temporary table.

Temporary tables and table variables have many similar places. So sometimes there is no specific rule as to which one to choose. For any given situation, you need to consider the pros and cons and do some performance testing. The following table will give you a more detailed reference to your comparative advantage.

Characteristics	Table variables	Temporary tables
Scope	Current Batch	Current session, nested stored procedures, global: all Sessions
Working with scenes	Custom functions, stored procedures, batch processing	Custom functions, stored procedures, batch processing
How to create	DECLARE statement only. Can only be created through 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 XML collections	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	Indexes must be established when the table is defined	Indexes can be established after a table is created
Constraints	PRIMARY KEY, UNIQUE, NULL, check constraint available, 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 is established	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 statement not supported	Support for SET IDENTITY_INSERT statements
Truncate table	Not allowed	Allow
destructor mode	Automatic destructor after batch processing ends	Calls the DROP TABLE statement explicitly. Current session End automatic destructor (global temporary table: also includes when other conversational sentences are not in the reference table.)
Transaction	Transactions are only available when the table is updated, and the duration is shorter than the temporary table	Normal transaction length, longer than table variable
Stored Procedure recompilation	Whether	can cause recompilation
Rolling back	Will not be affected by rollback	will be rolled back effect
Statistical data	No statistics are created, so all estimated lines are 1, so the build execution plan is imprecise	Create statistics to generate an execution plan from the actual number of rows.
Passing in a stored procedure as a parameter	Only in SQL Server2008, and must predefined user-defined table type.	Not allowed
Explicit naming of objects (indexes, constraints).	Not allowed	Allow, but be aware of multi-user problems
Dynamic SQL	Table variables must be defined in dynamic SQL	You can define a temporary table before calling dynamic SQL