临时表

在开发过程中，经常会遇到使用表变量和本地临时表的情况。下面是对二者的一个介绍：1. 为什么要使用表变量表变量是从2000开始引入的，微软认为与本地临时表相比，表变量具有如下优点：  a.与其他变量的定义一样，表变量具有良好的定义范围，并会被自动清除；  b.在存储过程中使用表变量会减少存储过程重新编译的发生；  c.表变量需要更少的锁请求和日志资源；  d.可以在表变量上使用UDF,UDDT,XML。2.表变量的限制与临时表相比，表变量存在着如下缺点：  a.在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据很多的情况下，会导致查询优化器选择很差的执行计划；  b.不能直接在表变量上创建索引，但可以通过创建约束（主键、唯一）来建立索引；  c.在DECLARE后，不能再对表变量进行更改；  d.不能对表变量执行INSERTEXEC,SELECT INTO语句；  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 毫秒。selectwithtemporarytable:20140 ms******************************************************************************** 表'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 毫秒。selectwithtable variable:107160 ms4.使用表变量的误区对于表变量，很多人认为，表变量和其他变量一样，只存在内存中，其实这是不正确的，表变量也存在tempdb中。可以通过下面例子进行对比。CREATETABLE #TempTable (TT_Col1INT)DECLARE@TableVariableTABLE (TV_Col1INT)SELECTTOP2*FROM tempdb.sys.objectsORDERBY create_dateDESCname------------------------------------------------------------------------------------------------------------------------------------------------------------------------#03317E3D#TempTable__________________________________________________________________________________________________________000000000003#03317E3D就是刚创建的表变量；5.其他  表变量不受rollback影响，某些情况下会破坏数据的完整性。CREATETABLE #TempTable (TT_Col1INT)DECLARE@TableVariableTABLE (TV_Col1INT)INSERT #TempTable VALUES (1)INSERT@TableVariableVALUES (1)BEGINTRANSACTION    INSERT #TempTableVALUES (2)     INSERT@TableVariableVALUES (2)ROLLBACKSELECT*FROM #TempTable/*TT_Col1-------1*/SELECT*FROM@TableVariable--返回了两条记录/*TV_Col1-------12*/6.参考http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2kwww.sqlservercentral.com/articles/63472/databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.htmlwww.sqlservercentral.com/articles/Temporary+Tables/66720/

原文链接：http://topic.csdn.net/u/20100610/22/fc438413-8472-40fb-b301-39ae0b8c36c4.html?seed=570979482&r=66142866#r_66142866