T-SQL table variables and temporary tables

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). A statement that defines a table variable is a subset of a table statement that uses the CREATE table definition as normal. Only table variables are defined by DECLARE @local_variable statements.

Characteristics of Table variables:

1. 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 .
2. Table variables produce fewer stored procedure recompilation than temporary tables.
3. A transaction against a table variable takes effect only when the data is updated, so the number of locks and logs is generated less.
4. 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" Used in such a statement. 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:

1. Although a table variable is a variable, it cannot be assigned to another variable.
2. Check constraints, default values, and computed columns cannot reference custom functions.
3. You cannot name a constraint.
4. You cannot truncate a 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)

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:

　　

Second, temporary table

Session, a session is simply 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 until the application ends or the connection may be established when the release connection is required.

A temporary table is a table that is built into a temporary system folder, and can be manipulated as a normal table if used properly.

temporary tables fall into two categories: local temporary tables and global temporary tables:

[1] The local temporary table is only visible in the current session, and the name of the local temporary table is preceded by a number sign (#);

[2] Global temporary tables are visible in all sessions with two number characters (# #) in front of the name of the global staging table.

If a local temporary table is created by a stored procedure or by an application that is executed concurrently by multiple users, SQL Server must be able to differentiate between tables created by different users. To do this, SQL Server internally appends a numeric suffix to the table name for each local temporary table. The temporary table stored in the sysobjects table in the tempdb database, whose full name consists of the table name specified in the Create table statement and the system-generated numeric suffix. To allow appending suffixes, the table name specified for the local temporary table table_name cannot exceed 116 characters.

The temporary table will be automatically deleted by the system when exiting its scope, unless the temporary table is explicitly dropped by using the Drop table statement.

When the stored procedure finishes, the local temporary table created in the stored procedure is automatically dropped. This table can be referenced by all nested stored procedures that are executed by the stored procedure that created the table. However, the process that called the stored procedure that created this table cannot reference this table.

All other local temporary tables are automatically deleted at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and other tasks stop referencing them. The association between a task and a table is persisted only within the lifetime of a single Transact-SQL statement. In other words, when the session that created the global temporary table ends, the last Transact-SQL statement referencing this table is finished and the table is automatically dropped.
A local temporary table created in a stored procedure or trigger differs from a temporary table of the same name that was created before the stored procedure or trigger was called. If the query references a temporary table, and there are two temporary tables with the same name, the query is not defined for which table to resolve. A nested stored procedure can also create a temporary table with the same name as the temporary table created by the stored procedure that called it. All references to table names in a nested stored procedure are interpreted as tables created for the nested procedure.

The CREATE TABLE syntax supports all constraint definitions other than the Foreign Key constraint when creating a local or global temporary table. If you specify the Foreign Key constraint in a temporary table, the statement returns a warning message stating that the constraint has been ignored, that the table will still be created, but does not have a Foreign Key constraint. Temporary tables cannot be referenced in the FOREIGN KEY constraint.

Consider using table variables without using temporary tables. Temporal tables are useful when you need to explicitly create an index on a temporary table, or when more than one stored procedure or function needs to use a table value. Typically, table variables provide more efficient query processing.

1. Create a temporary table

 --  method one: directly create  create  table   #临时表名 (field 1 constraints,      Field 2 constraints, ...)  create  table   # #临时表名 (field 1 constraints, field 2 constraints, ...)  --  method Two: Insert specific data to create  select  *  into  #临时表名 from   table name;  select  *   # #临时表名 from  table name; 

2. Inserting data in temporary tables

Create Table #临时表名 (numberintPrimaryKey)Insertinto  Values (1)

3. Querying temporary tables
SELECT * from #临时表名;
SELECT * from # #临时表名;

4. Delete temporary tables
drop table #临时表名;
drop table # #临时表名;

5. Empty all data and constraints of the temporary table

TRUNCATE TABLE #临时表名;
TRUNCATE TABLE # #临时表名;

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:
[1] Show call to drop TABLE statement
[2] Authorities temporary tables are created within a stored procedure, and the end of the stored procedure means that the local temporary table is drop.
[3] 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, it will return the error of creating the object repeatedly.
(1) The global temporary table will not be able to reference the global staging table after the session at which it was created is drop,drop.
(2) References are made at the statement level, such as:
[1] New Query window, 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] Back to the first window, close the window.
[4] The next time a second window is referenced, an error will be 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

1. The table variable is only in memory.

2. Temporary tables are only stored in 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. It 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 sysobjects where name ' #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.

3. Table variables cannot have indexes.

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.

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,   ColB char (1) UNIQUE,   PRIMARY KEY clust Ered (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. Choice of scenarios

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.

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.

V. Comparison of table variables and temporal tables

The table below will give you a more detailed reference to the advantages.

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

T-SQL table variables and temporary tables