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

一、表變量

　　表變量在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之前定義臨時表