Comparison of variables and the temporary table

What is on the table variables (table variable is not), and the temporary table is to let a lot of people very confused. Although the Internet has a lot of articles about them, but I have not found a more comprehensive. In this article, we will explore the table variables and the temporary table is (and what not), then we use temporary tables and table variables to decrypt.


Table variables

Table variables in the SQL Server was first introduced in 2000, so, what is the table variables? Microsoft in the BOL (Declare @local_variable) in defined it as a type of table variables. The specific definition of it including column definitions, names, data types and constraints. And can be used in a table variable constraints include the primary key constraint, a unique constraint, Null and Check constraints (foreign key constraint cannot be used in a table variable). The definition table variables and using the Create TABLE statement is normally defined table subset. Just a table variable defined by the DECLARE @local_variable statement.


By reference to 1 can know:

1) Table variables have a certain scope (in the current batch of statements, but not in any of the current batch of statements calling stored procedures and functions), table variables automatically cleared at the end of the batch after.


2) Reference 6 in "Recompilations Due to Certain Temporary Table Operations" link on a temporary table in will lead to a variety of reasons the stored procedure are being forced repetition compiled, but these reasons do not apply to a table variable. The stored procedure will produce less than table variables and temporary tables are recompiled.


3) For variable transaction shall become effective only when updating data, so less number of locks and log generated.


4) Because so little effect table domain variables, persistent part of the database and do not belong, so the transaction rollback will not affect the variables.


Table variables can be like normal table used in its scope. More specifically, table variables can be as normal table or table expression in select, delete, update, use the insert statement. But the table variables not in similar to “ SELECT select_list INTO table_variable” the use of such a statement. In SQL Server 2000, table variables cannot be used in “ INSERT INTO table_variable EXEC stored_procedure” such a statement.


Do the following variables cannot be table:

1 although the table variable is a variable, but it cannot be assigned to another variable.

2.check constraints, default values, and the computed column cannot reference a user-defined function.

3 cannot be a constraint name.

Truncate 4 cannot table variables

5 not explicit values into an identity column insertion (i.e. table variable does not support SET IDENTITY_INSERT ON)

The temporary table

Before delving into the temporary table, we first need to discuss the session (Session), a session is just a client to connect to the data engine. In the SQL Server Management Studio (SSMS), each query window will establish a connection and the database engine. An application can and database establishing one or more connected, in addition, the application may also establish a connection has not released until the application ends, may also use the end releases the connection required to establish a connection.

So, what is the temporary table? In the BOL (CREATE TABLE), we can know that the temporary table and created in the Create TABLE statement table have the same physical form, but the temporary table and normal table differ:


1) The temporary table name cannot exceed 116 characters, this is because the database engine in order to identify the different session establishment temporary tables are different, so will automatically in the temporary table name after additional string


2) A local temporary table (“ #” start naming) scope only in the current connection, from the establishment of a local temporary table in the storage process point of view, a local temporary table will be Drop under any of the following circumstances:

A. explicitly call the DROP Table statement

B. when a local temporary table is created inside a stored procedure, stored procedure end means a local temporary table is DROP

C. the end of the current session, all local temporary tables are created within the session will be Drop


3) A global temporary table (“ ##” start naming) visible in all of the sessions, so before create global temporary table first verify that it exists, or if already exists, you will be repeated to create the object error.

A. global temporary tables will be created in the DROP after the end of the session, the session will not be on other global temporary tables are referenced.

The B. reference is performed at the statement level, for example the following example:

       I. creates a new query window, run the following statement:

create table ##temp (RowID int)

       Ii. again open a new query to create, use the following statements every 5 seconds on the global temporary tables are referenced

while 1=1 begin select * from ##temp waitfor delay '00:00:05' end

      III. back to the first window, close the window

      Global in the next cycle of IV. temporary table, an error will occur


4) Not to partition the temporary table.


5) Not on temporary tables with foreign key constraints

6) Data types cannot be defined as temporary tables are not in the TempDb does not define a custom data types (custom data types are objects, database level and temporary table belongs to the TempDb), because TempDb is automatically created for each SQL Server after the restart, so you must use the startup stored procedure to TempDb to create a custom data types. You can achieve this goal by modifying the Model database.


7) The XML column cannot be defined as XML set form, unless this collection has been defined in TempDb


Temporary tables can be created using the Create Table statement, can also through the ” SELECT <select_list> INTO #table” create. You can also use the temporary table ” INSERT INTO #table EXEC stored_procedure” such a statement.

Temporary tables can have named constraints and indexes. However, when two users on the same storage process in the same time to call, will produce”There is already an object named ‘<objectname>’ in the database”Such a mistake. So the best approach is not to establish the object name, and use the system assignment in TempDb only. 6

Methods 6 talked about for many reasons caused by the temporary table stored procedure to recompile and avoid.


Misunderstanding

Misunderstanding 1 table variables only in memory.

Misunderstanding 2 temporary tables store only in the physical medium

These two views are obvious errors in reference 1, section Q4. Table variables are created in the TempDb database, because the table variables stored data may exceed the physical memory. In addition, we found that as long as enough memory, table variables and temporary tables are created in memory and processing. They also can at any time be saved to disk.

How to prove it? Please look at the following code (in SQL Server 2000 to 2008)

-- make a list of all of the user tables currently active in the -- TempDB database if object_id('tempdb..#tempTables') is not null drop table #tempTables select name into #tempTables from tempdb..sysobjects where type ='U' -- prove that even this new temporary table is in the list. -- Note the suffix at the end of it to uniquely identify the table across sessions. select * from #tempTables where name like '#tempTables%' GO -- create a table variable declare @MyTableVariable table (RowID int) -- show all of the new user tables in the TempDB database. select name from tempdb..sysobjects where type ='U' and name not in (select name from #tempTables)

There are a number of “ ” temporary table exists only in the memory error, below I said one:

Note that the table variable is the name of the distribution system, the first character table variables ” @” is not a letter, so it is not a valid variable name. Name the system will create a system assigned to variables in TempDb, so any method in the sysobjects or sys.tables lookup table variables will fail.

The correct approach should be the method I in the last example, I see a lot of people use the following query table variables:

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

The code looks like very good, but will produce multiple user problems. You set up the two connection, in creating temporary table first connected, run the above statement in the second windows to see the first temporary table connection is created, if you try second connected, the temporary table, then may produce errors, because the temporary table does not belong to your session.


Misunderstanding 3 table variables do not have index.

This error is the same mistake. Once you create a table variable, cannot carry on the DDL statement, which includes the Create Index statement. However, you can create index in table variables defined) such as the following statement.

declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

This will create a clustered index on a table variable. Because the primary key of a clustered index is corresponding, so the index will be named a system is created in the RowID column.

The following example you can create a unique constraint on a table variable column and how to establish index.

declare @temp TABLE ( RowID int NOT NULL, ColA int NOT NULL, ColB char(1)UNIQUE, PRIMARY KEY CLUSTERED(RowID, ColA))

1) SQL does not establish a statistical information for table variables, as it can for temporary tables to establish statistical information. This means that for a table variable, the execution engine that only 1 lines, this also means that the implementation of the plan table variables is not optimal. Although the estimated execution plan for table variables and temporary tables are 1, but the actual execution plan for the temporary table will change according to each stored procedure to recompile and (see reference 1, section Q2). If the temporary table does not exist, when generating the execution plan will generate an error.


2) As mentioned, must build a table variable cannot be carried on the DDL statement operation. So if you need to index table or add a column, you need a temporary table.


3) Table variables can not use the select … into statements, and temporary tables can be


4) In SQL Server 2008, you can be a table variable is passed as a parameter to a stored procedure. But the temporary table. In SQL Server 2000 and table variable 2005.


5) Scope: table variables only in the current batch effectively, and to any in the nested stored procedure is not visible. A local temporary table is only effective in the current session, which also includes the storage process nested. But the father of the stored procedure is not visible. Global temporary tables can be visible in any conversation, but will DROP as the session is terminated, the other session then not reference a global temporary table.


6) Sorting rules: collation table variables using the current database, temporary table sorting rules using TempDb. If they are not compatible, you still need to query or table definition is specified (see 7.Table Variables and Temporary Tables)


7) If you wish to use the table variables in dynamic SQL, you must define a table variable in dynamic SQL. Temporary tables can be defined in advance, reference in dynamic SQL.


Said so much, so, how should I choose.?

Microsoft recommends the use of variables (see Table 4), if the number of rows in the table is very small, using table variables. Many ” network expert ” will tell you 100 is a demarcation line, because this is the statistical information to create query plans to start efficiency. But I still want to tell you on your specific needs for temporary tables and table variables tested. A lot of people using table variables in the function, if you need to use primary and unique index on a table variable, you'll find out contains thousands of rows of table variables are still outstanding performance. But if you want the table variables and other table join, you'll find out due to the lack of precise execution plan, performance is often very poor.

To prove the point, please see the attachment. The code creates a table variable and temporary tables. And loaded into the AdventureWorks database Sales.SalesOrderDetail table. In order to get enough test data, the data in the table is inserted into the 10 times. And then to the ModifiedDate column as a condition of the temporary tables and table variables and the original Sales.SalesOrderDetail sheet for the Join operation, the statistical information from the IO significant difference. In the view of time table variables do join spent more than 50 seconds, and the temporary table only spent 8 seconds.

If you need to set up a table table after DLL operation, so the choice of temporary tables.

Temporary tables and table variables have many similar places. So there is no specific rules on how to choose a. On any given situation, you need to consider their advantages and disadvantages and do some performance testing. The following table will allow you to compare their advantages and slightly more detailed reference.


Summary

Characteristic Table variables The temporary table
Scope The current batch The current session, nested stored procedure, global: all sessions
Use scene Custom function, stored procedure, batch processing Custom function, stored procedure, batch processing
Create a style DECLARE statement only. can only be created through the DECLEARE statement

The CREATE TABLE statement

The SELECT INTO statement.

Table name length A maximum of 128 bytes A maximum of 116 bytes
The type of the column

You can use a custom data types

You can use the XML collection

Custom data types and the XML set must be defined in the TempDb
Collation String collation inherits from the current database String collation inherits from the TempDb database
Indexes The index must be built on the table definition The index can be established in a table is created
Constraint PRIMARY KEY, UNIQUE, NULL, CHECK constraints can be used, but must be declared in the table is established PRIMARY KEY, UNIQUE, NULL, CHECK. constraints can be used, can be added at any time, but can not have a foreign key constraint
Table after the establishment of the DDL (index, column) Not allowed Allow.
Data insertion INSERT statements (SQL 2000: cannot use INSERT/EXEC).

The INSERT statement, including the INSERT/EXEC.

The SELECT INTO statement.

Insert explicit values into identity columns (SET IDENTITY_INSERT). Does not support the SET IDENTITY_INSERT statement Support for the SET IDENTITY_INSERT statement
Truncate table Not allowed Allow
Structural analysis method Batch after the automatic analysis. An explicit call to DROP TABLE statement.
The end of the current session automatically destructor (global temporary table: even when other session statement is not in the reference table.)
Transaction There will only be the transaction when the update table, short duration than the temporary table Transaction length than normal, table variable length
The stored procedure to recompile Whether or not Will lead to re compile
Rollback Will not be rolled back effect Will be rolled back effect
Statistical data Don't create statistical data, so the estimation for all the number is 1, so the plan might not precise Create statistical data, through the actual line number generation execution plan.
As the parameter passed to the stored procedure Only in the SQL Server2008, and must be predefined user-defined table type. Not allowed
Explicit naming of objects (index, constraint). Not allowed Allowed, but must pay attention to the problem of multi user
Dynamic SQL You must define a table variable in dynamic SQL You can define a temporary table before calling the dynamic SQL

Reference resources:

1) INF: Frequently Asked Questions - SQL Server 2000 - Table Variables

2) T-SQL BOL (SQL 2000), table data type

3) T-SQL BOL (SQL 2008), Declare @local_variable

4) T-SQL BOL (SQL 2008), CREATE TABLE

5) Table-Valued Parameters (Database Engine)

6) Troubleshooting stored procedure recompilation

7) Local Temporary Tables and Table Variables

8) Startup stored procedure

9) Data Definition Language (DDL)

The other is worth reading:

1) Things You Didn’t Know About Temp Tables and Table Variables


Posted by Kim at November 22, 2013 - 5:15 PM