临时表和表变量，居然能够影响到sql2005的查询计划
[问题点数：50分]
临时表和表变量，居然能够影响到sql2005的查询计划
[问题点数：50分]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
相关帖子推荐：
2011年12月 总版技术专家分月排行榜第三2011年9月 总版技术专家分月排行榜第三
2011年6月 MS-SQL Server大版内专家分月排行榜第一
2011年12月 总版技术专家分月排行榜第三2011年9月 总版技术专家分月排行榜第三
2011年6月 MS-SQL Server大版内专家分月排行榜第一
2010年7月 荣获微软MVP称号
2008年11月 MS-SQL Server大版内专家分月排行榜第一2008年10月 MS-SQL Server大版内专家分月排行榜第一
2008年 总版技术专家分年内排行榜第二
2010年 总版技术专家分年内排行榜第四2009年 总版技术专家分年内排行榜第五2007年 总版技术专家分年内排行榜第六
本帖子已过去太久远了，不再提供回复功能。您所在的位置： &
SQL Server中的临时表和表变量
SQL Server中的临时表和表变量
DrillChina
在SQLServer的性能调优中，有一个不可比拟的问题：那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。
临时表和变量表的基础知识：
变量都以@或@@为前缀，表变量是变量的一种，另外一种变量被称为标量(可以理解为标准变量，就是标准数据类型的变量，例如整型int或者日期型DateTime)。以@前缀的表变量是本地的，因此只有在当前用户会话中才可以访问，而@@前缀的表变量是全局的，通常都是系统变量，比如说@@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量，因此它只能在一个Batch中生存，也就是我们所说的边界，超出了这个边界，表变量也就消亡了。
存放在内存中，正是因为这一点所有用户访问表变量的时候SQLServer是不需要生成日志。同时变量是不需要考虑其他会话访问的问题，因此也不需要锁机制，对于非常繁忙的系统来说，避免锁的使用可以减少一部分系统负载。
另外还有一个限制就是不能创建索引，当然也不存在统计数据的问题，因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了)，这一特性有的时候是件好事，而有些时候却会造成一些麻烦。
临时对象都以#或##为前缀，临时表是临时对象的一种，还有例如临时存储过程、临时函数之类的临时对象，临时对象都存储在tempdb中。以#前缀的临时表为本地的，因此只有在当前用户会话中才可以访问，而##前缀的临时表是全局的，因此所有用户会话都可以访问。临时表以会话为边界，只要创建临时表的会话没有结束，临时表就会持续存在，当然用户在会话中可以通过DROPTABLE命令提前销毁临时表。
我们前面说过临时表存储在tempdb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的。
跟表变量另外一个显著去别就是临时表可以创建索引，也可以定义统计数据，因此SQLServer在处理访问临时表的语句时需要考虑执行计划优化的问题。
表变量vs. 临时表
临时表 数据集的存储位置 内存(不考虑被换到页面文件这种情况) 磁盘(不考虑访问后被缓存到内存中) 是否需要日志 否是是否可以创建索引 否 是 是否可以使用统计数据 否 是 是否可以在多会话中访问 否 是 是否需要锁机制 否 是
综上所述，大家会发现临时表和表变量在底层处理机制上是有很多差别的。
简单地总结，我们对于较小的临时计算用数据集推荐使用表变量。如果数据集比较大，如果在代码中用于临时计算，同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化，比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等)，也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。
一般对于大的数据集我们推荐使用临时表，同时创建索引，或者通过SQLServer的统计数据(Statisitcs)自动创建和维护功能来提供访问SQL语句的优化。如果需要在多个用户会话间交换数据，当然临时表就是唯一的选择了。需要提及的是，由于临时表存放在tempdb中，因此要注意tempdb的调优。
从上文可以总结出，并不是临时表和变量表哪种更好，其实临时表和变量表是两种不同的表，二者都有各自的适合环境，所以在使用只要是适合的就是好用的，所以大家在选用时，要综合考虑使用环境。
【编辑推荐】
【责任编辑： TEL：（010）】
关于&&&&的更多文章
不管你有没有准备好，新版SQL Server来了！代号为Denali的下一个
数据库产品
数据库综合
数据库新闻
维基百科将切换到另外一款开源数据库MariaDB
暮春三月，京师草长，杂花生树，群莺乱飞。ChinaHadoo
MySQL 5.6.10已经发布，Oracle将其称之为MySQL 5.6正
51CTO将为您整理数据清洗方面的技术文章和相关资料，
本书使用通俗易懂的语言，通过大量的实例，从实际应用的角度出发，全面系统地介绍了网络服务操作系统平台、电子邮件系统、Web站
51CTO旗下网站9535人阅读
问题 1：为什么在已经有了临时表的情况下还要引入表变量？解答 1：与临时表相比，表变量具有下列优点：
如 SQL Server 联机丛书&表&(Table) 一文中所述，表变量（如局部变量）具有明确定义的范围，在该范围结束时会自动清除这些表变量。
与临时表相比，表变量导致存储过程的重新编译更少。
涉及表变量的事务仅维持表变量上更新的持续时间。因此，使用表变量时，需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分，所以事务回滚并不影响它们。
问题 2：如果说使用表变量比使用临时表导致存储过程的重新编译更少，这意味着什么？解答 2：下面的文章讨论了重新编译存储过程的一些原因：
(/kb/243586/) 存储过程重新编译的疑难解答
&由于某些临时表操作引起的重新编译&一节还列出了为避免一些问题（例如使用临时表导致重新编译）而需要满足的一些要求。这些限制不适用于表变量。表变量完全独立于创建这些表变量的批，因此，当执行 CREATE 或 ALTER 语句时，不会发生&重新解析&，而在使用临时表时可能会发生&重新解析&。临时表需要此&重新解析&，以便从嵌套存储过程引用该表。表变量完全避免了此问题，因此存储过程可以使用已编译的计划，从而节省了处理存储过程的资源。问题 3：表变量有哪些缺陷？解答 3：与临时表相比，它存在下列缺陷：
在表变量上不能创建非聚集索引（为 PRIMARY 或 UNIQUE 约束创建的系统索引除外）。与具有非聚集索引的临时表相比，这可能会影响查询性能。
表变量不像临时表那样可以维护统计信息。在表变量上，不能通过自动创建或使用 CREATE STATISTICS 语句来创建统计信息。因此，在大表上进行复杂查询时，缺少统计信息可能会妨碍优化器确定查询的最佳计划，从而影响该查询的性能。
在初始 DECLARE 语句后不能更改表定义。
表变量不能在 INSERT EXEC 或 SELECT INTO 语句中使用。
表类型声明中的检查约束、默认值以及计算所得的列不能调用用户定义的函数。
如果表变量是在 EXEC 语句或 sp_executesql 存储过程外创建的，则不能使用 EXEC 语句或 sp_executesql 存储过程来运行引用该表变量的动态 SQL Server 查询。由于表变量只能在它们的本地作用域中引用，因此 EXEC 语句和 sp_executesql 存储过程将在表变量的作用域之外。但是，您可以在 EXEC 语句或 sp_executesql 存储过程内创建表变量并执行所有处理，因为这样表变量本地作用域将位于 EXEC 语句或 sp_executesql 存储过程中。
问题 4：与临时表或永久表相比，表变量的仅存在于内存中的结构保证了更好的性能，是否因为它们是在驻留在物理磁盘上的数据库中维护的？解答 4：表变量不是仅存在于内存中的结构。由于表变量可能保留的数据较多，内存中容纳不下，因此它必须在磁盘上有一个位置来存储数据。与临时表类似，表变量是在 tempdb 数据库中创建的。如果有足够的内存，则表变量和临时表都在内存（数据缓存）中创建和处理。问题 5：必须使用表变量来代替临时表吗？解答 5：答案取决于以下三个因素：
插入到表中的行数。
从中保存查询的重新编译的次数。
查询类型及其对性能的指数和统计信息的依赖性。
在某些情况下，可将一个具有临时表的存储过程拆分为多个较小的存储过程，以便在较小的单元上进行重新编译。 通常情况下，应尽量使用表变量，除非数据量非常大并且需要重复使用表。在这种情况下，可以在临时表上创建索引以提高查询性能。但是，各种方案可能互不相同。Microsoft 建议您做一个测试，来验证表变量对于特定的查询或存储过程是否比临时表更有效。&
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：35908次
排名：千里之外
评论：35条
(1)(1)(3)(3)(1)(2)SQL Server 表变量和临时表的区别_百度知道
SQL Server 表变量和临时表的区别
提问者采纳
首先表变量和临时表声明方式肯定不同，一个declare 一个create 或 into出来；表变量可用于自定义函数（多语句表值函数）、存储过程和批处理中（这点临时表也可以），但是表变量类似于局部变量，有明确定义的作用域（一个go隔开，就不能再用了，呵呵）,在存储过程中使用表变量，减少了存储过程的重新编译量，效率上比临时表会高一点。但是也有不足的地方，表变量不支持事务，还不能有这样的写法INSERT INTO table_variable EXEC 存储过程。SELECT select旦範测既爻焕诧唯超沥_list INTO table_variable 语句。
其他类似问题
临时表的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁&与持久表一样，优化器创建并维护临时表的分布统计信息，并跟踪它的基数。当索引临时表时，这种能力尤其重要。当优化器需要评估选择性时，它就可以根据这些分布统计信息生成经过优化的计划。这是临时表在性能方面不同于表变量的主要特性之一。此外，因为临时表会维护统计信息，如果上次编译后被引用表有足够多的行发生变化（即达到重新编译阀值）代码会被重新编译。重新编译。重新编译阀值是根据表类型和行数计算得出的。对于持久表来说，如果n&=500,则RT=500（n=当编译查询计划时表的基数）；如果n&500，则RT=500+0.20*n。对于临时表，如果n&6，则RT=6；如果6&=n&=500，则RT=500；如果n&500，则RT=500+0.20*n。例如，向临时表中加载6行后，再增加第7行将导致重新编译，而持久表很晚才会出现第一次重新编译。如果你想在查询临时表使用KEEP PLAN查询提示即可。优化器维护临时表的分布统计信息这一特点及上述结论是选择临时对象类型的最重要特征。当从临时表和表变量中做出选择时，这些因素尤为重要，优化器不会为表变量创建或维护分布统计信息。虽然也为表变量维护行计数信息，但这个信息通常不准确。表变量自己不会引起重新编译，而重新编译对于更新行计数来说是必须的。在SQL SERVER 2005中，可以使用RECOMPILE查询提示以强制重新编译包含表变量的查询。总之，表变量不发生作用于优化的重新编译。我们必须问自己两个问题：1.优化器需要分布统计信息或精确基数评估来生成高效的执行计划吗？如果需要，当统计信息无效时，使用低效计划的成本是什么？2.使用临时表时重新编译的成本是什么？有时优化器不需要统计信息就可以计算出最佳计划。例如，从表中返回所有行的查询，在定义了唯一索引的列上执行的点查询，利用聚集索引或覆盖索引的范围查询等。在这些情况下，不论表的大小，都不会用到统计信息，只会产生重新编译的开销。这时，可以考虑使用表变量。此外，当表非常小时，可选的方法有：1）使用表变量，执行完全扫描，几乎或根本没有重新编译；2）使用临时表，执行index seek,产生更多的重新编译。由于重新编译的缺点，seek的优势也许比不上scan.这种情况也应该考虑使用表变量。另一方面，如果优化器的确需要统计信息可以生成高效的执行计划，而且你使用的又不是小表，那么使用低效计划的成本可能比重新编译的成本要高得多。这时应该考虑使用临时表。
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：63196次
积分：1397
积分：1397
排名：第13134名
原创：58篇
转载：56篇
评论：14条
(1)(1)(1)(1)(1)(1)(1)(2)(9)(22)(3)(5)(11)(13)(3)(1)(23)(2)(2)(1)(10)