表变量（Table Variables）
表变量总局限于提交到SQL Server的批处理语句范围。当你在批处理语句范围外引用表变量时，SQL Server就会返回你一条错误信息。这是和临时表相比第1个重大区别。下列代码向你展示了如何创建和使用表变量——只在简单存储过程的上下文里。
1 CREATE PROCEDURE DemonstrateTableVariablesNoRecompiles 2 AS 3 BEGIN 4 DECLARE @tempTable TABLE 5 ( 6 ID INT IDENTITY(1, 1) PRIMARY KEY, 7 FirstName CHAR(4000), 8 LastName CHAR(4000) 9 ) 10 11 INSERT INTO @TempTable (FirstName, LastName) 12 SELECT TOP 1000 name, name FROM master.dbo.syscolumns 13 14 SELECT * FROM @TempTable 15 END 16 GO
表变量的好处是它们不会引起任何重编译。当你执行这个存储过程并用SQL Server Profiler跟踪时，不会发现重编译事件。
1 EXEC dbo.DemonstrateTableVariablesNoRecompiles
为什么使用表变量就可以这样呢？首先表变量就是个变量——名副其实。当你定义你的表变量时，意味着你不会改变你的数据库架构。因此基于数据酷架构改变的重编译就可以避免。另外表变量是没有统计信息的。因此没有统计信息需要维护，第2个引起重编译原因也就消失了。
首先，这2样听起来都很棒，但当我们进一步分析时，就会发现它的重大缺点。我们来看看。表变量近乎就是个变量。在临时表里，表变量还是持续的。是的，你没看错：当你使用表变量时，会涉及到临时表里的物理I/O操作。这个可以用动态管理视图sys.dm_db_session_space_usage来验证，它是在会话级别跟踪临时表的使用率。我们来看下面的代码（请【新建查询】执行下列代码）：
1 -- Create a table variable 2 DECLARE @tempTable TABLE 3 ( 4 ID INT IDENTITY(1, 1) PRIMARY KEY, 5 FirstName CHAR(4000), 6 LastName CHAR(4000) 7 ) 8 9 -- Insert 4 records into the table variable 10 INSERT INTO @tempTable (FirstName, LastName) VALUES 11 ( 12 'Woody', 13 'Tu' 14 ), 15 ( 16 'Woody', 17 'Tu' 18 ), 19 ( 20 'Woody', 21 'Tu' 22 ), 23 ( 24 'Woody', 25 'Tu' 26 ) 27 28 -- Retrieve the data from the table variable. 29 -- The execution plan estimates 1 row. 30 SELECT * FROM @tempTable 31 GO 32 33 -- Review the space used in TempDb. 34 -- Our table variable currently needs 5 pages in TempDb. 35 -- The 5 needed pages from the table variable are already marked for deallocation (column "user_objects_dealloc_page_count") 36 SELECT * FROM sys.dm_db_session_space_usage 37 WHERE session_id = @@SPID 38 GO
从图中可以看出，这个表变量在临时表里需要分配5个页。因为这个表变量已经超过范围，这5个页面也已被标记为重分配（deallocation）。你要知道这个副作用。
表变量也没有统计信息。因此这里没有重编译发生。但是作为一个副作用，查询优化器始终认为估计行数为1.这个会非常，非常糟糕。如果你从表变量连接你数据库里另外一张表。在那个情况下，查选优化器在执行计划里引入嵌套循环连接（Nested Loop Join）运算符，引用的表变量作为外表，因为估计行数是1。如果事实上返回行是10000或更多的话，整个执行计划就谈不上最优。我们来看下面的例子（点击工具栏的
显示包含实际的执行计划）：
1 CREATE PROCEDURE BadPerformingQuery 2 AS 3 BEGIN 4 DECLARE @tempTable TABLE 5 ( 6 ID INT IDENTITY(1, 1) PRIMARY KEY, 7 FirstName CHAR(4000), 8 LastName CHAR(4000) 9 ) 10 11 INSERT INTO @TempTable (FirstName, LastName) 12 SELECT TOP 20000 name, name FROM master.dbo.syscolumns 13 14 -- The physical Join Operator will be a Nested Loop, 15 -- because Nested Loop is optimized for 1 row in the outer loop. 16 SELECT * FROM AdventureWorks2008R2.Person.Person p 17 INNER JOIN @tempTable t ON t.ID = p.BusinessEntityID 18 END 19 GO
我们仔细看下聚集索引扫描（ Clustered Index Scan）运算符的属性信息，你会看到这里的估计行数是1，而实际行数却是12622。
你可以通过自SQL Server 2005起引入的语句级别的重编译（Statement-Level Recompilation）来修正这个基数预估错误。
1 -- Use a statement-level recompilation to fix the problem with the 2 -- cardinality estimation. 3 ALTER PROCEDURE BadPerformingQuery 4 AS 5 BEGIN 6 DECLARE @tempTable TABLE 7 ( 8 ID INT IDENTITY(1, 1) PRIMARY KEY, 9 FirstName CHAR(4000), 10 LastName CHAR(4000) 11 ) 12 13 INSERT INTO @TempTable (FirstName, LastName) 14 SELECT TOP 20000 name, name FROM master.dbo.syscolumns 15 16 -- The physical Join Operator will be a Nested Loop, 17 -- because Nested Loop is optimized for 1 row in the outer loop. 18 SELECT * FROM AdventureWorks2008R2.Person.Person p 19 INNER JOIN @tempTable t ON t.ID = p.BusinessEntityID 20 OPTION (RECOMPILE) 21 END 22 GO
但是这个方法有点产生相反效果的（counter-productive），因为你又引入了重编译，原先你使用表变量就是为了避免重编译。
小结
使用表变量你可以避免SQL Server里重编译的负荷，但同样也有副作用。最大的副作用就是错误参数估计——估计行数为1。因此当你和小数量行打交道时可以使用表变量，因为那时错误的基数预估并不重要，也不影响你的性能。但和大量数据行打交道时，它会伤害你的性能，因为生成了低效的执行计划。
作为通常的经验法则（general rule-of-thumb），对于大数量的数据，你应该使用临时表，表变量用在小数量的数据上。但是你真的要为你的工作量测试（benchmark）下，来决定什么时候使用临时表，什么时候使用表变量是正确的。
