再议SQL Server临时表和表变量

【 tulaoshi.com - 编程语言 】

　　今天在我和一家软件公司的开发人员讨论数据库设计调优的时候又讨论到了表变量和临时表的问题，觉得这个问题确实是一个争议比较大的问题。

　　其实从上次发表了表变量和临时表的一个帖子http://database.ctocio.com.cn/tips/442/8206442.shtml以来，也有些人留言，也有些人发过邮件讨论这个问题。其实表变量和临时表的区别虽然有一些，但是两者最根本的区别还是在于

　　对存储的需求：表变量和临时表都消耗Tempdb中的存储空间，但是进行数据更新的时候，表变量不会写日志，而临时表则会写日志。(这一点是经过脚本测试的，表变量并不像我们想象的那样，只写在内存而不出现在Tempdb中。)

　　对优化的支持：表变量不支持索引和统计数据，临时表则可以支持索引和统计数据。

　　通常需要表变量或者临时表的情况都是一些需要支持临时计算结果集的地方，那么就有一些常见的情况了：

　　如果临时结果集仅仅需要往里面写数据，比如通过一个循环多次查找相关数据并合成一个临时结果集，那么就可以使用表变量。(结果有人提到了返回结果集的时候需要有排序，但是表变量不支持索引阿。其实这个不要紧，因为表变量虽然不支持索引，但是表变量支持主键阿，所以可以利用主键来替代索引。)

　　如果临时结果集不太多需要更改，而是更多地充当一个临时的关联数据集去参加各种数据集的连接(JOIN)，那么索引和统计数据可能会更加适合一些(当然这个临时结果集要足够大，这样索引和统计数据带来的代价才可以被弥补掉)。

　　由于表变量不支持统计数据，因此在一个存储过程中使用表变量可以减少由于数据变化而导致的重新编译问题。

　　当然，除了索引和统计数据这个明显的限制外，表变量同时也不支持并行执行计划，因此对于大型的临时结果集，表变量也不是一个好的选择。

　　前面一个关于表变量和临时表的贴子，有一位robi_xu的朋友提到的问题也确实是在选择表变量和临时表时候的一些问题。

　　对于函数中不能支持临时表是由于函数不能对函数作用域外部的资源状态造成永久性的更改，在SQL Server中也称为副作用(side effect)。不过如果在函数中使用大型的临时结果集是不推荐的，因为如果将这样的函数放置到一个查询中会造成很明显的性能问题，因此这种情况一般都采用存储过程之类的批处理脚本。

　　对于动态脚本不支持表变量的原因是因为存储过程不接受表类型的参数。不过如果表变量的声明和赋值都在sp_executesql的参数中的话，sp_executesql就可以执行了，因为这个时候表变量就存在sp_executesql的stmt参数里面，不需要传入，例如下面的代码：(当然这样的实用性也就没有多少了)

　　DECLARE @m nvarchar(max)
　　SET @m = N'DECLARE @t TABLE (ID int);INSERT INTO @t VALUES (1);SELECT * FROM @t T'
　　EXEC sp_executesql @m