The difference between a temporary table and a table variable in SQL Server

When we use a table in a database, we often encounter two ways to use the table, namely, the use of temporary tables and table variables. In practical use, how can we flexibly use them in stored procedures, although they implement the function is basically the same, how can there be time in a stored procedure to use temporary tables instead of using table variables, sometimes to use table variables instead of using temporary tables?

　　Temp table

A temporary table is similar to a permanent table, except that it is created in tempdb and only disappears after the end of a database connection or by a SQL command Drop, otherwise it will persist. Temporary tables generate the system logs of SQL Server when they are created, although they are in tempdb and are allocated in memory, they also support physical disks, but the user cannot see the files on the specified disk.

Temporary tables are both local and global, and the name of the local temporary table is prefixed with "#", which is visible only in the local current user connection and is deleted when the user disconnects from the instance. The names of global temporary tables are prefixed with "# #" and are visible to any user after they are created, and are deleted when all users referencing the table are disconnected.

Let's take a look at 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 statistics, so you can use Data Definition language (DDL) declarations to block restrictions, constraints, and referential integrity, such as primary key and foreign key constraints, that temporary tables Add. For example, we now add a default GetData () Current date value for the #news table field Newsdatetime, and add a primary key for news_id, and 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

Temporary tables can modify many of the defined options after they are created, including:

1) Add, modify, and delete columns. For example, the name, length, data type, precision, scale, and nullability of a column can be modified, but there are some limitations.

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

3) You can add or remove UNIQUE and CHECK constraints and DEFAULT definitions (objects).

4) You can use the IDENTITY or ROWGUIDCOL property to add or remove identifier columns. Although the ROWGUIDCOL property can be added to an existing column or deleted from an existing column, only one column can have that property at any time in the table

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

　　Table variables

A table variable creates a syntax similar to a temporary table, and the difference is that it must be named when it is created. Table variables are one of the variables, the table variable is also divided into local and global two, the name of the surface variable is "@" prefix, only in the local current user connection can be accessed. The name of the global table variable is prefixed with "@@", which is generally the global variable of the system, as we commonly use, such as @ @Error represents the wrong number, @ @RowCount represents the number of rows affected.

Let's look at the statement that creates the table variable:

DECLARE @News Table ( news_id int not NULL, Newstitle varchar (100), Newscontent varchar (2000), Newsdatetime datetime )

Comparing temporary tables and table variables can be done through SQL selection, insert, UPDATE, and DELETE statements, and their differences are mainly reflected in the following:

1) The table variable is stored in memory, when the user accesses the table variable, SQL Server does not generate the log, and in the temporary table is generated log;

2) in a table variable, a nonclustered index is not allowed;

3) The table variable is not allowed to have default defaults, and does not allow constraints;

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

5) There is a locking mechanism in the temporal table, and there is no mechanism for locking in the table variable.

Let's look at a complete example of the similarities and differences in their usage:

　　Using temporal 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 cases achieve the same effect, the first use of temporary tables when the temporary table is generally created, if at the time of execution, not through the drop table operation, the second time can no longer be created, and the definition of table variables do not need to do a drop table operation, Once execution is complete, it disappears.

In fact, when choosing a temporary table or a table variable, most of the time we use it is possible, but generally we need to follow the following situation, choose the corresponding way:

1) The main thing to consider when using table variables is the pressure on the memory of the application, and if the code runs a lot of instances, pay special attention to memory variables ' memory consumption. We use table variables for smaller data or for recommended calculations. If the result of the data is large, in the code for the temporary calculation, when the selection is not a grouping of aggregations, you can consider using table variables.

2) generally for large data results, or because the statistical data to facilitate better optimization, we recommend the use of temporary tables, but also to create an index, because the temporary table is stored in tempdb, the general default allocation of less space, you need to tune tempdb to increase its storage space.

      1. Causes the transaction log to not log table variables. Therefore, they are out of the scope of the transaction mechanism.

      2. Any stored procedure that uses a temporary table is not precompiled, but the execution plan of the stored procedure that uses the table variable can be pre-statically compiled. The main benefit of precompiling a script is that it speeds up execution. This benefit is more significant for long stored procedures because the cost of recompiling is too high for it.

      3. Table variables exist only in the same range where those variables can be present. As opposed to temporary tables, they are in internal stored procedures and exec string "the statement is not visible. They also cannot be in insert/exec statement used.

The difference between a temporary table and a table variable in SQL Server