SQL Server中使用表變數和臨時表

一、表變數

表變數在SQL Server 2000中首次被引入。

表變數的具體定義包括列定義，列名，資料型別和約束。而在表變數中可以使用的約束包括主鍵約束，唯一約束，NULL約束和CHECK約束(外來鍵約束不能在表變數中使用)。

定義表變數的語句是和正常使用Create Table定義表語句的子集。只是表變數通過DECLARE @local_variable語句進行定義。

1、定義和使用表變數

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

DECLARE @tb1 Table
　　(
 　　Id int,
 　　Name varchar(20),
 　　Age int
　　)

INSERT INTO @tb1 VALUES(1,'劉備',22)

SELECT * FROM @tb1

輸出結果如下：

2、表變數不能做如下事情:

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

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

　　SQL Server提示錯誤如下：

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

更多的不允許，請檢視下面的要求。

• 雖然表變數是一個變數，但是其不能賦值給另一個變數。
• check約束，預設值和計算列不能參照自定義函數。
• 不能為約束命名。
• 不能Truncate表變數。
• 不能向標識列中插入顯式值(也就是說表變數不支援SET IDENTITY_INSERT ON)

3、表變數的特徵：

• 表變數擁有特定作用域(在當前批次處理語句中，但不在任何當前批次處理語句呼叫的儲存過程和函數中)，表變數在批次處理結束後自動被清除。
• 表變數較臨時表產生更少的儲存過程重編譯。
• 針對表變數的事務僅僅在更新資料時生效，所以鎖和紀錄檔產生的數量會更少。
• 由於表變數的作用域如此之小，而且不屬於資料庫的持久部分，所以事務回滾不會影響表變數。

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

二、臨時表

在深入臨時表之前，我們要了解一下對談(Session)，一個對談僅僅是一個使用者端到資料引擎的連線。在SQL Server Management Studio中，每一個查詢視窗都會和資料庫引擎建立連線。

一個應用程式可以和資料庫建立一個或多個連線，除此之外，應用程式還可能建立連線後一直不釋放知道應用程式結束，也可能使用完釋放連線需要時建立連線。

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

• 臨時表的名稱不能超過116個字元，這是由於資料庫引擎為了辨別不同對談建立不同的臨時表，所以會自動在臨時表的名字後附加一串。
• 區域性臨時表(以"#"開頭命名的)作用域僅僅在當前的連線內，從在儲存過程中建立區域性臨時表的角度來看，區域性臨時表會在下列情況下被Drop：
  　　a、顯示呼叫Drop Table語句
  　　b、當區域性臨時表在儲存過程內被建立時，儲存過程結束也就意味著區域性臨時表被Drop。
  　　c、當前對談結束，在對談內建立的所有區域性臨時表都會被Drop。
• 全域性臨時表(以"##"開頭命名的)在所有的對談內可見，所以在建立全域性臨時表之前首先檢查其是否存在，否則如果已經存在，你將會得到重複建立物件的錯誤。
  　　a、全域性臨時表會在建立其的對談結束後被Drop，Drop後其他對談將不能對全域性臨時表進行參照。
  　　b、參照是在語句級別進行
• 不能對臨時表進行分割區。
• 不能對臨時表加外來鍵約束。
• 臨時表內列的資料型別不能定義成沒有在TempDb中沒有定義自定義資料型別(自定義資料型別是資料庫級別的物件，而臨時表屬於TempDb)。
  由於TempDb在每次SQL Server重啟後會被自動建立，所以你必須使用startup stored procedure來為TempDb建立自定義資料型別。你也可以通過修改Model資料庫來達到這一目標。
• 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、全域性臨時表參照是在語句級別進行

如:

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.下一次第二個視窗參照時，將產生錯誤。

三、比較

微軟推薦使用表變數，如果表中的行數非常小，則使用表變數。

臨時表和表變數有很多類似的地方。所以有時候並沒有具體的細則規定如何選擇哪一個。對任何特定的情況，你都需要考慮其各自優缺點並做一些效能測試。

下面的表格會讓你比較其優略有了更詳細的參考。

到此這篇關於SQL Server中使用表變數和臨時表的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。