Create Table # temporary temptable table and declare @ Bianliang table () Table Variables

In SQL server performance optimization, there is an incomparable problem: that is, how to process temporary datasets in a code that requires a long time or frequently called code? Table variables and temporary tables are two options. I remember seeing a large number of temporary data set processing requirements when I evaluated and optimized SQL Server application performance for a leading Chinese shipping company, their developers cannot determine when to use temporary tables and when to use table variables, so they simply use temporary tables. In fact, both temporary tables and table variables have specific application environments. First, let's get some basic knowledge: Table Variables All variables are prefixed with "@" or "@". Table variables are one type of variables. Another variable is called a scalar (which can be understood as a standard variable, which is a variable of the standard data type, for example, integer Int or datetime ). The table variables with the @ prefix are local, so they can only be accessed in the current user session. The table variables with the @ prefix are global and usually system variables, for example, @ error indicates the error number of a recent T-SQL statement. Of course, because a table variable is a variable first, it can only survive in one batch, that is, the boundary we call. If the boundary is exceeded, the table variable will die. Table variables are stored in the memory, because SQL server does not need to generate logs when all users access table variables. At the same time, variables do not need to consider other session access issues, so they do not need the lock mechanism. For very busy systems, avoiding the use of the lock can reduce part of the system load. Another restriction on table variables is that indexes cannot be created. Of course, there is no statistical data problem, therefore, when users access table variables, there is no Execution Plan Selection Problem (that is, they think there is no optimization stage after the compilation stage). This feature is a good thing sometimes, in some cases, it may cause some trouble. Temporary table Temporary objects are prefixed with # Or #. Temporary tables are temporary objects and temporary objects such as temporary stored procedures and temporary functions, temporary objects are stored in tempdb. Temporary tables with a prefix of # are local, so they can only be accessed in the current user session, while temporary tables with a prefix of # are global, so all user sessions can be accessed. A temporary table uses sessions as the boundary. As long as the session for creating a temporary table is not completed, the temporary table will continue to exist. Of course, you can use the drop table command to destroy the temporary table in the session. As we have mentioned before, temporary tables are stored in tempdb, so access to temporary tables may cause physical Io. Of course, logs need to be generated during modification to ensure consistency, the lock mechanism is also indispensable. Another notable difference with table variables is that temporary tables can create indexes or define statistical data. Therefore, SQL Server needs to consider execution plan optimization when processing statements that access temporary tables. Table variable vs. Temporary table   Conclusion In summary, we will find that there are many differences in the underlying processing mechanism between temporary tables and table variables. To sum up, we recommend that you use table variables for small temporary computation datasets. If the dataset is large, if it is used for temporary computing in the code, and such temporary use is always a simple full dataset scan without any optimization, for example, table variables can be used if no group or few groups are aggregated (such as Count, sum, average, and Max. Another consideration for using Table variables is the memory pressure in the application environment. If there are many running instances of code, pay special attention to the memory consumption caused by memory variables. We recommend that you use temporary tables for large datasets, create indexes at the same time, or use the statisitcs automatic creation and maintenance function to optimize access to SQL statements. If you need to exchange data between multiple user sessions, the temporary table is the only choice. It should be mentioned that the temporary tables are stored in tempdb, so you should pay attention to the optimization of tempdb. Temporary tables and table variables in SQL We often use temporary tables and table variables. Now we will discuss temporary tables and table variables. Temporary table Local temporary table Global temporary table Table Variables 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 Programmers think that temporary tables are very dangerous because they 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. ---------------------------------------- In addition, when I helped my colleague tuning SQL script, I found that the Big Data Table query (10 W-100 W ), the execution time of variables is 100 times less than that of select statements !! I have never thought of such a big difference. I was surprised to hear that ing recorded a record and studied it. M1: Declare @ tempid int Set @ tempid = (select lots_id from qs_notes where id = 'cvt20080321 ') Select * From ls_qs_notes where id = @ tempid --- Return record 998, row execution time 6589 M2: Select * From ls_qs_notes where id = (select lots_id from qs_notes where id = 'cvt20080321 ') --- 998 of the returned record, and 60 of the row execution time

 

 

/*************************************** **************************************** *******************************

During development, table variables and local temporary tables are often used. The following is an introduction to the two:

1. Why use table variables?

Table variables are introduced from 2000. Microsoft believes that compared with local temporary tables, table variables have the following advantages:
A. Like other variables, table variables have a well-defined range and will be automatically cleared;
B. Using table variables in a stored procedure will reduce the re-Compilation of the stored procedure;
C. Table variables require fewer lock requests and log resources;
D. You can use UDF, uddt, and XML on table variables.

2. Table variable restrictions

Compared with temporary tables, table variables have the following Disadvantages:
A. There is no statistical information on the table variables. The query optimizer selects an execution plan based on a fixed estimated value. In the case of a large amount of data, the query optimizer selects a poor execution plan;
B. You cannot create indexes directly on table variables, but you can create indexes by creating constraints (primary key and unique;
C. After declare, you cannot change the table variables;
D. The insert exec and select into statements cannot be executed on the table variables;
E. You cannot use exec or sp_executesql to execute dynamic SQL statements that involve table variables. However, if the table variables are defined in dynamic SQL statements, you can.

3. When can I use table variables?

Table variables should be determined based on the following rules:
A. number of rows in the table;
B. Use table variables to reduce the number of recompilation times;
C. The type of the query and the degree of dependency on the index or statistical information;
D. When UDF, uddt, and XML need to be generated.
In fact, you have to make a specific choice based on the actual query. However, the key point is that if the number of rows in a table is large, the use of table variables is actually more resource-consuming. Someone suggested that you use table variables when the number of rows is small (less than 1000 rows). If the number of rows is large (tens of thousands of rows), use a temporary table.

Therefore, in actual development, we should use temporary tables or table variables for comparison before making a decision.

The following is an example. There are more than 0.2 million rows of data inserted into the temporary table and table variables. We can see that the time used for temporary tables is 1/5 of the time spent on table variables.

Table 'salesorderheader '. Scan count 3, logical read 130
Physical reads 9 times, pre-reads 43 times, and LOB logic reads 0
Times, lob physical reads 0 times, and LOB pre-reads 0 times.
Table '# salesorderdetail ___________________________________________________________________________________________________________ 00000000001f '. Scan count 3, logical read 12331
Times, physical reads 0 times, pre-reads 0 times, and LOB logic reads 0
Times, lob physical reads 0 times, and LOB pre-reads 0 times.
Table 'worktable '. Scan count 0, logical read 0
Times, physical reads 0 times, pre-reads 0 times, and LOB logic reads 0
Times, lob physical reads 0 times, and LOB pre-reads 0 times.

SQL Server execution time:
CPU time = 2281 milliseconds, occupied time =
19726 Ms.
Select
With temporarytable:
20140 MS

**************************************** ****************************************

Table 'salesorderheader '. Scan count 0, logical read 764850
Times, physical reads 17 times, pre-reads 0 times, and LOB logic reads 0
Times, lob physical reads 0 times, and LOB pre-reads 0 times.
Table '#4e88abd4 '. Scan count 1, logical read 12331
Times, physical reads 0 times, pre-reads 0 times, and LOB logic reads 0
Times, lob physical reads 0 times, and LOB pre-reads 0 times.

SQL Server execution time:
CPU time = 4375 milliseconds, occupied time =
107160 Ms.
Select
With table variable: 107160 MS

4. misunderstandings about using Table Variables

Many people think that, like other variables, table variables only exist in memory. In fact, this is incorrect, and table variables also exist in tempdb. You can use the following example for comparison.

Create
Table # temptable (tt_col1int)

Declare
@ Tablevariable
Table (TV _col1
INT)

Select
Top 2 *

From tempdb. SYS. Objects

Order
By create_date
Desc

Name
Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# 03317e3d
# Temptable ______________________________________________________________________________________________________________ 000000000003

# 03317e3d is the created table variable;

5. Others

Table variables are not affected by rollback, which may damage data integrity in some cases.

Create
Table # temptable (tt_col1int)
Declare
@ Tablevariable
Table (TV _col1
INT)
Insert # temptablevalues (1)
Insert
@ Tablevariable
Values (1)
Begin
Transaction
Insert # temptablevalues (2)
Insert @ tablevariablevalues (2)
Rollback
Select
* From # temptable

/*
Tt_col1
-------
1
*/

Select
* From @ tablevariable
-- Two records are returned.
/*
TV _col1
-------
1
2
*/

Http://www.cnblogs.com/jokey/archive/2010/06/19/1760994.html