Temporary tables and table variables in SQL Server Declare @Tablename table

In SQL Server performance tuning, there is an aspect problem: How do you work with temporary datasets in code that takes a long time or is frequently called? Table variables and temporary tables are two choices. Remember that a large number of temporary data set processing requirements have been seen in SQL Server application performance evaluation and tuning for a leading shipping company in the country, and their developers are not sure 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 a specific applicable environment.

Show off some basic knowledge First:

Table Variables
variables are prefixed with @ or @@ 为 The table variable is one of the variables, and the other variable is called a scalar (which can be understood as a standard variable, that is, a variable of a standard data type, such as Integer int or date datetime). A table variable at the @ prefix is local, so it is accessible only in the current user session, and the table variable of the @@ 前缀 is global, usually system variables, such as @ @error represents the error number of the most recent T-SQL statement. Of course, because a table variable is a variable first, it can only survive in one batch, which is what we call the boundary, beyond which the table variable dies.

table variables are stored in memory, and it is precisely because of this that SQL Server does not need to generate logs when all users access table variables. At the same time variables are not required to consider other session access problems, so there is no lock mechanism, for very busy systems, to avoid the use of locks can reduce a part of the system load.

Table Variables There is another limitation is not to create an index, of course, there is no statistical problem, so when the user accesses the table variable there is no execution plan selection problem (that is, for the compilation phase there is no optimization stage), this feature is sometimes a good thing, And sometimes it can cause some trouble.

Temp Table
temporary objects are #为前缀 in # or #, temporary tables are one of the temporary objects, and temporary objects such as temporary stored procedures, temporary functions, and temporary objects are stored in tempdb. The temporary table with the # prefix is local, so it is accessible only in the current user session, and # #前缀的临时表是全局的, so all user sessions can be accessed. Temporary tables are session-bound, and the temporary table persists as long as the session that created the temporary table does not end, and of course the user can destroy the temporary table prematurely through the drop TABLE command in the session.

As we said earlier, temporary tables are stored in tempdb, so access to temporary tables is likely to result in physical IO, but it is also necessary to generate logs to ensure consistency when modifying, while locking mechanisms are also indispensable.

Another notable addition to the table variable is that temporary tables can create indexes or define statistics, so SQL Server needs to consider the issue of execution plan optimization when it processes statements that access temporary tables.

table variables vs. temporary tables



Conclusion
in summary, you will find that temporary tables and table variables in the underlying processing mechanism is a lot of differences.

briefly, we recommend using a table variable for a smaller, temporary calculation with a dataset. If the dataset is large, and if it is used in the code for ad hoc calculations, this temporary use is always a simple full-data-set scan with no optimizations to consider, such as aggregations that are not grouped or grouped (for example, Count, SUM, AVERAGE, Max, and so on), or you can consider using table variables. Using table variables Another consideration is the memory pressure of the application environment, and if the code is running a lot of instances, pay special attention to memory variables for memory consumption.

generally for large datasets we recommend using temporal tables, creating indexes at the same time, or automating the creation and maintenance of SQL Server statistics (STATISITCS) to provide optimizations for accessing SQL statements. If you need to exchange data between multiple user sessions, the temporary table is the only option. It is important to note that because temporary tables are stored in tempdb, be aware of the tuning of tempdb.



temporary tables and table variables in SQL

We often use temporal tables and table variables, so let's talk about temporal tables and table variables.

Temp table

Local temporary table

Global temp Table

Table variables



Temp Table

Temporary tables are stored in the tempdb database, and all users who use this instance of SQL Server share this tempdb because we should ensure that the hard disk used to store the tempdb database has enough space So that it can grow on its own. It is best to be stored on a single hard disk controller. Because this does not exist and other hard disk I/O is used for contention.



Many of our programmers think of temporary tables as dangerous because temporary tables can be shared by multiple connections. In fact, there are two kinds of temporary tables in SQL Server: Local temporary table and global temporary table, local temp table is identified by # prefix, And can only be used by the connection that created it. The Global temp table is #前缀来进行标识 with # and can be shared with other connections.



Local temporary table

The reason that a local temporary table cannot be shared by other connections is in fact that a unique character is appended to the table name of the local temporary table in SQL Server 2000. 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 and we'll find that our newly created temporary table #dimcustomer_test has been added with the suffix:



Use TempDB

GO

SELECT name from sysobjects WHERE name like '%dimcustomer% '



The Result is:

Name

#DimCustomer_test_____________________________________________________________________________________________ ______000000000005

Global Temp Table

Let's take a look at the global temp 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 and we'll find our newly created temporary table # #DimCustomer_test没有被加上了后缀:



Use TempDB

GO

SELECT name from sysobjects WHERE name like '%dimcustomer% '



The Result is:

#DimCustomer_test_____________________________________________________________________________________________ ______000000000005

# #DimCustomer_test



--drop Test Temp Tables

DROP TABLE [# #DimCustomer_test]

DROP TABLE [#DimCustomer_test]



You can see that the global temporary table name we just created is not labeled.



Table Variables

Table variables and temporary tables are not different for us, but in terms of storage they are different and table variables are stored in memory. So it would be better to compare the performance to the temp table!



Another difference is when you use table variables in a table connection, you specify an alias for this table variable. 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 2, 32

Must declare the variable ' @DimCustomer_test '.





If we make changes to the above query, use an alias (and open IO) for the query:

-----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 the batch process, so you do not have to delete them as they appear with temporary table tables.




----------------------------------------

In addition to help colleagues tuning SQL foot local time, found that the big Data scale of the query (10w-100w), with a variable than the way you use Select to actually execute time reduced by 100 times times!! indefinitely, but never thought the difference was so great, surprised ing, to record a sum, to study

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, line execution time 6589


M2:

SELECT * from ls_qs_notes WHERE id = (select lots_id from qs_notes where id= ' CVT20080321 ')

---return record 998, line execution time 60