Difference between SQL Server table variables and temporary tables (Supplement), SQL Server

Difference between SQL Server table variables and temporary tables (Supplement), SQL Server

I. Table Variables

Table variables are introduced in SQL Server 2000 for the first time. 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 ). 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.

Table variable features:
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. Table variables produce less stored procedure re-compilation than temporary tables.
3. transactions targeting table variables take effect only when updating data, so the number of locks and logs generated will be 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 Server2000, 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. The default value and calculation Column cannot reference user-defined functions.
3. constraints cannot be named.
4. You 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)

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,'刘备',22)

  SELECT * FROM @tb1

The output result is as follows:

Try some situations that do not meet the requirements. For example, after adding a table variable, add constraints and name the constraints:

Try some situations that do not meet the requirements. For example, after adding a table variable, add constraints and name the constraints:

The ALTER TABLE @ tb1
ADD the CONSTRAINT CN_AccountAge
The CHECK
(Account_Age > 18); -- the insertion age must be greater than 18

The SQL Server prompts the following error:

SQL Server does not support naming Constraint when defining table variables, or creating a Constraint after defining table variables.

For more information, see the preceding requirements.

Ii. Temporary table

Before going into the temporary table, let's take a look at sessions. A Session is just a connection from a client to a data engine. In SQL Server Management Studio, 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 not be released until the application ends, you may also need to establish a connection when releasing the connection.

The temporary Table and the Table created by the Create Table statement have the same physical engineering, but the temporary Table and the normal Table have the following differences:

1. The temporary table name cannot exceed 116 characters. This is because the database engine creates different temporary tables to identify different sessions. Therefore, a string is automatically appended to the temporary table name.

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. The Drop Table statement is called.
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 will be dropped after the session is created. After the Drop operation, other sessions will not be able to reference the global temporary table.
B. References are performed at the statement level, for example:
1. Create a query window and run the following statement:

Copy codeThe Code is as follows:
Create table # temp (RowID int)
Insert into # temp VALUES (3)

2. Create a New query window and reference the global temporary table every 5 seconds.

While 1=1 
  BEGIN
  SELECT * FROM ##temp
  WAITFOR delay '00:00:05'
  END

3. Return to the first window and close the window.
4. An error occurs when the second window is referenced.

4. Temporary tables cannot be partitioned.
5. Foreign key constraints cannot be added to temporary tables.
6. The data type of the columns in the temporary table cannot be defined as the custom data type not defined 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. XML Columns cannot be defined as XML sets unless they are already 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.

Iii. misunderstandings

Misunderstanding 1. Table variables are only in memory.

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

Both views are incorrect. Only the memory is sufficient. Both Table variables and temporary tables are created and processed in the memory. They can also be stored in the disk at any time.
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 a composite 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 variable 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. If the temporary table does not exist, an error occurs when an execution plan is generated.

2) Once a table variable is created, it cannot be operated by DDL statements. 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... The 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.

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.

4. How to choose

Microsoft recommends using Table variables. If the number of rows in a 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.

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.