Differences between SQL Server temporary tables and table variables in operations

This article mainly describes the SQL Server temporary tables and table variables, as well as the differences between SQL Server temporary tables and table variables, when using tables in a database, we generally encounter two ways to use tables, namely 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.

 

SQL Server 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:

 

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:

 

Alter table dbo. # news Add constraint [df_newsdatetime] default (getdate () for [newsdatetime], primary key clustered ([news_id]) on [primary] Go SQL Server temporary tables can be modified after they are created, 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 table variable creation is similar to that for SQL Server temporary tables. The difference is that the table variable must be named during creation. 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:

 

Declare @ news table (news_id int not null,

Newstitle varchar (100), newscontent varchar (2000), newsdatetime)

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: