在上一篇文章里，我討論了使用臨時表如何引起SQL Server里的重編譯。在文章最后我提到，今天這篇文章我會聚焦表變量（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.syscolumns13     14     SELECT * FROM @TempTable15 END16 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 variable10 INSERT INTO @tempTable (FirstName, LastName) VALUES11 (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 @tempTable31 GO32 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_usage37 WHERE session_id = @@SPID38 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.syscolumns13     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 p17     INNER JOIN @tempTable t ON t.ID = p.BusinessEntityID18 END19 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.syscolumns15     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 p19     INNER JOIN @tempTable t ON t.ID = p.BusinessEntityID20     OPTION (RECOMPILE)21 END22 GO

但是這個方法有點產生相反效果的（counter-productive），因為你又引入了重編譯，原先你使用表變量就是為了避免重編譯。

使用表變量你可以避免SQL Server里重編譯的負荷，但同樣也有副作用。最大的副作用就是錯誤參數估計——估計行數為1。因此當你和小數量行打交道時可以使用表變量，因為那時錯誤的基數預估并不重要，也不影響你的性能。但和大量數據行打交道時，它會傷害你的性能，因為生成了低效的執行計劃。

作為通常的經驗法則（general rule-of-thumb），對于大數量的數據，你應該使用臨時表，表變量用在小數量的數據上。但是你真的要為你的工作量測試（benchmark）下，來決定什么時候使用臨時表，什么時候使用表變量是正確的。