[轉]SQL Server 表變數和暫存資料表的區別

標籤：style   blog   http   color   io   使用   java   ar   strong   

一、表變數

　　表變數在SQL Server 2000中首次被引入。表變數的具體定義包括列定義，列名，資料類型和約束。而在表變數中可以使用的約束包括主鍵約束，唯一約束，NULL約束和CHECK約束(外鍵約束不能在表變數中使用)。定義表變數的語句是和正常使用Create Table定義表語句的子集。只是表變數通過DECLARE @local_variable語句進行定義。

　　表變數的特徵：

1. 表變數擁有特定範圍(在當前批處理語句中，但不在任何當前批處理語句調用的預存程序和函數中)，表變數在批處理結束後自動被清除。
2. 表變數較暫存資料表產生更少的預存程序重編譯。
3. 針對錶變數的事務僅僅在更新資料時生效，所以鎖和日誌產生的數量會更少。
4. 由於表變數的範圍如此之小，而且不屬於資料庫的持久部分，所以交易回復不會影響表變數。

　　表變數可以在其範圍內像正常的表一樣使用。更確切的說，表變數可以被當成正常的表或者表運算式一樣在SELECT,DELETE,UPDATE,INSERT語句中使用，但是表變數不能在類似"SELECT select_list INTO table_variable"這樣的語句中使用。而在SQL Server2000中，表變數也不能用於INSERT INTO table_variable EXEC stored_procedure這樣的語句中。

　　表變數不能做如下事情:

1. 雖然表變數是一個變數，但是其不能賦值給另一個變數。
2. check約束，預設值和計算資料行不能引用自訂函數。
3. 不能為約束命名。
4. 不能Truncate表變數。
5. 不能向識別欄位中插入顯式值(也就是說表變數不支援SET IDENTITY_INSERT ON)

　 下面來玩玩表變數吧。

　　定義一個表變數，插入一條資料，然後查詢：

　　DECLARE @tb1 Table　　( 　　Id int, 　　Name varchar(20), 　　Age int　　)　　INSERT INTO @tb1 VALUES(1,‘劉備‘,22)　　SELECT * FROM @tb1

　　輸出結果如下：

　　

　　再來試試一些不符合要求的情況，例如添加表變數後，添加約束，並對約束命名：

　　ALTER TABLE @tb1    ADD CONSTRAINT CN_AccountAge    CHECK     (Account_Age > 18);    -- 插入年齡必須大於18

　　SQL Server提示錯誤如下：

　　

　　SQL Server不支援定義表變數時對Constraint命名，也不支援定義表變數後，對其建Constraint。

　　更多的不允許，請查看上面的要求。

二、暫存資料表

　　在深入暫存資料表之前，我們要瞭解一下會話(Session)，一個會話僅僅是一個用戶端到資料引擎的串連。在SQL Server Management Studio中，每一個查詢時段都會和資料庫引擎建立串連。一個應用程式可以和資料庫建立一個或多個串連，除此之外，應用程式還可能建立串連後一直不釋放知道應用程式結束，也可能使用完釋放串連需要時建立串連。

　　暫存資料表和Create Table語句建立的表有著相同的物理工程，但暫存資料表與正常的表不同之處有：

　　1、暫存資料表的名稱不能超過116個字元，這是由於資料庫引擎為了辨別不同會話建立不同的暫存資料表，所以會自動在暫存資料表的名字後附加一串。

　　2、局部暫存資料表(以"#"開頭命名的)範圍僅僅在當前的串連內，從在預存程序中建立局部暫存資料表的角度來看，局部暫存資料表會在下列情況下被Drop： 　　　　a、顯示調用Drop Table語句 　　　　b、當局部暫存資料表在預存程序內被建立時，預存程序結束也就意味著局部暫存資料表被Drop。 　　　　c、當前會話結束，在會話內建立的所有局部暫存資料表都會被Drop。

　　3、全域暫存資料表(以"##"開頭命名的)在所有的會話內可見，所以在建立全域暫存資料表之前首先檢查其是否存在，否則如果已經存在，你將會得到重複建立對象的錯誤。 　　　　a、全域暫存資料表會在建立其的會話結束後被Drop，Drop後其他會話將不能對全域暫存資料表進行引用。 　　　　b、引用是在語句層級進行，如: 　　　　　　1.建立查詢時段，運行語句：

　　CREATE TABLE ##temp(RowID int)　　INSERT INTO ##temp VALUES(3)

　　　　　　2.再次建立一個查詢時段，每5秒引用一次全域暫存資料表

　　While 1=1 　　BEGIN　　SELECT * FROM ##temp　　WAITFOR delay ‘00:00:05‘　　END

　　　　　　3.回到第一個視窗，關閉視窗。 　　　　　　4.下一次第二個視窗引用時，將產生錯誤。

　　　　　　

　　4、不能對暫存資料表進行分區。

　　5、不能對暫存資料表加外鍵約束。

　　6、暫存資料表內列的資料類型不能定義成沒有在TempDb中沒有定義自訂資料類型(自訂資料類型是資料庫層級的對象，而暫存資料表屬於TempDb)。由於TempDb在每次SQL Server重啟後會被自動建立，所以你必須使用startup stored procedure來為TempDb建立自訂資料類型。你也可以通過修改Model資料庫來達到這一目標。

　　7、XML列不能定義成XML集合的形式，除非這個集合已經在TempDb中定義。

　　暫存資料表既可以通過Create Table語句建立，也可以通過"SELECT <select_list> INTO #table"語句建立。你還可以針對暫存資料表用"INSERT INTO #table EXEC stored_procedure"這樣的語句。 　　暫存資料表可以擁有命名的約束和索引。但是，當兩個使用者在同一時間調用同一預存程序時，將會產生”There is already an object named ‘<objectname>’ in the database”這樣的錯誤。所以最好的做法是不用為建立的對象進行命名，而使用系統分配的在TempDb中唯一的。

三、誤區

　　誤區1.表變數僅僅在記憶體中。

　　誤區2.暫存資料表僅僅儲存在物理介質中。

　　這兩種觀點都是錯誤的，只有記憶體足夠，表變數和暫存資料表都會在記憶體中建立和處理。他們也同樣可以在任何時間被存入磁碟。

    　　注意表變數的名字是系統分配的，表變數的第一個字元”@”並不是一個字母，所以它並不是一個有效變數名。系統會在TempDb中為表變數建立一個系統分配的名稱，所以任何在sysobjects或sys.tables尋找表變數的方法都會失敗。

　　正確的方法應該是我前面例子中的方法，我看到很多人使用如下查詢查表變數：

　　select * from sysobjects where name like‘#tempTables%‘

　　上述代碼看上去貌似很好用，但會產生多使用者的問題。你建立兩個串連，在第一個串連中建立暫存資料表，在第二個視窗中運行上面的語句能看到第一個串連建立的暫存資料表，如果你在第二個串連中嘗試操作這個暫存資料表，那麼可能會產生錯誤，因為這個暫存資料表不屬於你的會話。

　　誤區3.表變數不能擁有索引。

    這個誤區也同樣錯誤。雖然一旦你建立一個表變數之後，就不能對其進行DDL語句了，這包括Create Index語句。然而你可以在表變數定義的時候為其建立索引)比如如下語句。

　　declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED) 

    這個語句將會建立一個擁有叢集索引的表變數。由於主鍵有了對應的叢集索引，所以一個系統命名的索引將會被建立在RowID列上。

    下面的例子示範你可以在一個表變數的列上建立唯一約束以及如何建立複合索引。

 　　declare @temp TABLE (   　　RowID int NOT NULL,   　　ColA int NOT NULL,   　　ColB char(1)UNIQUE,   　　PRIMARY KEY CLUSTERED(RowID, ColA))

　　1) SQL 並不能為表變數建立統計資訊，就像其能為暫存資料表建立統計資訊一樣。這意味著對於表變數，執行引擎認為其只有1行，這也意味著針對錶變數的執行計畫並不是最優。雖然估計的執行計畫對於表變數和暫存資料表都為1，但是實際的執行計畫對於暫存資料表會根據每次預存程序的重編譯而改變。如果暫存資料表不存在，在產生執行計畫的時候會產生錯誤。

　　2) 一旦建立表變數後就無法對其進行DDL語句操作。因此如果需要為表建立索引或者加一列，你需要暫存資料表。

　　3) 表變數不能使用select …into語句，而暫存資料表可以。

　　4) 在SQL Server 2008中，你可以將表變數作為參數傳入預存程序。但是暫存資料表不行。在SQL Server 2000和2005中表變數也不行。

　　5) 範圍:表變數僅僅在當前的批處理中有效,並且對任何在其中嵌套的預存程序等不可見。局部暫存資料表只在當前會話中有效，這也包括嵌套的預存程序。但對父預存程序不可見。全域暫存資料表可以在任何會話中可見，但是會隨著建立其的會話終止而DROP，其它會話這時就不能再引用全域暫存資料表。

　　6) 定序：表變數使用當前資料庫的定序，暫存資料表使用TempDb的定序。如果它們不相容，你還需要在查詢或者表定義中進行指定。

　　7) 你如果希望在動態SQL中使用表變數，你必須在動態SQL中定義表變數。而暫存資料表可以提前定義，在動態SQL中進行引用。

四、如何選擇

　　微軟推薦使用表變數，如果表中的行數非常小，則使用表變數。很多”網路專家”會告訴你100是一個分界線,因為這是統計資訊建立查詢計劃效率高低的開始。但是我還是希望告訴你針對你的特定需求對暫存資料表和表變數進行測試。很多人在自訂函數中使用表變數，如果你需要在表變數中使用主鍵和唯一索引，你會發現包含數千行的表變數也依然效能卓越。但如果你需要將表變數和其它表進行join,你會發現由於不精準的執行計畫，效能往往會非常差。

　　為了證明這點，請看本文的附件。附件中代碼建立了表變數和暫存資料表.並裝入了AdventureWorks資料庫的Sales.SalesOrderDetail表。為了得到足夠的測試資料，我將這個表中的資料插入了10遍。然後以ModifiedDate 列作為條件將暫存資料表和表變數與原始的Sales.SalesOrderDetail表進行了Join操作，從統計資訊來看IO差別顯著。從時間來看錶變數做join花了50多秒，而暫存資料表僅僅花了8秒。

　　如果你需要在表建立後對錶進行DLL操作，那麼選擇暫存資料表吧。

　　暫存資料表和表變數有很多類似的地方。所以有時候並沒有具體的細則規定如何選擇哪一個。對任何特定的情況，你都需要考慮其各自優缺點並做一些效能測試。下面的表格會讓你比較其優略有了更詳細的參考。

五、總結

特性	表變數	暫存資料表
範圍	當前批處理	當前會話，嵌套預存程序，全域：所有會話
使用情境	自訂函數，預存程序，批處理	自訂函數，預存程序，批處理
建立方式	DECLARE statement only.只能通過DECLEARE語句建立	CREATE TABLE 語句 SELECT INTO 語句.
表名長度	最多128位元組	最多116位元組
列類型	可以使用自訂資料類型 可以使用XML集合	自訂資料類型和XML集合必須在TempDb內定義
Collation	字串定序繼承自當前資料庫	字串定序繼承自TempDb資料庫
索引	索引必須在表定義時建立	索引可以在表建立後建立
約束	PRIMARY KEY, UNIQUE, NULL, CHECK約束可以使用，但必須在表建立時聲明	PRIMARY KEY, UNIQUE, NULL, CHECK. 約束可以使用，可以在任何時後添加，但不能有外鍵約束
表建立後使用DDL (索引，列)	不允許	允許.
資料插入方式	INSERT 語句 (SQL 2000: 不能使用INSERT/EXEC).	INSERT 語句, 包括 INSERT/EXEC. SELECT INTO 語句.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	不支援SET IDENTITY_INSERT語句	支援SET IDENTITY_INSERT語句
Truncate table	不允許	允許
析構方式	批處理結束後自動析構	顯式調用 DROP TABLE 語句. 當前會話結束自動析構 (全域暫存資料表: 還包括當其它會話語句不在參考資料表.)
事務	只會在更新表的時候有事務，期間比暫存資料表短	正常的事務長度，比表變數長
預存程序重編譯	否	會導致重編譯
復原	不會被復原影響	會被復原影響
統計資料	不建立統計資料，所以所有的估計行數都為1,所以產生執行計畫會不精準	建立統計資料，通過實際的行數產生執行計畫。
作為參數傳入預存程序	僅僅在SQL Server2008, 並且必須預定義 user-defined table type.	不允許
顯式命名物件 (索引, 約束).	不允許	允許，但是要注意多使用者的問題
動態SQL	必須在動態SQL中定義表變數	可以在調用動態SQL之前定義暫存資料表

 

出處:http://www.cnblogs.com/CareySon/archive/2012/06/11/TableVariableAndTempTable.html

[轉]SQL Server 表變數和暫存資料表的區別