在上一篇文章里，我討論了使用臨時表如何引起SQL Server里的重編譯。在文章最后我提到，今天這篇文章我會聚焦表變量（Table Variables）的更多信息，它可以避免重編譯的昂貴開銷。我們來詳細(xì)分析下。

表變量總局限于提交到SQL Server的批處理語句范圍。當(dāng)你在批處理語句范圍外引用表變量時，SQL Server就會返回你一條錯誤信息。這是和臨時表相比第1個重大區(qū)別。下列代碼向你展示了如何創(chuàng)建和使用表變量——只在簡單存儲過程的上下文里。

 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

表變量的好處是它們不會引起任何重編譯。當(dāng)你執(zhí)行這個存儲過程并用SQL Server Profiler跟蹤時，不會發(fā)現(xiàn)重編譯事件。

1 EXEC dbo.DemonstrateTableVariablesNoRecompiles

為什么使用表變量就可以這樣呢？首先表變量就是個變量——名副其實(shí)。當(dāng)你定義你的表變量時，意味著你不會改變你的數(shù)據(jù)庫架構(gòu)。因此基于數(shù)據(jù)酷架構(gòu)改變的重編譯就可以避免。另外表變量是沒有統(tǒng)計(jì)信息的。因此沒有統(tǒng)計(jì)信息需要維護(hù)，第2個引起重編譯原因也就消失了。

首先，這2樣聽起來都很棒，但當(dāng)我們進(jìn)一步分析時，就會發(fā)現(xiàn)它的重大缺點(diǎn)。我們來看看。表變量近乎就是個變量。在臨時表里，表變量還是持續(xù)的。是的，你沒看錯：當(dāng)你使用表變量時，會涉及到臨時表里的物理I/O操作。這個可以用動態(tài)管理視圖sys.dm_db_session_space_usage來驗(yàn)證，它是在會話級別跟蹤臨時表的使用率。我們來看下面的代碼（請【新建查詢】執(zhí)行下列代碼）：

 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個頁。因?yàn)檫@個表變量已經(jīng)超過范圍，這5個頁面也已被標(biāo)記為重分配（deallocation）。你要知道這個副作用。

表變量也沒有統(tǒng)計(jì)信息。因此這里沒有重編譯發(fā)生。但是作為一個副作用，查詢優(yōu)化器始終認(rèn)為估計(jì)行數(shù)為1.這個會非常，非常糟糕。如果你從表變量連接你數(shù)據(jù)庫里另外一張表。在那個情況下，查選優(yōu)化器在執(zhí)行計(jì)劃里引入嵌套循環(huán)連接（Nested Loop Join）運(yùn)算符，引用的表變量作為外表，因?yàn)楣烙?jì)行數(shù)是1。如果事實(shí)上返回行是10000或更多的話，整個執(zhí)行計(jì)劃就談不上最優(yōu)。我們來看下面的例子（點(diǎn)擊工具欄的顯示包含實(shí)際的執(zhí)行計(jì)劃）：

 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

我們仔細(xì)看下聚集索引掃描（Clustered Index Scan）運(yùn)算符的屬性信息，你會看到這里的估計(jì)行數(shù)是1，而實(shí)際行數(shù)卻是12622。

你可以通過自SQL Server 2005起引入的語句級別的重編譯（Statement-Level Recompilation）來修正這個基數(shù)預(yù)估錯誤。

 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

但是這個方法有點(diǎn)產(chǎn)生相反效果的（counter-productive），因?yàn)槟阌忠肓酥鼐幾g，原先你使用表變量就是為了避免重編譯。

使用表變量你可以避免SQL Server里重編譯的負(fù)荷，但同樣也有副作用。最大的副作用就是錯誤參數(shù)估計(jì)——估計(jì)行數(shù)為1。因此當(dāng)你和小數(shù)量行打交道時可以使用表變量，因?yàn)槟菚r錯誤的基數(shù)預(yù)估并不重要，也不影響你的性能。但和大量數(shù)據(jù)行打交道時，它會傷害你的性能，因?yàn)樯闪说托У膱?zhí)行計(jì)劃。

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