Differences between SQL Server temporary tables and table Variables

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 its creation is in tempdb. It disappears only after a database connection is completed or the SQL command is drop, 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:

SQL code

1. Create Table DBO. # News
2. (
3. News_id int not null,
4. Newstitle varchar (100 ),
5. Newscontent varchar (2000 ),
6. Newsdatetime datetime
7. )

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:

SQL code

1. Alter table dbo. # News
2. Add
3. Constraint [df_newsdatetime] default (getdate () for [newsdatetime],
4. Primary Key clustered
5. (
6. [News_id]
7. ) On [primary]
8. Go

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:

SQL code

1. Declare @ news table
2. (
3. News_id int not null,
4. Newstitle varchar (100 ),
5. Newscontent varchar (2000 ),
6. Newsdatetime datetime
7. )

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

SQL code

1. Create Table DBO. # News
2. (
3. News_id int not null,
4. Newstitle varchar (100 ),
5. Newscontent varchar (2000 ),
6. Newsdatetime datetime
7. )
8. Insert into DBO. # News (news_id, newstitle, newscontent, newsdatetime)
9. Values (1, 'bluegreen', 'austen ', 200801, getdate ())
10. Select news_id, newstitle, newscontent, newsdatetime from DBO. # News
11. Drop table DBO. [# News]

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

SQL code

1. Declare @ news table
2. (
3. News_id int not null,
4. Newstitle varchar (100 ),
5. Newscontent varchar (2000 ),
6. Newsdatetime datetime
7. )
8. Insert into @ News (news_id, newstitle, newscontent, newsdatetime)
9. Values (1, 'bluegreen', 'austen ', 200801, getdate ())
10. Select news_id, newstitle, newscontent, newsdatetime from @ news

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.

3) to return a table in a user-defined function, use the following table variables:

SQL code

1. DBO. usp_customersbypostalcode
3. (@ Postalcode varchar (15 ))
4. Returns
5. @ Mermerhitstab table (
6. [Customerid] [nchar] (5 ),
7. [Contactname] [nvarchar] (30 ),
8. [Phone] [nvarchar] (24 ),
9. [Fax] [nvarchar] (24)
10. )
11. As
12. Begin
13. Declare @ hitcount int
15. Insert into @ customerhitstab
16. Select [customerid],
17. [Contactname],
18. [Phone],
19. [Fax]
20. From [northwind]. [DBO]. [MERs]
21. Where postalcode = @ postalcode
23. Select @ hitcount = count (*)
26. From @ customerhitstab
28. If @ hitcount = 0
29. -- No records match criteria
30. Insert into @ customerhitstab (
31. [Customerid],
32. [Contactname],
33. [Phone],
34. [Fax])
35. Values ('', 'no companies in area ','','')
36. Return
37. End
38. Go

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