在上一篇文章裡，我讨論了使用臨時表如何引起SQL Server裡的重編譯。在文章最後我提到，今天這篇文章我會聚焦表變量（Table Variables）的更多資訊，它可以避免重編譯的昂貴開銷。我們來詳細分析下。
表變量（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）下，來決定什麼時候使用臨時表，什麼時候使用表變量是正确的。
參考文章：
https://www.sqlpassion.at/archive/2014/11/16/recompilations-part-2-2/
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！