說臨時表和表變量，這是一個古老的話題，我們在網上也找到很多的資料闡述兩者的特征，優點與缺點
這里我們在SQL Server 2005SQL Server 2008版本上通過舉例子，說明臨時表和表變量兩者的一些特征，讓我們對臨時表和表變量有進一步的認識。在本章中，我們將從下面幾個方面去進行描述，對其中的一些特征舉例子說明：
約束(Constraint) 索引(Index) I/0開銷 作用域(scope) 存儲位置 其他
例子描述
約束(Constraint)
在臨時表和表變量，都可以創建Constraint。針對表變量，只有定義時能加Constraint。
e.g.在Microsoft SQL Server Management Studio（MSSMS）查詢中，創建臨時表并建Constraint場景，
tempdb<br><br> object_id() <br> #1 <br><br> #1<br>(<br> ID ,<br> Nr nvarchar(50) ,<br> OperationTime datetime (getdate()),<br> PK_#1_ID (ID)<br>)<br><br> #1 CK_#1_Nr (Nr )<br><br>
中，可以看出在臨時表#1的創建時，創建Constraint如“Constraint PK_#1_ID Primary Key(ID)”，也可以在創建臨時表#1后創建Constraint，如“Alter Table #1 Add Constraint CK_#1_Nr Check(Nr Between ‘10001’ And’19999′)”，下面我們來看表變量的場景，在定義表變量時不能指定Constraint名，定義表變量后不能對表變量創建Constraint。
e.g. 在定義表變量時不能指定Constraint名
tempdb<br><br> @1 <br>(<br> ID ,<br> Nr nvarchar(50) ,<br> OperationTime datetime (getdate()),<br> [PK_@1_ID] (ID)<br>)<br>
在定義表變量后不能對表變量創建Constraint，
tempdb<br><br> @1 <br>(<br> ID ,<br> Nr nvarchar(50),<br> OperationTime datetime (getdate())<br>)<br><br> @1 [CK_@1_Nr] (Nr )<br>
在和中可以發現，在解析T-SQL語法過程就發生錯誤，也就是SQL Server不支持定義表變量時對Constraint命名，也不支持定義表變量后，對其建Constraint。
這里慎重提示下，在給臨時表建Constraint的時候，特別是在并發場景中，不要指定具體的Constraint名稱，不然會發生對象已存在的錯誤提示。
e.g. 在MSSMS中我們先執行之前的創建臨時表#1，不關閉當前會話的情況下，另建一個查詢，執行與相同的代碼，如圖
左邊的查詢窗口，是執行原先的，右邊的查詢窗口，是后執行相同的。在這里，我們注意紅色圈圈部分，發現在創建臨時表#1的過程，明確給了一個主鍵名稱“PK_#1_ID”，當右邊再創建相同臨時表#1的時候就發生了對象重復錯誤問題。我們也可以通過SQL Server提供的系統視圖sys.objects查詢約束“PK_#1_ID”的信息，
tempdb<br><br><br><br> * sys.objects name=<br>
在系統視圖sys.objects，發現“PK_#1_ID”名稱后面不加如何的隨機數值表述不同會話有不同的對象。根據SQL Server對sys.objects的描述規則，sys.objects中的Name列數據是唯一的。當另一個會話創建相同的對象時就會發生對象重復的錯誤。
在Constraint中，Foreign Key是不能應用與表變量，對于臨時表，創建Foreign Key是沒有意義的。也就是說臨時表不受Foreign Key約束。下面我們通過例子來說明臨時表的情況，
e.g.
tempdb<br><br> object_id() <br> #1 <br><br> object_id() <br> #2 <br><br> #1<br>(<br><br> ID ,<br> Nr nvarchar(50) ,<br> OperationTime datetime (getdate()),<br> PK_#1_ID (ID)<br>)<br> #1 CK_#1_Nr (Nr )<br> #2<br>(<br> ID ,<br> ForeignID , (ForeignID) #1(ID)<br>)<br>Go
可以看出對于臨時表不強制Foreign Key約束,我們也可以通過SQL Server系統視圖sys.foreign_keys查詢
tempdb<br><br> * sys.tables name <br> * sys.foreign_keys<br>
右邊的查詢，只看到在sys.tables表哦中存在剛才創建的臨時表#1和#2，在sys.foreign_keys看不到有關Foreign Key約束信息。這也驗證了左邊SQL Server提示的，在臨時表中無法強制使用Foreign Key約束。
索引(Index)
從索引方面看臨時表和表變量，與從Constraint上分析有些類似，在臨時表中，它與真實表一樣可以創建索引。在表變量定義過程中，也可以創建一些類似唯一和聚集索引。
e.g.
tempdb<br><br><br><br> @1 ( <br><br> ID ,<br><br> Nr nvarchar(50) <br><br>)<br><br>Insert @1 (id,Nr) (1,)<br><br>Insert @1 (id,Nr) (2,)<br><br>Insert @1 (id,Nr) (8,)<br><br>Insert @1 (id,Nr) (3,)<br><br>Insert @1 (id,Nr) (7,)<br><br> 2 *<br><br> sys.indexes a<br><br> sys.tables b b.object_id=a.object_id<br><br> b.create_date <br><br> Nr @1 Nr=<br><br><br>
上面截的是兩張圖，第一張圖描述在表變量使聚集Primary Key，創建非聚集的Unique約束，第二張圖描述查詢語句”Select Nr From @1 Where Nr=’10005’” 應用到在表變量創建的唯一索引“UQ_#……”
是于臨時表索引的例子，我們拿一個例子說明，與前邊說的Constraint例子有點相似，這里我們對臨時表創建索引，并給索引一個具體名稱，測試是否會重復。
e.g.在MSSMS新增兩個查詢，編寫下面的SQL語句：
tempdb<br><br> object_id() <br> #1 <br><br> #1<br>(<br> ID ,<br> Nr nvarchar(50) ,<br> OperationTime datetime (getdate()),<br>)<br><br> IX_#1_Nr #1(Nr )<br><br> b.name TableName,<br> a.* <br> sys.indexes a<br> sys.tables b b.object_id=a.object_id<br> b.name <br> b.create_date Asc
從返回的結果，我們看到在系統視圖表Sys.Indexes中，創建有兩個相同的索引”IX_#1_Nr”，但注意下object_id數據不同。在SQL Server中是允許不同的表索引名稱可以相同的。在并發的環境下，按原理是可以對臨時表創建的索引給明確名稱的。除非并發的情況會發生重復的表名或重復的Constraint，或其它系統資源不足的問題，才會導致出錯。
I/0開銷
臨時表與表變量，在I/O開銷的描述，我們直接通過一個特殊的例子去描述它們，在MSSMS上新增兩個查詢，分別輸入臨時表和表變量的測試代碼：
e.g.臨時表：
tempdb<br><br> object_id() <br> #1 <br><br> #1<br>(<br> ID ,<br> Nr nvarchar(50) ,<br> OperationTime datetime (getdate())<br>)<br><br>Insert #1(ID,Nr,OperationTime)<br> 50000 row_number() ( a.object_id),(a.name+b.name,50) ,a.create_date<br> master.sys.all_objects a ,sys.all_columns b<br> type=<br><br><br><br> Nr,(Nr) Sum_ <br> #1 <br> Nr <br> Nr<br>
表變量：
tempdb<br><br> @1 <br>(<br> ID ,<br> Nr nvarchar(50) ,<br> OperationTime datetime (getdate())<br>)<br><br>Insert @1(ID,Nr,OperationTime)<br> 50000 row_number() ( a.object_id),(a.name+b.name,50) ,a.create_date<br> master.sys.all_objects a ,sys.all_columns b<br> type=<br><br><br> Nr,(Nr) Sum_ <br> @1 <br> Nr <br> Nr<br>
和，主要是看最后的查詢語句I/O的開銷，兩者有何不同。通過上面的運行結果圖形描述，可以看出查詢開始，不管是臨時表還是表變量，都使用到了聚集索引掃描(Clustered Index Scan)，兩者雖然返回的數據一致，但I/O的開銷不同。臨時表的I/O開銷是0.324606,而表變量只有0.003125 相差非常大。在臨時表的執行計劃圖形中，我們發現一行“缺少索引（影響 71.9586):CREATE ……）”提示信息。我們對臨時表#1，在字段“Nr”上創建一個非聚集索引，再看執行執行結果：
IX_#1_Nr #1(Nr)
我們在臨時表#1上創建完索引“IX_#1_Nr”，運行看上面的圖形顯示，就感覺非常的有意思了。在臨時表#1查詢時用了索引搜索（Index Seek），而且I/O開銷減少到了0.0053742。雖然開始查詢的時候I/O開銷還是比表變量開始查詢的時候大一些，但執行步驟中比變變量少了一個“排序(Sort)”開銷，后最后的看回Select結果，估計子樹的成本比使用表變量的大大減少。
這里的例子只是描述一個特殊的情況，在真實的環境中，要根據實際的數據量來判斷是否使用臨時表或表變量。倘若在存儲過程中，當數據量非常少如只有不到50行記錄，數據占的頁面也不會超過1個頁面，那么使用表變量是一個很好的解決方案。
作用域(scope)
表變量像局部變量(local variable)一樣，有著很窄的作用域，只能應用于定義的函數、存儲過程或內。如，一個會話里面有幾個，那么表變量只能作用在它定義所在的范圍內。其他的無法再調用它。
e.g.在MSSMS新增一個查詢，編寫
tempdb<br><br> Nocount <br> @1 ( <br> ID ,<br> Nr nvarchar(50) <br>)<br>Insert @1 (id,Nr) (1,)<br>Insert @1 (id,Nr) (2,)<br>Insert @1 (id,Nr) (8,)<br>Insert @1 (id,Nr) (3,)<br>Insert @1 (id,Nr) (7,)<br><br> * @1<br><br> --結束點<br><br> * @1
所在的查詢相當于一個會話，”Go”描述的一個的結束點。在”Go”之前定義的表變量，在”Go”之后調用是發生“必須聲明變量”的錯誤提示。
臨時表與表變量不同，臨時表的作用域是當前會話都有效，一直到會話結束或者臨時表被Drop的時候。也就是說可以跨當前會話的幾個范圍。
e.g.
tempdb<br><br> object_id() <br> #1 <br><br> #1<br>(<br> ID ,<br> Nr nvarchar(50) ,<br> OperationTime datetime (getdate()),<br> PK_#1_ID (ID)<br>)<br> * #1<br><br> --結束點<br><br> * #1<br>
中可以看出在”GO”前后都可以查詢到臨時表#1。
在描述臨時表與表變量的作用域時，有個地方要注意的是，當 sp_executesql 或 Execute 語句執行字符串時，字符串將作為它的自包含執行. 如果表變量在sp_executesql 或 Execute 語句之前定義，在sp_executesql 或 Execute 語句的字符串中無法調用外部定義的表變量。
e.g.
tempdb<br><br> nocount <br> @1 ( <br> ID ,<br> Nr nvarchar(50) <br>)<br>Insert @1 (id,Nr) (1,)<br>Insert @1 (id,Nr) (2,)<br>Insert @1 (id,Nr) (8,)<br>Insert @1 (id,Nr) (3,)<br>Insert @1 (id,Nr) (7,)<br><br> * @1<br><br>(N)<br><br><br>
中，當執行到”Execute(N’Select * From @1′)”時候，同樣發生與一樣的錯誤提示“必須聲明變量@1”.
臨時表是可以在sp_executesql 或 Execute 語句執行字符串中被調用。這里不再舉例子，如果你有所模糊可以參考把表變量轉成臨時表測試下就能加深理解與記憶。
存儲位置
說到臨時表和表變量的存儲位置，我們可以看到有很多版本的說法，特別是表變量。有的說表變量數據存儲在內存中，有的說存儲在數據庫tempdb中，有的說有部分存儲在內存，部分存儲在數據庫tempdb中。根據我查到的官方資料，說的是在SQL Server 2000下:
“A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache). ”
在SQL Server 2005SQL2008的版本，表變量存儲與臨時表有相似，都會在數據庫tempdb創建，使用到tempdb存儲空間。
e.g.臨時表
tempdb<br><br> nocount <br><br> sp_spaceused <br><br> object_id() <br> #1 <br><br> #1(ID ,Nr nvarchar(50))<br>Insert #1 (ID,Nr)<br> (1) row_number() ( a.object_id),(a.name+b.name,50)<br> sys.all_objects a,<br> sys.all_columns b <br><br> (1) name,object_id,type,create_date sys.tables create_date <br><br> sp_spaceused <br><br><br>
在執行后，我們可以看到在數據庫tempdb中的表sys.tables創建有表#1。我們接著看空間的使用情況，插入數據之前，數據庫未使用空間(unallocated space)為510.39MB,向臨時表#1插入1條數據后，數據庫未使用空間為501.38MB,未使用空間變小了。再來看整個數據庫的數據(data)使用的空間變化,從552KB變成560KB,使用了一頁的數據空間(8kb)。這說明一點，臨時表，即使你只插入一條數據都會使用到數據庫tempdb的空間。也許會有人問，要是我只建臨時表#1,不插入數據，會如何。我們可以結果：
這里你會發現前后的空間大小不變，不過，不要認為沒有使用到數據庫tempdb數據空間，當你多用戶創建臨時表結構的時候，你就會發現其實都會應用到數據庫tempdb的空間。我這里創建了10個#1后的效果如：
相同的原理，我們使用類似的方法測試表變量的情況，發現結論是與臨時表的一致的，會使用到數據庫tempdb的空間.
e.g.表變量
tempdb<br><br> nocount <br> sp_spaceused <br><br> @1 (ID ,Nr nvarchar(50))<br>Insert @1 (ID,Nr)<br> (1) row_number() ( a.object_id),(a.name+b.name,50)<br> sys.all_objects a,<br> sys.all_columns b <br><br> (1) name,object_id,type,create_date sys.objects type= create_date <br><br> sp_spaceused <br><br><br> sp_spaceused <br>
中，我多寫了一個”GO”之后檢查空間大小的存儲過程sp_spaceused。這樣為了了更能體現表變量使用空間變化情況。從插入數據前和插入數據后的結果圖來看，表變量不僅在數據庫tempdb創建了表結構#267ABA7A類似的這樣表，表變量也應用到了數據庫tempdb的空間。不過這里注意一點就是在”Go”之后，我們發現表變量@1,會馬上釋放所使用的數據空間。為了更能體現使用空間情況。我們可以向表變量@1插入大量數據看空間變化情況（測試插入1000萬的數據行）。
e.g.
tempdb<br><br> nocount <br> sp_spaceused <br><br> @1 (ID ,Nr nvarchar(50))<br>Insert @1 (ID,Nr)<br> (10000000) row_number() ( a.object_id),(a.name+b.name,50)<br> sys.all_objects a,<br> sys.all_columns b <br><br> (1) name,object_id,type,create_date sys.objects type= create_date <br><br> sp_spaceused <br><br><br> sp_spaceused <br>
這里我們可清晰的看到數據庫tempdb的大小(database_size)變化情況，從插入數據前的552.75MB變成插入數據之后的892.75MB。非常有意思的是我們在”Go之后”發現數據庫大小保存在892.75MB,但數據使用空間(data)從560KB—>851464KB—>536KB ，說明SQL Server自動釋放為使用的數據空間，但不會馬上自動釋放數據庫分配的磁盤空間。我們在實際的環境中，發現臨時數據庫tempdb使用的磁盤空間越來越大，這是其中的原因之一。
其他
臨時表與表變量，還有其他的特征，如臨時表受事務回滾，而表變量不受事務回滾影響。對應事務方面，更為正確的說法是表變量的事務只在表變量更新期間存在。因此減少了表變量對鎖定和記錄資源的需求。
e.g.
tempdb<br><br> nocount <br><br> object_id() <br> #1 <br> #1(ID ,Nr nvarchar(50))<br> @1 (ID ,Nr nvarchar(50))<br><br><br><br>Insert #1 (ID,Nr)<br> (1) row_number() ( a.object_id),(a.name+b.name,50)<br> sys.all_objects a,<br> sys.all_columns b <br><br><br>Insert @1 (ID,Nr)<br> (1) row_number() ( a.object_id),(a.name+b.name,50)<br> sys.all_objects a,<br> sys.all_columns b <br><br><br><br> * #1<br> * @1<br><br><br><br>
這里發現”Rollback Tran”之后，臨時表#1沒有數據插入，而表變量@1還有一條數據存在。說明表變量不受”Rollback Tran”所影響。它的行為有類似于局部變量一樣。
另外SQL Server對表變量不保留任何的統計信息，因為如此，我們在數據量大的時候使用表變量，發現比臨時表要慢許多。前面在I/O開銷那里我們取有一個特殊的例子，這里不再舉例。
小結
無論如何，臨時表和表變量有各自的特征，有自己優點和缺點。在不同的場景選擇它們靈活應用。本文章是我對臨時表和表變量的一些認識理解，可能有些地方說的不夠好或者遺漏，你可以留言或Email與我聯系，我會繼續改進或糾正，我也不希望有些錯誤的見解會誤導別人。正如說的一句” I’d hate to think of anyone being misled by my advice!”.
附參考：
