SQL SERVER中临时表、持久表、表变量的区别

 与持久表一样，优化器创建并维护临时表的分布统计信息，并跟踪它的基数。当索引临时表时，这种能力尤其重要。当优化器需要评估选择性时，它就可以根据这些分布统计信息生成经过优化的计划。这是临时表在性能方面不同于表变量的主要特性之一。
此外，因为临时表会维护统计信息，如果上次编译后被引用表有足够多的行发生变化（即达到重新编译阀值）代码会被重新编译。重新编译。重新编译阀值是根据表类型和行数计算得出的。
对于持久表来说，如果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.这种情况也应该考虑使用表变量。
另一方面，如果优化器的确需要统计信息可以生成高效的执行计划，而且你使用的又不是小表，那么使用低效计划的成本可能比重新编译的成本要高得多。这时应该考虑使用临时表。