一、表變量

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

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

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

1、定義和使用表變量

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

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

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

SELECT * FROM @tb1

輸出結(jié)果如下：

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

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

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

　　SQL Server提示錯(cuò)誤如下：

SQL Server不支持定義表變量時(shí)對(duì)Constraint命名，也不支持定義表變量后，對(duì)其建Constraint。

更多的不允許，請(qǐng)查看下面的要求。

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

3、表變量的特征：

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

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

二、臨時(shí)表

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

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

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

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

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

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

如:

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

CREATE TABLE ##temp(RowID int)
INSERT INTO ##temp VALUES(3)

2.再次新建一個(gè)查詢窗口，每5秒引用一次全局臨時(shí)表

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

3.回到第一個(gè)窗口，關(guān)閉窗口。
4.下一次第二個(gè)窗口引用時(shí)，將產(chǎn)生錯(cuò)誤。

三、比較

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

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

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

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

最新評(píng)論