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

When we use tables in a database, we often encounter two ways to use a table, which is to use temporary tables and table variables. How can we use them flexibly in a stored procedure when we are actually using them, although they are basically the same functionality, how do you sometimes use a temporary table instead of a table variable in a stored procedure, or do you use a table variable instead of a temporary table?

Temporary tables

A temporary table is similar to a permanent table, except that it is created in tempdb, which disappears only after a database connection has been completed or dropped by the SQL command, otherwise it will always exist. Temporary tables generate SQL Server's system logs when they are created, although they are embodied in tempdb, are allocated in memory, and they support physical disks, but the user cannot see the files on the specified disk.

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

Let's 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 prevent temporary tables from adding restrictions, constraints, and referential integrity, such as primary and foreign key constraints. For example, we now add a default GetData () Current date value for #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

IF EXISTS (SELECT * fromtempdbsysobjects WHERE ID =object_id (' tempdb# #wzg_test ') and type= ' U ')

Begin

DROP TABLE[ADDON_SCM]. [# #wzg_test]

Select ' Droptable # #wzg_test '

End

Else

Begin

CREATE TABLE [ADDON_SCM]. [# #wzg_test]

(

ID int,

Name varchar (100)

)

Select ' CreateTable # #wzg_test '

End

IF EXISTS (SELECT * fromtempdbsysobjects WHERE ID =object_id (' tempdb# #wzg_test ') and type= ' U ')

Begin

insert into [ADDON_SCM]. [# #wzg_test] VALUES (1, ' Wzh ')

SELECT * FROM [ADDON_SCM]. [# #wzg_test]

End

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

1 Add, modify, delete columns. For example, the name, length, data type, precision, scale, and nullability of a column can be modified, except for a few limitations.

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

3 You can add or remove unique and CHECK constraints and default definitions (objects).

4 You can add or remove an identifier column using the identity or ROWGUIDCOL property. Although the ROWGUIDCOL property can also 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 selected columns in the table and table are registered as Full-text indexes.

Table variables

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

As we 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 embodied in the following:

1 table variables are stored in memory, when the user accesses the table variables, SQL Server is not generated by the log, and in the temporary table is generated log;

2 in the table variable, is not allowed to have nonclustered index;

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

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

5 There is a mechanism for locking in the temporary table, and there is no mechanism for locking in table variables.

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

Using temporary tables

CreateTable 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, the first use of temporary tables, temporary tables are generally created, if at the time of execution, not through the droptable operation, the second can not be created, and the definition of table variables do not need to do droptable operations, Once execution is complete, it disappears.

In fact, when we choose 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 appropriate way:

1 The main need to consider the use of table variables is the application of memory pressure, if the code run a lot of instances, we should pay special attention to memory variables of memory consumption. We use table variables for smaller data or for calculated recommendations. If the results of the data are large and are used in the code for temporary computations, you can consider using table variables when there are no grouped aggregations at the time of the selection.

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