SQL Server temporary table Vs table Variables

Speaking of temporary tables and table variables, this is an old topic. We have also found a lot of information on the Internet to illustrate the characteristics, advantages and disadvantages of the two.

Speaking of temporary tables and table variables, this is an old topic. We have also found a lot of information on the Internet to illustrate the characteristics, advantages and disadvantages of the two.

Here, we use SQL Server 2005 \ SQL Server 2008 as an example to illustrate some features of temporary tables and table variables, so that we can have a better understanding of temporary tables and table variables. In this chapter, we will describe some features from the following aspects:

Constraint Index I/0 overhead scope memory locations others

Example description

Constraint (Constraint)

You can create a Constraint for both temporary tables and table variables. For Table variables, Constraint can be added only when defined.

E.g. in Microsoft SQL Server Management Studio (MSSMS) query, create a temporary table and create a Constraint scenario, <脚本s1.>

 tempdb

 object_id()    
   #1 

  #1
(
 ID ,
 Nr nvarchar(50)  ,
 OperationTime datetime  (getdate()),
  PK_#1_ID   (ID)
)

  #1   CK_#1_Nr (Nr    )

<SCRIPT S1.>, you can see that when creating a temporary table #1, create a Constraint such as "Constraint PK _ # 1_ID Primary Key (ID )", you can also create a Constraint after creating a temporary Table #1, for example, "Alter Table #1 Add Constraint CK _ # modify Nr Check (Nr Between '000000' And '000000 ')", in the following table variable scenario, you cannot specify a Constraint name when defining a table variable. After defining a table variable, you cannot create a Constraint for the table variable.

E.g. The Constraint name cannot be specified when defining table variables. <代码s2.>

 tempdb

 @1 
(
 ID ,
 Nr nvarchar(50)  ,
 OperationTime datetime  (getdate()),
  [PK_@1_ID]   (ID)
)

You cannot create a Constraint for a table variable after defining the table variable, <代码s3.>

 tempdb

 @1 
(
 ID    ,
 Nr nvarchar(50),
 OperationTime datetime  (getdate())
)

  @1   [CK_@1_Nr] (Nr    )

In <代码s2.> And <代码s3.> It can be found that an error occurs in parsing the T-SQL syntax process, that is, SQL Server does not support defining table variables for the Constraint name, nor does it support creating a Constraint after defining table variables.

Note with caution that <代码s1.> When creating a Constraint for a temporary table, especially in a concurrent scenario, do not specify a specific Constraint name. Otherwise, an error message indicating that the object already exists will occur.

E.g. Before executing the command in MSSMS <代码s1.> Create a temporary table #1. If the current session is not closed, create another query and run <代码s1.> The same Code,

The query window on the left is the execution of the original <代码s1.> , The query window on the right is followed by the same execution <代码s1.> . Here, we pay attention to the Red Circle and find that a primary key name "PK _ # 1_ID" is explicitly given in the process of creating a temporary table #1 ", when the same temporary table #1 is created on the right, the object repetition error occurs. You can also use the System View sys. objects provided by SQL Server to query information that restricts "PK _ # 1_ID,

 tempdb



 *  sys.objects  name=

In the system view sys. objects, we found that the "PK _ # 1_ID" name does not contain any random numbers to indicate that different sessions have different objects. According to SQL Server's description rules for sys. objects, the Name column data in sys. objects is unique. When another session creates the same object, the error of repeated objects will occur.

In Constraint, Foreign Key cannot be used with table variables. For temporary tables, creating a Foreign Key is meaningless. That is to say, the temporary table is not subject to the Foreign Key constraint. The following example describes the temporary table,

E.g. <SCRIPT S4.>

 tempdb

 object_id()   
   #1 

 object_id()   
   #2 

  #1
(

 ID ,
 Nr nvarchar(50)  ,
 OperationTime datetime (getdate()),
  PK_#1_ID  (ID)
)
  #1   CK_#1_Nr (Nr    )
  #2
(
 ID   ,
 ForeignID    , (ForeignID)  #1(ID)
)
Go

We can see that the temporary table does not have a mandatory Foreign Key constraint. You can also use the SQL Server System View sys. foreign_keys to query

 tempdb

 *  sys.tables  name  
 *  sys.foreign_keys

In the query on the right, only the temporary tables #1 and #2 created in the sys. tables Table exist. The Foreign Key constraint information cannot be found in sys. foreign_keys. This also verifies the SQL Server prompt on the left. The Foreign Key constraint cannot be forcibly used in the temporary table.

Index)

In terms of indexes, temporary tables and table variables are similar to Constraint analysis. In temporary tables, indexes can be created like real tables. You can also create unique and clustered indexes during table variable definition.

E.g. <SCRIPT S5.>

 tempdb



 @1 ( 

 ID    ,

 Nr nvarchar(50)  

)

Insert  @1 (id,Nr) (1,)

Insert  @1 (id,Nr) (2,)

Insert  @1 (id,Nr) (8,)

Insert  @1 (id,Nr) (3,)

Insert  @1 (id,Nr) (7,)

  2 *

  sys.indexes  a

   sys.tables  b  b.object_id=a.object_id

   b.create_date 

 Nr  @1  Nr=

The above two graphs are captured. the first figure describes clustering Primary Key in Table variables and creates non-clustering Unique constraints, the second figure describes the query statement "Select Nr From @ 1 Where Nr = '000000'" Applied to the unique index "UQ _ #…" created in the table variable _#......"

This is an example of a temporary table index. Here we create an index for the temporary table and give the index a specific name, whether the test will be repeated.

E.g. Add two queries in MSSMS and write the following SQL statement:

<SCRIPT S6.>

 tempdb

 object_id()    
   #1 

  #1
(
 ID   ,
 Nr nvarchar(50)  ,
 OperationTime datetime  (getdate()),
)

   IX_#1_Nr  #1(Nr )

 b.name  TableName,
 a.* 
  sys.indexes  a
   sys.tables  b  b.object_id=a.object_id
  b.name  
   b.create_date Asc

From the returned results, we can see that in the System View Sys. Indexes, there are two identical Indexes "IX _ # collate Nr" created, but note that the object_id data is different. In SQL Server, different table index names can be the same. In a concurrent environment, you can specify the name of the index created for the temporary table based on the principle. Errors may occur only when the concurrency occurs due to duplicate table names, duplicate Constraint, or insufficient system resources.

I/0 overhead

Temporary tables and table variables are described in the I/O overhead. We use a special example to describe them and add two new queries on MSSMS, enter the test code for the temporary table and table variables respectively:

E.g. <SCRIPT S7.> temporary table:

 tempdb

 object_id()    
   #1 

  #1
(
 ID   ,
 Nr nvarchar(50)  ,
 OperationTime datetime  (getdate())
)

Insert  #1(ID,Nr,OperationTime)
  50000 row_number() (  a.object_id),(a.name+b.name,50) ,a.create_date
  master.sys.all_objects  a ,sys.all_columns  b
  type=



 Nr,(Nr)  Sum_ 
  #1 
  Nr   
   Nr

<SCRIPT S8.> table variables:

 tempdb

 @1 
(
 ID   ,
 Nr nvarchar(50)  ,
 OperationTime datetime  (getdate())
)

Insert  @1(ID,Nr,OperationTime)
  50000 row_number() (  a.object_id),(a.name+b.name,50) ,a.create_date
  master.sys.all_objects  a ,sys.all_columns  b
  type=


 Nr,(Nr)  Sum_ 
  @1 
  Nr   
   Nr

<SCRIPT S7.> and <SCRIPT S8.> mainly refer to the overhead of the last query statement I/O. What is the difference between the two. Through the graphic description of the preceding running results, we can see that at the beginning of the query, both temporary tables and table variables use the Clustered Index Scan. Although the two return the same data, however, I/O costs are different. The I/O overhead of a temporary table is 0.324606, while the table variable is only 0.003125, which is very different. In the Execution Plan graph of the temporary table, we found that the row "index missing (affects 71.9586): CREATE ......)" Prompt information. For the temporary table #1, create a non-clustered index on the field "Nr" and then view the execution result:

   IX_#1_Nr  #1(Nr)

We have created the index "IX _ # collate Nr" on the temporary table #1. It is very interesting to run it and view the figure above. Index Seek is used for the #1 query of a temporary table, and the I/O overhead is reduced to 0.0053742. Although the I/O overhead at the start of the query is greater than that when the table variable starts the query, the execution step is less than the variable, and the "Sort" overhead is reduced, then, the final result of the Select statement is returned, and the cost of the subtree is estimated to be much lower than that of the table variable.

The example here only describes a special case. In a real environment, you need to determine whether to use temporary tables or table variables based on the actual data volume. If the data volume is very small, such as less than 50 rows of records, and the data occupies no more than 1 page, using Table variables is a good solution.

Scope)

Table variables, like local variable, have narrow scopes and can only be applied to defined functions, stored procedures, or objects. For example, if there are several variables in a session, the table variables can only be used within the scope of its definition. Others cannot call it any more.

E.g. Add a query in MSSMS and write the <SCRIPT S9.>

Tempdb

Nocount
@ 1 (
ID,
Nr nvarchar (50)
)
Insert @ 1 (id, Nr) (1 ,)
Insert @ 1 (id, Nr) (2 ,)
Insert @ 1 (id, Nr) (8 ,)
Insert @ 1 (id, Nr) (3 ,)
Insert @ 1 (id, Nr) (7 ,)

* @ 1

-- End point

* @ 1

<SCRIPT S9.> the query is equivalent to an end point described by "Go. If the table variables defined before "Go" are called after "Go", an error message "variables must be declared" is returned.

Unlike table variables, the temporary table has a valid scope for the current session until the session ends or the temporary table is dropped. That is to say, it can span several scopes of the current session.

E.g. <SCRIPT S10.>

Tempdb

Object_id ()
#1

#1
(
ID,
Nr nvarchar (50 ),
OperationTime datetime (getdate ()),
PK _ # 1_ID (ID)
)
* #1

-- End point

* #1

<SCRIPT S10.> we can see that temporary table #1 can be queried before and after "GO.

When describing the scope of temporary tables and table variables,Note that when the sp_executesql or Execute statement executes a string, the string is executed as its self-contained. if table variables are defined before sp_executesql or Execute statements, External table variables cannot be called in the strings of sp_executesql or Execute statements.

E.g. <SCRIPT S11.>

 tempdb

 nocount 
 @1 ( 
 ID    ,
 Nr nvarchar(50)  
)
Insert  @1 (id,Nr) (1,)
Insert  @1 (id,Nr) (2,)
Insert  @1 (id,Nr) (8,)
Insert  @1 (id,Nr) (3,)
Insert  @1 (id,Nr) (7,)

 *  @1

(N)

<SCRIPT S11.> when "Execute (n' Select * From @ 1')" is executed, the same error message as <SCRIPT S9.> indicates that the variable @ 1 must be declared ".

Temporary tables can be called in execution strings of sp_executesql or Execute statements. I will not give an example here. If you are fuzzy, you can refer to <SCRIPT S11.> converting table variables into temporary tables for further understanding and memory.

Storage location

When talking about the storage location of temporary tables and table variables, we can see many versions, especially table variables. Some say that the table variable data is stored in the memory, some say that the data is stored in the database tempdb, some say that some are stored in the memory, and some are stored in the database tempdb. According to the official information I found, it is in SQL Server 2000:

"A table variable is not a memory-only structure. because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. table variables are created in the tempdb database similar to temporary tables. if memory is available, both table variables and temporary tables are created and processed while in memory (data cache )."

In SQL Server 2005 \ SQL2008, table variable storage is similar to a temporary table, which is created in the database tempdb and used in the tempdb storage space.

E.g. <SCRIPT S12.> temporary table

 tempdb

 nocount 

 sp_spaceused 

 object_id()    
   #1 

  #1(ID  ,Nr nvarchar(50))
Insert  #1 (ID,Nr)
  (1) row_number() (  a.object_id),(a.name+b.name,50)
  sys.all_objects  a,
 sys.all_columns  b 

 (1) name,object_id,type,create_date  sys.tables   create_date  

 sp_spaceused 

After <SCRIPT S12.> is executed, we can see that the table sys. tables in the database tempdb Creates table #1. Next, let's take a look at the space usage. Before data is inserted, the database's unused space (unallocated space) is 510.39 MB. After inserting 1 piece of data into the temporary table #1, the unused database space is 501.38 MB, and the unused space is reduced. Let's take a look at the changes in the space used by the data in the entire database, from 552KB to 560KB, and use a one-page data space (8 KB ). This indicates that temporary tables use the database tempdb space even if you insert only one data entry. Someone may ask what if I only create a temporary table #1 without inserting data. The result is as follows:

Here, you will find that the size of the front and back space remains unchanged. However, do not consider that the data space in the database tempdb is not used. When multiple users create a temporary table structure, you will find that it will actually be applied to the database tempdb space. I have created 10 #1 here and the effect is as follows:

With the same principle, we use a similar method to test the table variables. We found that the conclusion is consistent with that of the temporary table and will use the database tempdb space.

E.g. <SCRIPT S13.> table Variables

 tempdb

 nocount 
 sp_spaceused 

 @1 (ID  ,Nr nvarchar(50))
Insert  @1 (ID,Nr)
  (1) row_number() (  a.object_id),(a.name+b.name,50)
  sys.all_objects  a,
 sys.all_columns  b 

 (1) name,object_id,type,create_date  sys.objects  type=   create_date  

 sp_spaceused 


 sp_spaceused 

<SCRIPT S13.>, I wrote another "GO" and checked the storage process sp_spaceused. In this way, we can better reflect the changes in the use space of table variables. From the result graph before and after data insertion, the table variables not only create a table structure like # 267ABA7A in the database tempdb, but also apply the table variables to the database tempdb space. However, note that after "Go", the table variable @ 1 is immediately released. In order to better reflect the use of space. We can insert a large amount of data into the table variable @ 1 to check the space changes (test inserting 10 million rows of data ).

E.g. <SCRIPT S14.>

 tempdb

 nocount 
 sp_spaceused 

 @1 (ID  ,Nr nvarchar(50))
Insert  @1 (ID,Nr)
  (10000000) row_number() (  a.object_id),(a.name+b.name,50)
  sys.all_objects  a,
 sys.all_columns  b 

 (1) name,object_id,type,create_date  sys.objects  type=   create_date  

 sp_spaceused 


 sp_spaceused 

Here we can clearly see the changes in database tempdb size (database_size), from the 552.75MB before data insertion to the 892.75 MB after data insertion. It is very interesting that we found that the database size is 892.75 MB after Go, but the data usage space (data) is from 560KB-> 851464KB-> 536KB, this indicates that SQL Server automatically releases the used data space, but does not immediately release the disk space allocated by the database. In the actual environment, we found that the disk space used by the temporary database tempdb is growing, which is one of the reasons.

Others

Temporary tables and table variables have other features, such as transaction rollback of temporary tables, and table variables are not affected by transaction rollback. In terms of transactions, a more correct statement is that the table variable transaction only exists during the update of the table variable. This reduces the need for table variables to lock and record resources.

E.g. <SCRIPT S15.>

 tempdb

 nocount 

 object_id()    
   #1 
  #1(ID  ,Nr nvarchar(50))
 @1 (ID  ,Nr nvarchar(50))

  

Insert  #1 (ID,Nr)
  (1) row_number() (  a.object_id),(a.name+b.name,50)
  sys.all_objects  a,
 sys.all_columns  b 


Insert  @1 (ID,Nr)
  (1) row_number() (  a.object_id),(a.name+b.name,50)
  sys.all_objects  a,
 sys.all_columns  b 

  

 *  #1
 *  @1

After "Rollback Tran" is found, no data is inserted in the temporary table #1, and another data exists in the table variable @ 1. The table variables are not affected by "Rollback Tran. Its behavior is similar to local variables.

In addition, SQL Server does not retain any statistical information for table variables. Because of this, we use table variables when there is a large amount of data, and it is much slower than a temporary table. We have a special example of I/O overhead.

Summary

In any case, temporary tables and table variables have their own characteristics and have their own advantages and disadvantages. They can be flexibly applied in different scenarios. This article is about my understanding of temporary tables and table variables. It may be inappropriate or missing in some places. You can leave a message or Email to contact me. I will continue to improve or correct the situation, I also don't want some wrong opinions to mislead others. As I said, "I 'd hate to think of anyone being misled by my advice! ".

Appendix: