Differences between temporary tables and table variables in SQL Server

 

When using tables in a database, we often encounter two ways to use tables: using temporary tables and table variables. In actual use, how can we flexibly use them in the stored procedure? Although they implement the same functions, in a stored procedure, how does one sometimes use temporary tables instead of table variables, and sometimes use table variables instead of temporary tables?

Temporary table

A temporary table is similar to a permanent table, but it is created in tempdb. It is only after a database connection ends orSQLCommand drop to disappear, otherwise it will always exist. Temporary tables generate SQL Server System logs when they are created. Although they are reflected in tempdb, they are allocated in memory and support physical disks, but the user cannot see the file in the specified disk.

Temporary tables can be divided into local and global tables. The names of local temporary tables are prefixed with "#" and are only visible in local user connections, the user is deleted when the instance is disconnected. The name of the global temporary table is prefixed with "#". After the table is created, it is visible to all users. When all users that reference the table are disconnected, the table is deleted.

Here is an example of creating a temporary table:

Create Table DBO. # News ( News_idInt NotNull, Newstitle varchar (100 ), Newscontent varchar (2000 ), Newsdatetime datetime )

Temporary tables can create indexes or define statistical data. Therefore, you can use the Data Definition Language (DDL) statement to block restrictions and constraints added to temporary tables, and reference integrity, such as primary key and foreign key constraints. For example, to add a default getdata () current date value for the # News table field newsdatetime and add a primary key for news_id, we can use the following statement:

Alter table dbo. # News Add Constraint [df_newsdatetime] default (getdate () for [newsdatetime], Primary Key clustered ( [News_id] ) On [primary] Go

After creating a temporary table, you can modify many defined options, including:

1) Add, modify, and delete columns. For example, the column name, length, data type, precision, number of decimal places, and null can be modified, but there are some restrictions.

2) You can add or delete primary keys and foreign key constraints.

3) You can add or delete unique and check constraints and default definitions (objects ).

4) You can use the identity or rowguidcol attribute to add or delete an identifier column. Although the rowguidcol attribute can be added to or deleted from an existing column, only one column in the table can have this attribute at any time.

5) The selected columns in the table and table have been registered as full-text indexes.

Table Variables

The syntax for creating table variables is similar to that for a temporary table. The difference is that you must name the table variables when creating them. Table variables are one type of variables. Table variables are also divided into local and global variables. The names of local table variables are prefixed, only the local user connection can be accessed. The names of global table variables are prefixed with "@". Generally, they are system global variables. For example, @ error indicates the error number, @ rowcount indicates the number of affected rows.

For example, let's look at the statement for creating table variables:

Declare @ news table ( News_idInt NotNull, Newstitle varchar (100 ), Newscontent varchar (2000 ), Newsdatetime datetime )

You can select, insert, update, and delete SQL statements to compare temporary tables and table variables. Their differences are mainly reflected in the following:

1) Table variables are stored in the memory. When you access table variables, SQL server does not generate logs, but logs are generated in temporary tables;

2) Non-clustered indexes are not allowed in Table variables;

3) Table variables cannot have default values or constraints;

4) The statistical information on the temporary table is sound and reliable, but the statistical information on the table variables is unreliable;

5) The temporary table has a lock mechanism, but there is no lock mechanism in the table variables.

Let's take a complete example to see the similarities and differences between their usage:

Use temporary tables

Create Table DBO. # News ( News_id int not null, Newstitle varchar (100 ), Newscontent varchar (2000 ), Newsdatetime datetime ) Insert into DBO. # News (news_id, newstitle, newscontent, newsdatetime) Values (1, 'bluegreen', 'austen ', 200801, getdate ()) Select news_id, newstitle, newscontent, newsdatetime from DBO. # News Drop table DBO. [# News]

Using table Variables

Declare @ newsTable ( News_idInt NotNull, Newstitle varchar (100 ), Newscontent varchar (2000 ), Newsdatetime datetime ) Insert into @ News (news_id, newstitle, newscontent, newsdatetime) Values (1, 'bluegreen', 'austen ', 200801, getdate ()) Select news_id, newstitle, newscontent, newsdatetime from @ news

We can see that the above two situations achieve the same effect. First, when using a temporary table, the temporary table is generally created, if the drop table operation is not performed, the table cannot be created again for the second time, and you do not need to perform the drop table operation to define the table variables. After one operation is completed, the table disappears.

In fact, we can use temporary tables or table variables in most cases, but generally we need to follow the following situation and select the corresponding method:

1) The main consideration for using Table variables is the application.ProgramMemory pressure, ifCodeThere are many running instances. Pay special attention to the memory consumption of memory variables. We recommend using Table variables for small data or computed data. If the data results are large and used for temporary computation in the Code, there is no group aggregation during the selection, you can consider using Table variables.

2) We recommend that you use temporary tables and create indexes for big data results or statistical data to facilitate better optimization, because temporary tables are stored in tempdb, the space allocated by default is very small. You need to optimize tempdb to increase the storage space.

Supplement:

In the temporary table

Create Table # T (...)

And table Variables

Declare @ t table (...)

Mainly include3Theoretically different.

 

The first difference makes transaction logs not record table variables. As a result, they are out of the scope of the transaction mechanism and can be seen from the following example:

 

Create Table # T (s varchar (128 ))

Declare @ t table (s varchar (128 ))

Insert into # T select 'old value #'

Insert into @ t select 'old value @'

Begin transaction

Update # t set S = 'new value #'

Update @ t set S = 'new value @'

Rollback transaction

Select * from # T

Select * From @ t

 

S

---------------

Old value #

 

S

---------------

New value @

 

Declare a temporary table# TAnd table Variables@ TThen, assign them the same valueOld ValueString. Then, start a transaction to update them. At this time, they all have new identical values.New ValueString. But when the transaction rolls back, as you can see, the table Variables@ TThe new value is retained without returning it.Old ValueString. This is because even if the table variable is updated in the transaction, it is not a part of the transaction.

 

The second major difference is that any stored procedure using a temporary table will not be pre-compiled. However, the execution plan of the stored procedure using Table variables can be compiled statically in advance. The main advantage of precompiling a script is that it accelerates execution. This benefit is even more significant for long stored procedures because re-compilation is too costly.

 

Finally, table variables exist only in the same range that can exist. Unlike temporary tablesExec(String) Statement is invisible. They cannotInsert/execStatement.

 

 

 

Table variable vs. Temporary table

	Table Variables	Temporary table
Data Set storage location	Memory (not considering switching to page files)	Disk (cache to memory after access is not considered)
Logs required?	No	Yes
Can I create an index?	No	Yes
Can statistics be used?	No	Yes
Whether it can be accessed in multiple sessions	No	Yes
Lock required?	No	Yes

Conclusion

In summary, we will find that there are many differences in the underlying processing mechanism between temporary tables and table variables.

To sum up, we recommend that you use table variables for small temporary computation datasets. If the dataset is large, if it is used for temporary computing in the code, and such temporary use is always a simple full dataset scan without any optimization, for example, table variables can be used if no group or few groups are aggregated (such as Count, sum, average, and Max. Another consideration for using Table variables is the memory pressure in the application environment.InstanceMany, pay special attention to the memory consumption of memory variables.

We recommend that you use temporary tables for large datasets, create indexes at the same time, or use the statisitcs automatic creation and maintenance function to optimize access to SQL statements. If you need to exchange data between multiple user sessions, the temporary table is the only choice. It should be mentioned that the temporary tables are stored in tempdb, so you should pay attention to the optimization of tempdb.