表变量和临时表总结 

  1 在开发过程中，经常会遇到使用表变量和本地临时表的情况。下面是对二者的一个介绍：
  2 
  3 1. 为什么要使用表变量
  4 
  5 表变量是从2000开始引入的，微软认为与本地临时表相比，表变量具有如下优点：
  6   a.与其他变量的定义一样，表变量具有良好的定义范围，并会被自动清除；
  7   b.在存储过程中使用表变量会减少存储过程重新编译的发生；
  8   c.表变量需要更少的锁请求和日志资源；
  9   d.可以在表变量上使用UDF,UDDT,XML。
 10 
 11 2.表变量的限制
 12 
 13 与临时表相比，表变量存在着如下缺点：
 14   a.在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据很多的情况下，会导致查询优化器选择很差的执行计划；
 15   b.不能直接在表变量上创建索引，但可以通过创建约束（主键、唯一）来建立索引；
 16   c.在DECLARE后，不能再对表变量进行更改；
 17   d.不能对表变量执行INSERT EXEC,SELECT INTO语句；
 18   e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句，但如果表变量是在动态SQL语句内定义的，则可以。
 19 
 20 3.那什么时候可以使用表变量
 21 
 22 要使用表变量应该根据如下规则来判断：
 23   a.表的行数；
 24   b.使用表变量能够减少的重新编译次数；
 25   c.查询的类型和对索引或者统计信息的依赖程度；
 26   d.需要生用UDF,UDDT,XML的时候。
 27 其实也就说，得从实际出发，根据具体的查询，作出具体的选择。但是，其中很关键的一点，如果表的行数非常多，使用表变量其实是更费资源的。有人提出了这样的建议：对于行数较少的情况下(小于1000行）可以使用表变量；如果行数很多（有几万行），则使用临时表。
 28 
 29 因此，在实际的开发中，应通过分别使用临时表或表变量进行对比后，才作出决定。
 30 
 31 
 32 下面是一个例子，插入临时表和表变量的数据有20多万行，可以看到，使用临时表的时间是使用表变量所花时间的1/5。
 33 
 34 表 'SalesOrderHeader'。扫描计数 3，逻辑读取 130 次，物理读取 9 次，预读 43 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 35 表 '#SalesOrderDetail___________________________________________________________________________________________________00000000001F'。扫描计数 3，逻辑读取 12331 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 36 表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 37 
 38  SQL Server 执行时间:
 39    CPU 时间 = 2281 毫秒，占用时间 = 19726 毫秒。
 40 select with temporary table: 20140 ms
 41  
 42 ********************************************************************************
 43  
 44 表 'SalesOrderHeader'。扫描计数 0，逻辑读取 764850 次，物理读取 17 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 45 表 '#4E88ABD4'。扫描计数 1，逻辑读取 12331 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 46 
 47  SQL Server 执行时间:
 48    CPU 时间 = 4375 毫秒，占用时间 = 107160 毫秒。
 49 select with table variable: 107160 ms
 50 
 51 4.使用表变量的误区
 52 
 53 对于表变量，很多人认为，表变量和其他变量一样，只存在内存中，其实这是不正确的，表变量也存在tempdb中。可以通过下面例子进行对比。
 54 
 55 CREATE TABLE #TempTable (TT_Col1 INT)
 56 
 57 DECLARE @TableVariable TABLE (TV_Col1 INT)
 58 
 59 SELECT TOP 2 *
 60 
 61 FROM tempdb.sys.objects
 62 
 63 ORDER BY create_date DESC
 64 
 65 
 66 name
 67 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 68 #03317E3D
 69 #TempTable__________________________________________________________________________________________________________000000000003
 70 
 71 
 72 #03317E3D就是刚创建的表变量；
 73 
 74 5.其他
 75 
 76   表变量不受rollback影响，某些情况下会破坏数据的完整性。
 77 
 78 CREATE TABLE #TempTable (TT_Col1 INT)
 79 DECLARE @TableVariable TABLE (TV_Col1 INT)
 80 INSERT #TempTable VALUES (1)
 81 INSERT @TableVariable VALUES (1)
 82 BEGIN TRANSACTION
 83      INSERT #TempTable VALUES (2)
 84       INSERT @TableVariable VALUES (2)
 85 ROLLBACK
 86 SELECT * FROM #TempTable
 87 
 88 /*
 89 TT_Col1
 90 -------
 91 1
 92 */
 93 
 94 SELECT * FROM @TableVariable
 95 --返回了两条记录
 96 /*
 97 TV_Col1
 98 -------
 99 1
100 2
101 */
102 
103 
104 
105 6.参考
106 
107 http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
108 www.sqlservercentral.com/articles/63472/databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
109 www.sqlservercentral.com/articles/Temporary+Tables/66720/

 

posted @ 2015-04-29 10:20  tiger5  阅读(157)  评论(0)    收藏  举报