SQL Server 表变量和临时表的区别(详细补充篇)

　　上述代码看上去貌似很好用，但会产生多用户的问题。你建立两个连接，在第一个连接中创建临时表，在第二个窗口中运行上面的语句能看到第一个连接创建的临时表，如果你在第二个连接中尝试操作这个临时表，那么可能会产生错误，因为这个临时表不属于你的会话。

　　误区3.表变量不能拥有索引。

这个误区也同样错误。虽然一旦你创建一个表变量之后，就不能对其进行DDL语句了，这包括Create Index语句。然而你可以在表变量定义的时候为其创建索引)比如如下语句。

　　declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

这个语句将会创建一个拥有聚集索引的表变量。由于主键有了对应的聚集索引，所以一个系统命名的索引将会被创建在RowID列上。

下面的例子演示你可以在一个表变量的列上创建唯一约束以及如何建立复合索引。

declare @temp TABLE (
 　　RowID int NOT NULL,
 　　ColA int NOT NULL,
 　　ColB char(1)UNIQUE,
 　　PRIMARY KEY CLUSTERED(RowID, ColA))

1) SQL 并不能为表变量建立统计信息，就像其能为临时表建立统计信息一样。这意味着对于表变量，执行引擎认为其只有1行，这也意味着针对表变量的执行计划并不是最优。虽然估计的执行计划对于表变量和临时表都为1，但是实际的执行计划对于临时表会根据每次存储过程的重编译而改变。如果临时表不存在，在生成执行计划的时候会产生错误。

2) 一旦建立表变量后就无法对其进行DDL语句操作。因此如果需要为表建立索引或者加一列，你需要临时表。

3) 表变量不能使用select …into语句，而临时表可以。

4) 在SQL Server 2008中，你可以将表变量作为参数传入存储过程。但是临时表不行。在SQL Server 2000和2005中表变量也不行。

5) 作用域:表变量仅仅在当前的批处理中有效,并且对任何在其中嵌套的存储过程等不可见。局部临时表只在当前会话中有效，这也包括嵌套的存储过程。但对父存储过程不可见。全局临时表可以在任何会话中可见，但是会随着创建其的会话终止而DROP，其它会话这时就不能再引用全局临时表。

6) 排序规则：表变量使用当前数据库的排序规则，临时表使用TempDb的排序规则。如果它们不兼容，你还需要在查询或者表定义中进行指定。

7) 你如果希望在动态SQL中使用表变量，你必须在动态SQL中定义表变量。而临时表可以提前定义，在动态SQL中进行引用。

四、如何选择

微软推荐使用表变量，如果表中的行数非常小，则使用表变量。很多”网络专家”会告诉你100是一个分界线,因为这是统计信息创建查询计划效率高低的开始。但是我还是希望告诉你针对你的特定需求对临时表和表变量进行测试。很多人在自定义函数中使用表变量，如果你需要在表变量中使用主键和唯一索引，你会发现包含数千行的表变量也依然性能卓越。但如果你需要将表变量和其它表进行join,你会发现由于不精准的执行计划，性能往往会非常差。

为了证明这点，请看本文的附件。附件中代码创建了表变量和临时表.并装入了AdventureWorks数据库的Sales.SalesOrderDetail表。为了得到足够的测试数据，我将这个表中的数据插入了10遍。然后以ModifiedDate 列作为条件将临时表和表变量与原始的Sales.SalesOrderDetail表进行了Join操作，从统计信息来看IO差别显著。从时间来看表变量做join花了50多秒，而临时表仅仅花了8秒。

如果你需要在表建立后对表进行DLL操作，那么选择临时表吧。

临时表和表变量有很多类似的地方。所以有时候并没有具体的细则规定如何选择哪一个。对任何特定的情况，你都需要考虑其各自优缺点并做一些性能测试。下面的表格会让你比较其优略有了更详细的参考。

（编辑：南京站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!