Differential analysis of temporary tables and table variables in SQL Server _mssql

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 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 (MB), 
newscontent varchar (2000), c6/> 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 K EY CLUSTERED 
( 
[news_id] 
) on [PRIMARY] 

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:

Copy Code code 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 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

Copy Code code 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 Code code 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 situations achieve the same effect, the first way to use a temporary table, when the temporary table is generally created, if at the time of execution, not through the drop table operation, the second can not be created, and the definition of table variables do not need to do the drop table operation, 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.




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

Copy Code code 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 the area ', ', ', ')
Return
End
Go