SQL Server 表變量和臨時表的區(qū)別(詳細補充篇)

一、表變量

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

　　表變量的特征：
1.表變量擁有特定作用域(在當前批處理語句中，但不在任何當前批處理語句調(diào)用的存儲過程和函數(shù)中)，表變量在批處理結束后自動被清除。
2.表變量較臨時表產(chǎn)生更少的存儲過程重編譯。
3.針對表變量的事務僅僅在更新數(shù)據(jù)時生效，所以鎖和日志產(chǎn)生的數(shù)量會更少。
4.由于表變量的作用域如此之小，而且不屬于數(shù)據(jù)庫的持久部分，所以事務回滾不會影響表變量。

　　表變量可以在其作用域內(nèi)像正常的表一樣使用。更確切的說，表變量可以被當成正常的表或者表表達式一樣在SELECT,DELETE,UPDATE,INSERT語句中使用，但是表變量不能在類似"SELECT select_list INTO table_variable"這樣的語句中使用。而在SQL Server2000中，表變量也不能用于INSERT INTO table_variable EXEC stored_procedure這樣的語句中。

　　表變量不能做如下事情:
1.雖然表變量是一個變量，但是其不能賦值給另一個變量。
2.check約束，默認值和計算列不能引用自定義函數(shù)。
3.不能為約束命名。
4.不能Truncate表變量。
5.不能向標識列中插入顯式值(也就是說表變量不支持SET IDENTITY_INSERT ON)

 　 下面來玩玩表變量吧。

　　定義一個表變量，插入一條數(shù)據(jù)，然后查詢：

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)，一個會話僅僅是一個客戶端到數(shù)據(jù)引擎的連接。在SQL Server Management Studio中，每一個查詢窗口都會和數(shù)據(jù)庫引擎建立連接。一個應用程序可以和數(shù)據(jù)庫建立一個或多個連接，除此之外，應用程序還可能建立連接后一直不釋放知道應用程序結束，也可能使用完釋放連接需要時建立連接。

　　臨時表和Create Table語句創(chuàng)建的表有著相同的物理工程，但臨時表與正常的表不同之處有：

　　1、臨時表的名稱不能超過116個字符，這是由于數(shù)據(jù)庫引擎為了辨別不同會話建立不同的臨時表，所以會自動在臨時表的名字后附加一串。

　　2、局部臨時表(以"#"開頭命名的)作用域僅僅在當前的連接內(nèi)，從在存儲過程中建立局部臨時表的角度來看，局部臨時表會在下列情況下被Drop：
　　　　a、顯示調(diào)用Drop Table語句
　　　　b、當局部臨時表在存儲過程內(nèi)被創(chuàng)建時，存儲過程結束也就意味著局部臨時表被Drop。
　　　　c、當前會話結束，在會話內(nèi)創(chuàng)建的所有局部臨時表都會被Drop。

　　3、全局臨時表(以"##"開頭命名的)在所有的會話內(nèi)可見，所以在創(chuàng)建全局臨時表之前首先檢查其是否存在，否則如果已經(jīng)存在，你將會得到重復創(chuàng)建對象的錯誤。
　　　　a、全局臨時表會在創(chuàng)建其的會話結束后被Drop，Drop后其他會話將不能對全局臨時表進行引用。
　　　　b、引用是在語句級別進行，如:
　　　　　　1.新建查詢窗口，運行語句：

2.再次新建一個查詢窗口，每5秒引用一次全局臨時表

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

3.回到第一個窗口，關閉窗口。
4.下一次第二個窗口引用時，將產(chǎn)生錯誤。

4、不能對臨時表進行分區(qū)。
5、不能對臨時表加外鍵約束。
6、臨時表內(nèi)列的數(shù)據(jù)類型不能定義成沒有在TempDb中沒有定義自定義數(shù)據(jù)類型(自定義數(shù)據(jù)類型是數(shù)據(jù)庫級別的對象，而臨時表屬于TempDb)。由于TempDb在每次SQL Server重啟后會被自動創(chuàng)建，所以你必須使用startup stored procedure來為TempDb創(chuàng)建自定義數(shù)據(jù)類型。你也可以通過修改Model數(shù)據(jù)庫來達到這一目標。
7、XML列不能定義成XML集合的形式，除非這個集合已經(jīng)在TempDb中定義。
臨時表既可以通過Create Table語句創(chuàng)建，也可以通過"SELECT select_list> INTO #table"語句創(chuàng)建。你還可以針對臨時表用"INSERT INTO #table EXEC stored_procedure"這樣的語句。
 臨時表可以擁有命名的約束和索引。但是，當兩個用戶在同一時間調(diào)用同一存儲過程時，將會產(chǎn)生”There is already an object named ‘objectname>' in the database”這樣的錯誤。所以最好的做法是不用為建立的對象進行命名，而使用系統(tǒng)分配的在TempDb中唯一的。

三、誤區(qū)

誤區(qū)1.表變量僅僅在內(nèi)存中。

誤區(qū)2.臨時表僅僅存儲在物理介質中。

這兩種觀點都是錯誤的，只有內(nèi)存足夠，表變量和臨時表都會在內(nèi)存中創(chuàng)建和處理。他們也同樣可以在任何時間被存入磁盤。
　　注意表變量的名字是系統(tǒng)分配的，表變量的第一個字符”@”并不是一個字母，所以它并不是一個有效的變量名。系統(tǒng)會在TempDb中為表變量創(chuàng)建一個系統(tǒng)分配的名稱，所以任何在sysobjects或sys.tables查找表變量的方法都會失敗。

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

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

　　上述代碼看上去貌似很好用，但會產(chǎn)生多用戶的問題。你建立兩個連接，在第一個連接中創(chuàng)建臨時表，在第二個窗口中運行上面的語句能看到第一個連接創(chuàng)建的臨時表，如果你在第二個連接中嘗試操作這個臨時表，那么可能會產(chǎn)生錯誤，因為這個臨時表不屬于你的會話。

　　誤區(qū)3.表變量不能擁有索引。

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

　　declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

這個語句將會創(chuàng)建一個擁有聚集索引的表變量。由于主鍵有了對應的聚集索引，所以一個系統(tǒng)命名的索引將會被創(chuàng)建在RowID列上。

下面的例子演示你可以在一個表變量的列上創(chuàng)建唯一約束以及如何建立復合索引。

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

1) SQL 并不能為表變量建立統(tǒng)計信息，就像其能為臨時表建立統(tǒng)計信息一樣。這意味著對于表變量，執(zhí)行引擎認為其只有1行，這也意味著針對表變量的執(zhí)行計劃并不是最優(yōu)。雖然估計的執(zhí)行計劃對于表變量和臨時表都為1，但是實際的執(zhí)行計劃對于臨時表會根據(jù)每次存儲過程的重編譯而改變。如果臨時表不存在，在生成執(zhí)行計劃的時候會產(chǎn)生錯誤。

2) 一旦建立表變量后就無法對其進行DDL語句操作。因此如果需要為表建立索引或者加一列，你需要臨時表。

3) 表變量不能使用select …into語句，而臨時表可以。

4) 在SQL Server 2008中，你可以將表變量作為參數(shù)傳入存儲過程。但是臨時表不行。在SQL Server 2000和2005中表變量也不行。

5) 作用域:表變量僅僅在當前的批處理中有效,并且對任何在其中嵌套的存儲過程等不可見。局部臨時表只在當前會話中有效，這也包括嵌套的存儲過程。但對父存儲過程不可見。全局臨時表可以在任何會話中可見，但是會隨著創(chuàng)建其的會話終止而DROP，其它會話這時就不能再引用全局臨時表。

6) 排序規(guī)則：表變量使用當前數(shù)據(jù)庫的排序規(guī)則，臨時表使用TempDb的排序規(guī)則。如果它們不兼容，你還需要在查詢或者表定義中進行指定。

7) 你如果希望在動態(tài)SQL中使用表變量，你必須在動態(tài)SQL中定義表變量。而臨時表可以提前定義，在動態(tài)SQL中進行引用。

四、如何選擇

微軟推薦使用表變量，如果表中的行數(shù)非常小，則使用表變量。很多”網(wǎng)絡專家”會告訴你100是一個分界線,因為這是統(tǒng)計信息創(chuàng)建查詢計劃效率高低的開始。但是我還是希望告訴你針對你的特定需求對臨時表和表變量進行測試。很多人在自定義函數(shù)中使用表變量，如果你需要在表變量中使用主鍵和唯一索引，你會發(fā)現(xiàn)包含數(shù)千行的表變量也依然性能卓越。但如果你需要將表變量和其它表進行join,你會發(fā)現(xiàn)由于不精準的執(zhí)行計劃，性能往往會非常差。

為了證明這點，請看本文的附件。附件中代碼創(chuàng)建了表變量和臨時表.并裝入了AdventureWorks數(shù)據(jù)庫的Sales.SalesOrderDetail表。為了得到足夠的測試數(shù)據(jù)，我將這個表中的數(shù)據(jù)插入了10遍。然后以ModifiedDate 列作為條件將臨時表和表變量與原始的Sales.SalesOrderDetail表進行了Join操作，從統(tǒng)計信息來看IO差別顯著。從時間來看表變量做join花了50多秒，而臨時表僅僅花了8秒。

如果你需要在表建立后對表進行DLL操作，那么選擇臨時表吧。

臨時表和表變量有很多類似的地方。所以有時候并沒有具體的細則規(guī)定如何選擇哪一個。對任何特定的情況，你都需要考慮其各自優(yōu)缺點并做一些性能測試。下面的表格會讓你比較其優(yōu)略有了更詳細的參考。