一、表變量

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

表變量的具體定義包括列定義，列名，數(shù)據(jù)類型和約束。而在表變量中可以使用的約束包括主鍵約束，唯一約束，NULL約束和CHECK約束(外鍵約束不能在表變量中使用)。

定義表變量的語句是和正常使用Create Table定義表語句的子集。只是表變量通過DECLARE @local_variable語句進(jìn)行定義。

1、定義和使用表變量

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

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

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

SELECT * FROM @tb1

輸出結(jié)果如下：

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

來試試一些不符合要求的情況，例如添加表變量后，添加約束，并對約束命名：

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

　　SQL Server提示錯誤如下：

SQL Server不支持定義表變量時對Constraint命名，也不支持定義表變量后，對其建Constraint。

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

• 雖然表變量是一個變量，但是其不能賦值給另一個變量。
• check約束，默認(rèn)值和計算列不能引用自定義函數(shù)。
• 不能為約束命名。
• 不能Truncate表變量。
• 不能向標(biāo)識列中插入顯式值(也就是說表變量不支持SET IDENTITY_INSERT ON)

3、表變量的特征：

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

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

二、臨時表

在深入臨時表之前，我們要了解一下會話(Session)，一個會話僅僅是一個客戶端到數(shù)據(jù)引擎的連接。在SQL Server Management Studio中，每一個查詢窗口都會和數(shù)據(jù)庫引擎建立連接。

一個應(yīng)用程序可以和數(shù)據(jù)庫建立一個或多個連接，除此之外，應(yīng)用程序還可能建立連接后一直不釋放知道應(yīng)用程序結(jié)束，也可能使用完釋放連接需要時建立連接。

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

• 臨時表的名稱不能超過116個字符，這是由于數(shù)據(jù)庫引擎為了辨別不同會話建立不同的臨時表，所以會自動在臨時表的名字后附加一串。
• 局部臨時表(以"#"開頭命名的)作用域僅僅在當(dāng)前的連接內(nèi)，從在存儲過程中建立局部臨時表的角度來看，局部臨時表會在下列情況下被Drop：
  　　a、顯示調(diào)用Drop Table語句
  　　b、當(dāng)局部臨時表在存儲過程內(nèi)被創(chuàng)建時，存儲過程結(jié)束也就意味著局部臨時表被Drop。
  　　c、當(dāng)前會話結(jié)束，在會話內(nèi)創(chuàng)建的所有局部臨時表都會被Drop。
• 全局臨時表(以"##"開頭命名的)在所有的會話內(nèi)可見，所以在創(chuàng)建全局臨時表之前首先檢查其是否存在，否則如果已經(jīng)存在，你將會得到重復(fù)創(chuàng)建對象的錯誤。
  　　a、全局臨時表會在創(chuàng)建其的會話結(jié)束后被Drop，Drop后其他會話將不能對全局臨時表進(jìn)行引用。
  　　b、引用是在語句級別進(jìn)行
• 不能對臨時表進(jìn)行分區(qū)。
• 不能對臨時表加外鍵約束。
• 臨時表內(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ù)庫來達(dá)到這一目標(biāo)。
• XML列不能定義成XML集合的形式，除非這個集合已經(jīng)在TempDb中定義。

臨時表既可以通過Create Table語句創(chuàng)建，也可以通過"SELECT <select_list> INTO #table"語句創(chuàng)建。你還可以針對臨時表用"INSERT INTO #table EXEC stored_procedure"這樣的語句。
臨時表可以擁有命名的約束和索引。但是，當(dāng)兩個用戶在同一時間調(diào)用同一存儲過程時，將會產(chǎn)生”There is already an object named ‘<objectname>’ in the database”這樣的錯誤。所以最好的做法是不用為建立的對象進(jìn)行命名，而使用系統(tǒng)分配的在TempDb中唯一的。

1、全局臨時表引用是在語句級別進(jìn)行

如:

1.新建查詢窗口，運(yùn)行語句：

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.回到第一個窗口，關(guān)閉窗口。
4.下一次第二個窗口引用時，將產(chǎn)生錯誤。

三、比較

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

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

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

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

最新評論