Go Differences Between SQL Server table variables and temporal tables

1. 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. 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). The statements that define table variables are a subset of the statements that normally use Create Table to define tables. Only the table variable is defined by DECLARE @local_variable statement.

Characteristics of table variables:

Table variables have a specific scope (in the current batch statement, but not in any stored procedures and functions called by the current batch statement), and table variables are automatically cleared after the batch process ends.
Table variables produce fewer stored procedure recompiles than temporary tables.
Transactions on table variables only take effect when updating data, so the number of locks and logs will be less.
Because the scope of table variables is so small, and is not part of the persistent part of the database, so the transaction rollback will not affect the table variables.
Table variables can be used like normal tables in their scope. More precisely, table variables can be used as normal tables or table expressions in SELECT, DELETE, UPDATE, INSERT statements, but table variables cannot be used in statements like "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:

Although a table variable is a variable, it cannot be assigned to another variable.
Check constraints, default values and calculated columns cannot refer to custom functions.
You cannot name the constraint.
Cannot Truncate table variables.
Cannot insert explicit values into identity columns (that is, table variables do not support SET IDENTITY_INSERT ON)
Let's play with table variables.

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

DECLARE @ tb1 Table
(
 Id int,
 Name varchar (20),
 Age int
)

INSERT INTO @ tb1 VALUES (1, ‘Liu Bei’, 22)

SELECT * FROM @ tb1
The output is as follows:



Let's try some situations that do not meet the requirements, such as adding table variables, adding constraints, and naming the constraints:

ALTER TABLE @ tb1
    ADD CONSTRAINT CN_AccountAge
    CHECK
    (Account_Age> 18);-Insert age must be greater than 18
SQL Server prompts the error as follows:



SQL Server does not support naming Constraint when defining table variables, nor does it support building Constraints after defining table variables.

More is not allowed, please check the requirements above.

Second, the temporary table
Before diving into the temporary table, we have to understand the session (Session), a session is just a client to the data engine connection. In SQL Server Management Studio, each query window will establish a connection with the database engine. An application can establish one or more connections to the database. In addition, the application may not release after the connection is established until the application ends, or it may use the connection to release the connection when needed.

The temporary table and the table created by the Create Table statement have the same physical engineering, but the temporary table is different from the normal table:

1. The name of the temporary table cannot exceed 116 characters. This is because the database engine creates different temporary tables in order to distinguish different sessions, so it will automatically append a string after the name of the temporary table.

2. The scope of the local temporary table (named with "#" at the beginning) is only in the current connection. From the perspective of creating a local temporary table in the stored procedure, the local temporary table will be dropped under the following circumstances: Show call Drop Table statement b. When the local temporary table is created in the stored procedure, the end of the stored procedure means that the local temporary table is dropped. C. The current session ends, and all local temporary tables created in the session will be dropped.

3. The global temporary table (named with "##" at the beginning) is visible in all sessions, so first check whether it exists before creating the global temporary table, otherwise if it already exists, you will get the error of repeatedly creating objects. A. The global temporary table will be dropped after the session that created it. After the drop, other sessions will not be able to reference the global temporary table. B. Reference is made at the sentence level, such as: 1. Create a new query window and run the statement

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 * FROM ## 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 will be generated.



4. Cannot partition the temporary table.

5. Cannot add foreign key constraints on temporary tables.

6. The data type of the column in the temporary table cannot be defined as that there is no custom data type 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 goal by modifying the Model database.

7. The XML column cannot be defined in the form of an XML collection unless this collection has been 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 like "INSERT INTO #table EXEC stored_procedure" for temporary tables. Temporary tables can have named constraints and indexes. However, when two users call the same stored procedure at the same time, the error “There is already an object named‘ <objectname> ’in the database” will occur. So the best approach is not to name the created object, but to use the system-assigned unique in TempDb.

3. Misunderstanding
Misunderstanding 1. Table variables are only in memory.

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

Both views are wrong, only memory is enough, table variables and temporary tables will 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 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 creates a system-assigned name for table variables in TempDb, so any method of finding table variables in sysobjects or sys.tables will fail.

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

Select * from sysobjects where name like ‘# tempTables%’
The above code seems to be very useful, but it will cause problems for multiple users. You establish 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, if you try to manipulate the temporary in the second connection Table, then 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 indexes for table variables when they are defined), such as the following statement.

Declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)
    This statement will create a table variable with a clustered index. Since the primary key has a corresponding clustered index, a system-named index will be created on the RowID column.

    The following example demonstrates that you can create unique constraints on a column of table variables and how to build a compound 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 for a table variable, the execution engine thinks it has only one row, which also 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 for temporary tables will change every time the stored procedure is recompiled. If the temporary table does not exist, an error will be generated when the execution plan is generated.

2) Once the table variable is created, the DDL statement cannot be operated on it. So if you need to index or add a column to the table, you need a temporary table.

3) Table variables cannot use select… into statements, while temporary tables can.

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

5) Scope: Table variables are only valid in the current batch, and are not visible to any stored procedures nested within them. Local temporary tables are only valid in the current session, which also includes nested stored procedures. But it is not visible to the parent stored procedure. The global temporary table can be seen in any session, but will be DROP as the session that created it is terminated, and other sessions can no longer refer to the global temporary table at this time.

6) Sorting rules: table variables use the current database sorting rules, and temporary tables use TempDb sorting rules. If they are not compatible, 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. The temporary table can be defined in advance and referenced in dynamic SQL.

4. How to choose
Microsoft recommends using table variables. If the number of rows in the table is very small, use table variables. Many "network experts" will tell you that 100 is a dividing line, because this is the beginning of the efficiency of statistical information to create a 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. If you need to use primary keys and unique indexes in table variables, you will find that table variables with thousands of rows are still excellent. But if you need to join table variables and other tables, you will find that performance is often very poor due to inaccurate execution plans.

To prove this, please see the attachment of this article. The code in the attachment creates table variables and temporary tables. It is loaded into the Sales.SalesOrderDetail table of the AdventureWorks database. In order to get enough test data, I inserted the data in this table 10 times. Then use the ModifiedDate column as a condition to join the temporary table and table variables with the original Sales.SalesOrderDetail table. From the statistical information, the difference in IO is significant. From the time point of view, it took more than 50 seconds for the table variable to join, while the temporary table only took 8 seconds.

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

Temporary tables and table variables have many similarities. So sometimes there are no specific rules on how to choose which one. For any specific situation, you need to consider their respective advantages and disadvantages and do some performance tests. The following table will give you a more detailed reference for comparing its advantages.

V. Summary
Characteristics Table variables Temporary 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
Created by DECLARE statement only. Can only be created by DECLARE statement
CREATE TABLE statement

SELECT INTO statement.

Table name length up to 128 bytes up to 116 bytes
Column type
Can use custom data types

XML collection can be used

Custom data types and XML collections must be defined in TempDb
Collation String collation inherited from the current database String collation inherited from the TempDb database
Index Index must be established when the table is defined Index can be established after the table is created
Constraints PRIMARY KEY, UNIQUE, NULL, CHECK constraints can be used, but must declare PRIMARY K when the table is created EY, UNIQUE, NULL, CHECK. Constraints can be used and can be added at any time, but there can be no foreign key constraints
The use of DDL (index, column) after table creation is not allowed.
Data insertion method INSERT statement (SQL 2000: INSERT / EXEC cannot be used).
INSERT statements, including INSERT / EXEC.

SELECT INTO statement.

Insert explicit values into identity columns (SET IDENTITY_INSERT). SET IDENTITY_INSERT statement is not supported. SET IDENTITY_INSERT statement is supported.
Truncate table not allowed allowed
Destructive mode Automatically destruct after batch processing. DROP TABLE statement is explicitly called. Automatic destructuring at the end of the current session (global temporary table: also includes when other session statements are not in the reference table.)
The transaction will only have a transaction when the table is updated, the duration is shorter than the temporary table, the normal transaction length is longer than the table variable
Stored procedure recompilation No Will cause recompilation
Rollback will not be affected by the rollback will be affected by the rollback
Statistical data does not create statistical data, so all estimated rows are 1, so generating an execution plan will be inaccurate. Creating statistical data creates an execution plan based on the actual number of rows.
Passed into the stored procedure as a parameter is only in SQL Server2008, and must be predefined user-defined table type. Not allowed
Explicitly named objects (indexes, constraints). Not allowed Allowed, but pay attention to the issue of multiple users
Dynamic SQL must define table variables in dynamic SQL. You can define temporary tables before calling dynamic SQL
 

Source: http://www.cnblogs.com/CareySon/archive/2012/06/11/TableVariableAndTempTable.html

[Switch] the difference between SQL Server table variables and temporary tables