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_id int not null, 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_id int not null, 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 @ News table ( News_id int not null, 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 memory pressure of applications. If there are many running instances of code, pay special attention to the memory consumption caused by 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:
1.So that transaction logs do not record table variables. Therefore, they are out of the scope of the transaction mechanism.

2.Any stored procedure using a temporary table is not pre-compiled. However, the execution plan of the stored procedure using Table variables can be pre-compiled statically. 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.

3.Table variables exist only in the same range that can exist. Unlike temporary tables, they are invisible in internal stored procedures and exec (string) statements. They cannot be used in insert/exec statements.