SQL Server table variables and temporary tables

The temporary table is stored in tempdb,It is automatically deleted when it is no longer in use.

Temporary tables can be local or global. They differ in terms of name, visibility, and availability. The name of the local temporary table starts with a single digit (#). They are only visible to the current user connection and are deleted when the user is disconnected from the SQL server instance. The name of the global temporary table starts with two numeric symbols (#). After being created, the table is visible to all users. When all users that reference the table are disconnected from SQL Server, the table is deleted.

For example, if you have created an employees table, you can use the table unless it has been deleted. If a local temporary table # employees is created for a database session, only the table can be used by the session. After the session is disconnected, the table is deleted. If the # employees global temporary table is created, any user in the database can use the table. If the table is not used by other users after you create it, delete it when you disconnect it. If another user is using the table after you create the table, SQL Server will delete the table when you disconnect it and all other sessions no longer use it.

Temporary table

Temporary tables are stored in the tempdb database. All users using this SQL server instance share this tempdb, because we should ensure that the hard disk used to store the tempdb database has enough space, to make it grow on its own. it is best to store it on an independent hard disk controller. because there is no competition with other hard disk I/O.

Many of usProgramThe temporary table may be shared by multiple connections. in fact, there are two types of temporary tables in SQL Server: local temporary tables and global temporary tables. Local temporary tables are identified by the # prefix, and can only be used by the connection that creates it. the global temporary table is identified by the # prefix and can be shared with other connections.

Local temporary table

The reason that the local temporary table cannot be shared by other connections is that in SQL Server 2000, a unique character is automatically appended to the table name of the local temporary table. For example:

Create Table [# dimcustomer_test]

(

[Customerkey] [int]

, [Firstname] [nvarchar] (50)

, [Middlename] [nvarchar] (50)

, [Lastname] [nvarchar] (50)

)

Now let's take a look at the sysobjects table in tempdb. We will find that the newly created temporary table # dimcustomer_test has been suffixed:

Use tempdb

Go

Select name from sysobjects where name like '% dimcustomer %'

The result is:

Name

# Dimcustomer_test ___________________________________________________________________________________________________________ 000000000005

Global temporary table

Let's take a look at the global temporary table:

Create Table [# dimcustomer_test]

(

[Customerkey] [int]

, [Firstname] [nvarchar] (50)

, [Middlename] [nvarchar] (50)

, [Lastname] [nvarchar] (50)

)

Now let's take a look at the sysobjects table in tempdb. We will find that the newly created temporary table # dimcustomer_test is not suffixed:

Use tempdb

Go

Select name from sysobjects where name like '% dimcustomer %'

The result are:

# Dimcustomer_test ___________________________________________________________________________________________________________ 000000000005

# Dimcustomer_test

-- Drop test temp tables

Drop table [# dimcustomer_test]

Drop table [# dimcustomer_test]

The name of the created global temporary table is not identified.

Table Variables

Table variables and temporary tables are no different for our users, but they are different in terms of storage. Table variables are stored in the memory. therefore, the performance is better than that of temporary tables!

Another difference is that you need to specify an alias for the table variable when using the table variable in the table connection. For example:

Use adventureworksdw

Go

Declare @ dimcustomer_test table

(

[Customerkey] [int]

, [Firstname] [nvarchar] (50)

, [Middlename] [nvarchar] (50)

, [Lastname] [nvarchar] (50)

)

--- Insert data to @ dimcustomer_test

Insert @ dimcustomer_test

(

[Customerkey]

, [Firstname]

, [Middlename]

, [Lastname]

)

Select

[Customerkey]

, [Firstname]

, [Middlename]

, [Lastname]

From dimcustomer

Select [@ dimcustomer_test]. customerkey, sum (factinternetsales. orderquantity)

From @ dimcustomer_test inner join factinternetsales on

@ Dimcustomer_test.customerkey = factinternetsales. customerkey

Group by customerkey

Result:

Server: MSG 137, Level 15, state 2, line 32

Must declare the variable'@ Dimcustomer_test'.

If we make changes to the preceding query, use the alias for the query (and open Io ):

----- In the follow script, we used the table alias.

Declare @ dimcustomer_test table

(

[Customerkey] [int]

, [Firstname] [nvarchar] (50)

, [Middlename] [nvarchar] (50)

, [Lastname] [nvarchar] (50)

)

Insert @ dimcustomer_test

(

[Customerkey]

, [Firstname]

, [Middlename]

, [Lastname]

)

Select

[Customerkey]

, [Firstname]

, [Middlename]

, [Lastname]

From dimcustomer

Select T. customerkey, F. orderquantity

From @ dimcustomer_test t inner join factinternetsales F on

T. customerkey = f. customerkey

Where T. customerkey = 13513

Table variables are automatically deleted by the system at the end of batch processing, so you do not need to delete them as they are displayed in the temporary table.
Table variables mainly overwrite the system memory, while temporary tables use tempdb. Table variables can be used for intermediate data storage with small data volumes. When the amount of data to be temporarily saved is large, we recommend that you use temporary tables. The specific use of table variables or temporary tables can be adjusted according to the system running status.

========================================================== ================

For example, if you create a project named employees
If you have the security permission to use the table in the database, you can use the table unless it has been deleted. If you create a project named # employees
To delete a local temporary table. If you create a project named # employees
Global temporary table. Any user in the data table can perform operations on the table. If the table is not used by other users after you create it, delete it when you disconnect it. If the table is created by another user
SQL Server deletes the table after all users are disconnected.

========================================================== ==================

The non-index view is just a definition and does not store data. Data is retrieved from the base table only during query.

The index view stores data.

Both the index view and temporary table data are stored on the hard disk.

Data in the index view is stored in the database file where the view is located.

Temporary table data is stored in the database file tempdb.

Question 1: Why do I need to introduce table variables when a temporary table already exists?

Solution 1: Compared with a temporary table, table variables have the following advantages: • as described in table, table variables (such as local variables) with a clearly defined range, these table variables are automatically cleared at the end of the range.
• Compared with temporary tables, table variables cause less re-Compilation of stored procedures.
• Transactions involving table variables only maintain the update duration on table variables. Therefore, when using Table variables, You need to lock and record resources in less cases. Because table variables have a limited range and are not part of a persistent database, transaction rollback does not affect them.
Question 2: What does it mean if the use of table variables is less than the use of temporary tables resulting in re-Compilation of stored procedures?

Solution 2:ArticleSome reasons for re-compiling the stored procedure are discussed:

243586 (http://support.microsoft.com/kb/243586/) Stored Procedure recompilation troubleshooting
The "recompilation caused by some temporary table operations" section also lists the requirements that need to be met to avoid some problems (for example, re-compilation due to the use of temporary tables. These restrictions are not applicable to table variables.

Table variables are completely independent from the batch for creating these table variables. Therefore, when you execute create or alter
When a temporary table is used, "re-parsing" may not occur ". The temporary table needs to be reparsed to reference the table from the nested stored procedure. Table variables completely avoid this problem.
Therefore, stored procedures can use compiled plans to save resources for processing stored procedures.

Question 3: What are the defects of table variables?

Solution 3: Compared with a temporary table, it has the following drawbacks: • non-clustered indexes cannot be created on table variables (except for system indexes created for the primary or unique constraints ). Compared with temporary tables with non-clustered indexes, this may affect query performance.
• Table variables do not maintain statistical information as temporary tables do. In table variables, statistical information cannot be created automatically or using the create statistics statement. Therefore, when performing complex queries on large tables, the lack of statistical information may affect the optimizer's determination of the optimal query plan, thus affecting the query performance.
• The table definition cannot be changed after the initial declare statement.
• Table variables cannot be used in insert exec or select into statements.
• Check constraints, default values, and calculated columns in the table type declaration cannot call user-defined functions.
•
If the table variable is created outside the exec statement or sp_executesql stored procedure, the exec statement or sp_executesql statement cannot be used.
Stored Procedures to run the dynamic SQL Server query that references the table variable. Because table variables can only be referenced in their local scopes, exec statements and
The sp_executesql stored procedure will be out of the scope of the table variable. However, you can use the exec statement or sp_executesql
Create Table variables and execute all the processing in the stored procedure, because the local scope of the table variables will be in the exec statement or sp_executesql stored procedure.
Problem 4: Compared with a temporary or permanent table, the table variable only exists in the memory structure to ensure better performance, is it because they are maintained in databases residing on physical disks?

Solution 4: The table variable does not exist only in the memory structure. Because the table variables may retain a large amount of data and cannot accommodate the data in the memory, it must have a location on the disk to store the data. Similar to a temporary table, table variables are created in the tempdb database. If enough memory is available, both the table variables and temporary tables are created and processed in the memory (data cache.

Question 5: Must I use table variables instead of temporary tables?

Answer 5: The answer depends on three factors: • number of rows inserted into the table.
• The number of times the query is recompiled.
• Query types and their dependence on performance indexes and statistical information.
In some cases, you can split a stored procedure with a temporary table into multiple smaller stored procedures for recompilation on smaller units.

Table variables should be used whenever possible unless the data volume is large and the table needs to be reused. In this case, you can create an index on a temporary table to improve query performance. However, different solutions may be different. Microsoft recommends that you perform a test to verify whether table variables are more effective for specific queries or stored procedures than temporary tables.