一、表變量

表變量在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語句創(chuàng)建的表有著相同的物理工程，但臨時表與正常的表不同之處有：

• 臨時表的名稱不能超過116個字符，這是由于數據庫引擎為了辨別不同會話建立不同的臨時表，所以會自動在臨時表的名字后附加一串。
• 局部臨時表(以"#"開頭命名的)作用域僅僅在當前的連接內，從在存儲過程中建立局部臨時表的角度來看，局部臨時表會在下列情況下被Drop：
  　　a、顯示調用Drop Table語句
  　　b、當局部臨時表在存儲過程內被創(chuàng)建時，存儲過程結束也就意味著局部臨時表被Drop。
  　　c、當前會話結束，在會話內創(chuàng)建的所有局部臨時表都會被Drop。
• 全局臨時表(以"##"開頭命名的)在所有的會話內可見，所以在創(chuàng)建全局臨時表之前首先檢查其是否存在，否則如果已經存在，你將會得到重復創(chuàng)建對象的錯誤。
  　　a、全局臨時表會在創(chuàng)建其的會話結束后被Drop，Drop后其他會話將不能對全局臨時表進行引用。
  　　b、引用是在語句級別進行
• 不能對臨時表進行分區(qū)。
• 不能對臨時表加外鍵約束。
• 臨時表內列的數據類型不能定義成沒有在TempDb中沒有定義自定義數據類型(自定義數據類型是數據庫級別的對象，而臨時表屬于TempDb)。
  由于TempDb在每次SQL Server重啟后會被自動創(chuàng)建，所以你必須使用startup stored procedure來為TempDb創(chuàng)建自定義數據類型。你也可以通過修改Model數據庫來達到這一目標。
• XML列不能定義成XML集合的形式，除非這個集合已經在TempDb中定義。

臨時表既可以通過Create Table語句創(chuàng)建，也可以通過"SELECT <select_list> INTO #table"語句創(chuàng)建。你還可以針對臨時表用"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.下一次第二個窗口引用時，將產生錯誤。

三、比較

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

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

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

到此這篇關于SQL Server中使用表變量和臨時表的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持腳本之家。

最新評論