Analysis of the difference between temporary table and table variable in SQL Server

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:

Copy CodeThe code is as follows:
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:

Copy CodeThe code is as follows:
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, there can be only one column in the table that can have that property at any time.

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:

Copy CodeThe code is as follows:
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

Copy CodeThe code is as follows:
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

Copy CodeThe code is as follows:
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.




3) If you want to return a table in the custom function, use the table variable such as:

Copy CodeThe code is as follows:
Dbo.usp_customersbypostalcode

(@PostalCode VARCHAR (15))
RETURNS
@CustomerHitsTab TABLE (
[CustomerID] [NCHAR] (5),
[ContactName] [nvarchar] (30),
[Phone] [nvarchar] (24),
[Fax] [nvarchar] (24)
)
As
BEGIN
DECLARE @HitCount INT

INSERT into @CustomerHitsTab
SELECT [CustomerID],
[ContactName],
[Phone],
[Fax]
from [Northwind]. [dbo]. [Customers]
WHERE PostalCode = @PostalCode

SELECT @HitCount = COUNT (*)


From @CustomerHitsTab

IF @HitCount = 0
--no Records Match Criteria
INSERT into @CustomerHitsTab (
[CustomerID],
[ContactName],
[Phone],
[Fax])
VALUES (' ', ' No Companies in area ', ' ', ')
RETURN
END
GO

Analysis of the difference between temporary table and table variable in SQL Server