表变量

4.6.2　创建表变量来保存临时结果集在2.4节中就介绍过表变量。我们学了如何使用表变量来保存OUTPUT命令的结果。注解 SQL Server 2008引入了表值参数和用户定义类型，可以用它来在模块之间传递临时结果集。第11章会讲到这些主题。创建表变量的语法和创建表的相似，不同的是使用DECLARE关键字，并且表名有@符号前缀：DECLARE @TableName TABLE (column_name [ NULL | NOT NULL ] [ ,...n ] ) 在这个示例中，使用表变量的方式和前面技巧中使用临时表的方式相似。这个示例演示实现的不同（包括不显式地DROP表的方法）：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 这个查询返回：ProductID AvgStandardCost ProductCount 710 3.3963 1 709 3.3963 1 731 352.1394 1 解析这个技巧使用表变量的方式和前面技巧使用临时表的方式基本一样。不过，两个技巧还是有重要区别的。首先，这次的表变量使用DECLARFE @ Tablename TABLE而不是CREATE TABLE来定义。其次，和临时表技巧不同，该技巧在每个语句后面没有GO，因为临时表只能在批处理、存储过程或函数中存在。在技巧的后面一部分中，像使用普通表一样从表变量插入和查询，不同的是这次使用@tablename格式：INSERT @ProductCostStatistics ... SELECT TOP 3 * FROM @ProductCostStatistics ... 在示例的最后并不需要使用DROP TABLE，因为在批处理/存储过程/函数执行后表变量就从内存中消失了。

[SQL]表变量和临时表

1. 为什么要使用表变量

表变量是从2000开始引入的，微软认为与本地临时表相比，表变量具有如下优点：

  a.与其他变量的定义一样，表变量具有良好的定义范围，并会被自动清除；
  b.在存储过程中使用表变量会减少存储过程重新编译的发生；
  c.表变量需要更少的锁请求和日志资源；
  d.可以在表变量上使用UDF,UDDT,XML。

2 .表变量的限制

与临时表相比，表变量存在着如下缺点：
  a.在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据很多的情况下，会导致查询优化器选择很差的执行计划；
  b.不能直接在表变量上创建索引，但可以通过创建约束（主键、唯一）来建立索引；
  c.在DECLARE后，不能再对表变量进行更改；
  d.不能对表变量执行INSERT EXEC, SELECT INTO语句（只针对05前的版本）；
  e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句，但如果表变量是在动态SQL语句内定义的，则可以。

3 .那什么时候可以使用表变量

要使用表变量应该根据如下规则来判断：
  a.表的行数；
  b.使用表变量能够减少的重新编译次数；
  c.查询的类型和对索引或者统计信息的依赖程度；
  d.需要生用UDF,UDDT,XML的时候。
其实也就说，得从实际出发，根据具体的查询，作出具体的选择。但是，其中很关键的一点，如果表的行数非常多，使用表变量其实是更费资源的。有人提出了这样的建议：对于行数较少的情况下(小于1000行）可以使用表变量；如果行数很多（有几万行），则使用临时表。

因此，在实际的开发中，应通过分别使用临时表或表变量进行对比后，才作出决定。


下面是一个例子，插入临时表和表变量的数据有20多万行，可以看到，使用临时表的时间是使用表变量所花时间的1 / 5 。

表 ' SalesOrderHeader ' 。扫描计数 3，逻辑读取 130次，物理读取 9次，预读 43次，lob 逻辑读取 0次，lob 物理读取 0次，lob 预读 0次。
表 ' #SalesOrderDetail___________________________________________________________________________________________________00000000001F' 。扫描计数 3，逻辑读取 12331次，物理读取 0次，预读 0次，lob 逻辑读取 0次，lob 物理读取 0次，lob 预读 0次。
表 ' Worktable ' 。扫描计数 0，逻辑读取 0次，物理读取 0次，预读 0次，lob 逻辑读取 0次，lob 物理读取 0次，lob 预读 0次。

SQL Server 执行时间:
   CPU 时间 = 2281 毫秒，占用时间 =19726 毫秒。
select with temporary table : 20140ms

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

表 ' SalesOrderHeader ' 。扫描计数 0，逻辑读取 764850次，物理读取 17次，预读 0次，lob 逻辑读取 0次，lob 物理读取 0次，lob 预读 0次。
表 ' #4E88ABD4 ' 。扫描计数 1，逻辑读取 12331次，物理读取 0次，预读 0次，lob 逻辑读取 0次，lob 物理读取 0次，lob 预读 0次。

SQL Server 执行时间:
   CPU 时间 = 4375 毫秒，占用时间 =107160 毫秒。
select with table variable: 107160ms

4 .使用表变量的误区

对于表变量，很多人认为，表变量和其他变量一样，只存在内存中，其实这是不正确的，表变量也存在tempdb中。可以通过下面例子进行对比。

CREATE TABLE #TempTable (TT_Col1 INT )

DECLARE @TableVariable TABLE (TV_Col1 INT )

SELECT TOP 2*

FROM tempdb.sys.objects

ORDER BY create_date DESC


name
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
#03317E3D
#TempTable__________________________________________________________________________________________________________000000000003


#03317E3D就是刚创建的表变量；

5 .其他

  表变量不受rollback影响，某些情况下会破坏数据的完整性。

CREATE TABLE #TempTable (TT_Col1 INT )
DECLARE @TableVariable TABLE (TV_Col1 INT )
INSERT #TempTable VALUES ( 1 )
INSERT @TableVariable VALUES ( 1 )
BEGIN TRANSACTION
     INSERT #TempTable VALUES ( 2 )
      INSERT @TableVariable VALUES ( 2 )
ROLLBACK
SELECT * FROM #TempTable

/*
TT_Col1
-------
1
*/

SELECT * FROM @TableVariable
-- 返回了两条记录
/*
TV_Col1
-------
1
2
*/

 

本地临时表 
以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表。
全局临时表 
以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表即被除去。

 

 

http://blog.chinaunix.net/uid-20551228-id-2826603.html 全局临时表的使用