TSQL笔记7：临时表和表变量

T-SQL笔记7：临时表和表变量

本章摘要：

1：临时表

2：表变量

3：两者的取舍

    Temporary tables are defined just like regular tables, only they are automatically stored in the tempdb database (no matter which database context you create them in).

    There are two different temporary table types: globaland local. Local temporary tables are prefixed with a single # sign, and global temporary tables with a double ## sign.

    Local temporary tables are dropped by using the DROP statement or are automatically removed from memory when the user connection is closed.

    Global temporary tables are removed fromSQL Server if explicitly dropped by DROP TABLE. They are also automatically removed after the connection that created it exits and the global temporary table is no longer referenced by other connections. As an a side,  I rarely see global temporary tables used in the field.

1.1:Using aTemporary Table for Multiple Lookups Within aBatch

    In this example, I’ll demonstrate creating a local temporary table that is then referenced multiple times in a batch of queries. This technique can be helpful if the query used to generate the lookup values takes several seconds to execute. Rather then execute the SELECT query multiple times, we can query the pre-aggregated temp table instead:

CREATE TABLE #ProductCostStatistics 
( ProductID int NOT NULL PRIMARY KEY,
AvgStandardCost money NOT NULL,
ProductCount int NOT NULL)
INSERT #ProductCostStatistics 
(ProductID, AvgStandardCost, ProductCount)
SELECT ProductID,
AVG(StandardCost) AvgStandardCost,
COUNT(ProductID) Rowcnt
FROM Production.ProductCostHistory
GROUP BY ProductID
GO
SELECT TOP 3 *
FROM #ProductCostStatistics 
ORDER BY AvgStandardCost ASC
SELECT TOP 3 *
FROM #ProductCostStatistics 
ORDER BY AvgStandardCost DESC
SELECT AVG(AvgStandardCost) Average_of_AvgStandardCost
FROM #ProductCostStatistics 
DROP TABLE #ProductCostStatistics 

    How It Works
    In this recipe, a temporary table called #ProductCostStatisticswas created. The table had rows inserted into it like a regular table, and then the temporary table was queried three times (again, just like aregular table), and then dropped. The table was created and queried with the same syntax as aregular table, only the temporary table name was prefixed with a # sign. In situations where the initial population query execution time takes too long to execute, this is one technique to consider.

    Microsoft recommends table variables as a replacement of temporary tables when the data set is not very large (which is avague instruction—in the end it is up to you to test which table types work best in your environment). A table variable is a data type that can be used within a Transact-SQL batch, stored procedure, or function—and is created and defined similarly to a table, only with a strictly defined lifetime scope.
    Unlike regular tables or temporary tables, table variables can’t have indexes or FOREIGN KEY constraints added to them. Table variables do allow some constraints to be used in the table definition (PRIMARY KEY, UNIQUE, CHECK).

2.1:Creating aTable Variable to Hold aTemporary Result Set

    The syntax to creating a table variable is similar to creating atable, only the DECLARE keyword is used and the table name is prefixed with an @ symbol:

DECLARE @TableName TABLE
(column_name <data_type> [ NULL | NOT NULL ] [ ,...n ]  )

    In this example, a table variable is used in a similar fashion to the temporary table of the previous recipe. This example demonstrates how the implementation differs (including how you don’t explicitly DROPthe table):

DECLARE @ProductCostStatistics TABLE
( ProductID int NOT NULL PRIMARY KEY,
AvgStandardCost money NOT NULL,
ProductCount int NOT NULL)
INSERT @ProductCostStatistics 
(ProductID, AvgStandardCost, ProductCount)
SELECT ProductID,
AVG(StandardCost) AvgStandardCost,
COUNT(ProductID) Rowcnt
FROM Production.ProductCostHistory
GROUP BY ProductID
SELECT TOP 3 *
FROM @ProductCostStatistics 
ORDER BY ProductCount

    How It Works
    This recipe used a table variable in much the same way as the previous recipe did with temporary tables. There are important distinctions between the two recipes however.

     First, this time a table variable was defined using DECLARE @Tablename TABLE instead of CREATE TABLE. Secondly, unlike the temporary table recipe, there isn’t a GO after each statement, as temporary tables can only be scoped within the batch, procedure, or function.  In the next part of the recipe, you’ll use inserts and selects from the table variable as you would a regular table, only this time using the @tablenameformat:

INSERT @ProductCostStatistics 
...
SELECT TOP 3 *
FROM @ProductCostStatistics 
...

     No DROP TABLE was necessary at the end of the example, as the table variable is eliminated from memory after the end of the batch/procedure/function execution.

    Reasons to use table variables include:
    * Well scoped. The lifetime of the table variable only lasts for the duration of the batch, function, or stored procedure.
    * Shorter locking periods (because of the tighter scope).
    * Less recompilation when used in stored procedures.
    There are drawbacks to using table variables though. Table variable performance suffers when the result set becomes too large (defined by your hardware, database design, and query). When encountering performance issues, be sure to test all alternative solutions and don’t necessarily assume that one option (temporary tables) is less desirable than others (table variables).