SQLServer表變量和臨時(shí)表的區(qū)別是什么
SQL Server 表變量和臨時(shí)表的區(qū)別是什么，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。
成都創(chuàng)新互聯(lián)公司憑借專(zhuān)業(yè)的設(shè)計(jì)團(tuán)隊(duì)扎實(shí)的技術(shù)支持、優(yōu)質(zhì)高效的服務(wù)意識(shí)和豐厚的資源優(yōu)勢(shì)，提供專(zhuān)業(yè)的網(wǎng)站策劃、網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)、網(wǎng)站優(yōu)化、軟件開(kāi)發(fā)、網(wǎng)站改版等服務(wù)，在成都10余年的網(wǎng)站建設(shè)設(shè)計(jì)經(jīng)驗(yàn)，為成都數(shù)千家中小型企業(yè)策劃設(shè)計(jì)了網(wǎng)站。
一、表變量
表變量在SQL Server 2000中首次被引入。表變量的具體定義包括列定義，列名，數(shù)據(jù)類(lèi)型和約束。而在表變量中可以使用的約束包括主鍵約束，唯一約束，NULL約束和CHECK約束(外鍵約束不能在表變量中使用)。定義表變量的語(yǔ)句是和正常使用Create Table定義表語(yǔ)句的子集。只是表變量通過(guò)DECLARE @local_variable語(yǔ)句進(jìn)行定義。
表變量的特征：1.表變量擁有特定作用域(在當(dāng)前批處理語(yǔ)句中，但不在任何當(dāng)前批處理語(yǔ)句調(diào)用的存儲(chǔ)過(guò)程和函數(shù)中)，表變量在批處理結(jié)束后自動(dòng)被清除。2.表變量較臨時(shí)表產(chǎn)生更少的存儲(chǔ)過(guò)程重編譯。3.針對(duì)表變量的事務(wù)僅僅在更新數(shù)據(jù)時(shí)生效，所以鎖和日志產(chǎn)生的數(shù)量會(huì)更少。4.由于表變量的作用域如此之小，而且不屬于數(shù)據(jù)庫(kù)的持久部分，所以事務(wù)回滾不會(huì)影響表變量。
表變量可以在其作用域內(nèi)像正常的表一樣使用。更確切的說(shuō)，表變量可以被當(dāng)成正常的表或者表表達(dá)式一樣在SELECT,DELETE,UPDATE,INSERT語(yǔ)句中使用，但是表變量不能在類(lèi)似"SELECT select_list INTO table_variable"這樣的語(yǔ)句中使用。而在SQL Server2000中，表變量也不能用于INSERT INTO table_variable EXEC stored_procedure這樣的語(yǔ)句中。
表變量不能做如下事情:1.雖然表變量是一個(gè)變量，但是其不能賦值給另一個(gè)變量。2.check約束，默認(rèn)值和計(jì)算列不能引用自定義函數(shù)。3.不能為約束命名。4.不能Truncate表變量。5.不能向標(biāo)識(shí)列中插入顯式值(也就是說(shuō)表變量不支持SET IDENTITY_INSERT ON)
下面來(lái)玩玩表變量吧。
定義一個(gè)表變量，插入一條數(shù)據(jù)，然后查詢：
DECLARE @tb1 Table ( Id int, Name varchar(20), Age int ) INSERT INTO @tb1 VALUES(1,'劉備',22) SELECT * FROM @tb1
輸出結(jié)果如下：
再來(lái)試試一些不符合要求的情況，例如添加表變量后，添加約束，并對(duì)約束命名：
再來(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)查看上面的要求。
二、臨時(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í)表與正常的表不同之處有：
1、臨時(shí)表的名稱不能超過(guò)116個(gè)字符，這是由于數(shù)據(jù)庫(kù)引擎為了辨別不同會(huì)話建立不同的臨時(shí)表，所以會(huì)自動(dòng)在臨時(shí)表的名字后附加一串。
2、局部臨時(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?！ 、當(dāng)前會(huì)話結(jié)束，在會(huì)話內(nèi)創(chuàng)建的所有局部臨時(shí)表都會(huì)被Drop。
3、全局臨時(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)行引用?！ 、引用是在語(yǔ)句級(jí)別進(jìn)行，如: 1.新建查詢窗口，運(yùn)行語(yǔ)句：
復(fù)制代碼 代碼如下: 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ò)誤。
4、不能對(duì)臨時(shí)表進(jìn)行分區(qū)。5、不能對(duì)臨時(shí)表加外鍵約束。6、臨時(shí)表內(nèi)列的數(shù)據(jù)類(lèi)型不能定義成沒(méi)有在TempDb中沒(méi)有定義自定義數(shù)據(jù)類(lèi)型(自定義數(shù)據(jù)類(lèi)型是數(shù)據(jù)庫(kù)級(jí)別的對(duì)象，而臨時(shí)表屬于TempDb)。由于TempDb在每次SQL Server重啟后會(huì)被自動(dòng)創(chuàng)建，所以你必須使用startup stored procedure來(lái)為T(mén)empDb創(chuàng)建自定義數(shù)據(jù)類(lèi)型。你也可以通過(guò)修改Model數(shù)據(jù)庫(kù)來(lái)達(dá)到這一目標(biāo)。7、XML列不能定義成XML集合的形式，除非這個(gè)集合已經(jīng)在TempDb中定義。臨時(shí)表既可以通過(guò)Create Table語(yǔ)句創(chuàng)建，也可以通過(guò)"SELECT
三、誤區(qū)
誤區(qū)1.表變量?jī)H僅在內(nèi)存中。
誤區(qū)2.臨時(shí)表僅僅存儲(chǔ)在物理介質(zhì)中。
這兩種觀點(diǎn)都是錯(cuò)誤的，只有內(nèi)存足夠，表變量和臨時(shí)表都會(huì)在內(nèi)存中創(chuàng)建和處理。他們也同樣可以在任何時(shí)間被存入磁盤(pán)?！ ∽⒁獗碜兞康拿质窍到y(tǒng)分配的，表變量的第一個(gè)字符”@”并不是一個(gè)字母，所以它并不是一個(gè)有效的變量名。系統(tǒng)會(huì)在TempDb中為表變量創(chuàng)建一個(gè)系統(tǒng)分配的名稱，所以任何在sysobjects或sys.tables查找表變量的方法都會(huì)失敗。
正確的方法應(yīng)該是我前面例子中的方法，我看到很多人使用如下查詢查表變量：
select * from sysobjects where name like'#tempTables%'
上述代碼看上去貌似很好用，但會(huì)產(chǎn)生多用戶的問(wèn)題。你建立兩個(gè)連接，在第一個(gè)連接中創(chuàng)建臨時(shí)表，在第二個(gè)窗口中運(yùn)行上面的語(yǔ)句能看到第一個(gè)連接創(chuàng)建的臨時(shí)表，如果你在第二個(gè)連接中嘗試操作這個(gè)臨時(shí)表，那么可能會(huì)產(chǎn)生錯(cuò)誤，因?yàn)檫@個(gè)臨時(shí)表不屬于你的會(huì)話。
誤區(qū)3.表變量不能擁有索引。
這個(gè)誤區(qū)也同樣錯(cuò)誤。雖然一旦你創(chuàng)建一個(gè)表變量之后，就不能對(duì)其進(jìn)行DDL語(yǔ)句了，這包括Create Index語(yǔ)句。然而你可以在表變量定義的時(shí)候?yàn)槠鋭?chuàng)建索引)比如如下語(yǔ)句。
declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)
這個(gè)語(yǔ)句將會(huì)創(chuàng)建一個(gè)擁有聚集索引的表變量。由于主鍵有了對(duì)應(yīng)的聚集索引，所以一個(gè)系統(tǒng)命名的索引將會(huì)被創(chuàng)建在RowID列上。
下面的例子演示你可以在一個(gè)表變量的列上創(chuàng)建唯一約束以及如何建立復(fù)合索引。
declare @temp TABLE ( RowID int NOT NULL, ColA int NOT NULL, ColB char(1)UNIQUE, PRIMARY KEY CLUSTERED(RowID, ColA))
1) SQL 并不能為表變量建立統(tǒng)計(jì)信息，就像其能為臨時(shí)表建立統(tǒng)計(jì)信息一樣。這意味著對(duì)于表變量，執(zhí)行引擎認(rèn)為其只有1行，這也意味著針對(duì)表變量的執(zhí)行計(jì)劃并不是最優(yōu)。雖然估計(jì)的執(zhí)行計(jì)劃對(duì)于表變量和臨時(shí)表都為1，但是實(shí)際的執(zhí)行計(jì)劃對(duì)于臨時(shí)表會(huì)根據(jù)每次存儲(chǔ)過(guò)程的重編譯而改變。如果臨時(shí)表不存在，在生成執(zhí)行計(jì)劃的時(shí)候會(huì)產(chǎn)生錯(cuò)誤。
2) 一旦建立表變量后就無(wú)法對(duì)其進(jìn)行DDL語(yǔ)句操作。因此如果需要為表建立索引或者加一列，你需要臨時(shí)表。
3) 表變量不能使用select …into語(yǔ)句，而臨時(shí)表可以。
4) 在SQL Server 2008中，你可以將表變量作為參數(shù)傳入存儲(chǔ)過(guò)程。但是臨時(shí)表不行。在SQL Server 2000和2005中表變量也不行。
5) 作用域:表變量?jī)H僅在當(dāng)前的批處理中有效,并且對(duì)任何在其中嵌套的存儲(chǔ)過(guò)程等不可見(jiàn)。局部臨時(shí)表只在當(dāng)前會(huì)話中有效，這也包括嵌套的存儲(chǔ)過(guò)程。但對(duì)父存儲(chǔ)過(guò)程不可見(jiàn)。全局臨時(shí)表可以在任何會(huì)話中可見(jiàn)，但是會(huì)隨著創(chuàng)建其的會(huì)話終止而DROP，其它會(huì)話這時(shí)就不能再引用全局臨時(shí)表。
6) 排序規(guī)則：表變量使用當(dāng)前數(shù)據(jù)庫(kù)的排序規(guī)則，臨時(shí)表使用TempDb的排序規(guī)則。如果它們不兼容，你還需要在查詢或者表定義中進(jìn)行指定。
7) 你如果希望在動(dòng)態(tài)SQL中使用表變量，你必須在動(dòng)態(tài)SQL中定義表變量。而臨時(shí)表可以提前定義，在動(dòng)態(tài)SQL中進(jìn)行引用。
四、如何選擇
微軟推薦使用表變量，如果表中的行數(shù)非常小，則使用表變量。很多”網(wǎng)絡(luò)專(zhuān)家”會(huì)告訴你100是一個(gè)分界線,因?yàn)檫@是統(tǒng)計(jì)信息創(chuàng)建查詢計(jì)劃效率高低的開(kāi)始。但是我還是希望告訴你針對(duì)你的特定需求對(duì)臨時(shí)表和表變量進(jìn)行測(cè)試。很多人在自定義函數(shù)中使用表變量，如果你需要在表變量中使用主鍵和唯一索引，你會(huì)發(fā)現(xiàn)包含數(shù)千行的表變量也依然性能卓越。但如果你需要將表變量和其它表進(jìn)行join,你會(huì)發(fā)現(xiàn)由于不精準(zhǔn)的執(zhí)行計(jì)劃，性能往往會(huì)非常差。
為了證明這點(diǎn)，請(qǐng)看本文的附件。附件中代碼創(chuàng)建了表變量和臨時(shí)表.并裝入了AdventureWorks數(shù)據(jù)庫(kù)的Sales.SalesOrderDetail表。為了得到足夠的測(cè)試數(shù)據(jù)，我將這個(gè)表中的數(shù)據(jù)插入了10遍。然后以ModifiedDate 列作為條件將臨時(shí)表和表變量與原始的Sales.SalesOrderDetail表進(jìn)行了Join操作，從統(tǒng)計(jì)信息來(lái)看IO差別顯著。從時(shí)間來(lái)看表變量做join花了50多秒，而臨時(shí)表僅僅花了8秒。
如果你需要在表建立后對(duì)表進(jìn)行DLL操作，那么選擇臨時(shí)表吧。
臨時(shí)表和表變量有很多類(lèi)似的地方。所以有時(shí)候并沒(méi)有具體的細(xì)則規(guī)定如何選擇哪一個(gè)。對(duì)任何特定的情況，你都需要考慮其各自優(yōu)缺點(diǎn)并做一些性能測(cè)試。下面的表格會(huì)讓你比較其優(yōu)略有了更詳細(xì)的參考。
特性 表變量 臨時(shí)表 作用域 當(dāng)前批處理 當(dāng)前會(huì)話，嵌套存儲(chǔ)過(guò)程，全局：所有會(huì)話 使用場(chǎng)景 自定義函數(shù)，存儲(chǔ)過(guò)程，批處理 自定義函數(shù)，存儲(chǔ)過(guò)程，批處理 創(chuàng)建方式 DECLARE statement only.只能通過(guò)DECLEARE語(yǔ)句創(chuàng)建
CREATE TABLE 語(yǔ)句
SELECT INTO 語(yǔ)句.
表名長(zhǎng)度 最多128字節(jié) 最多116字節(jié) 列類(lèi)型
可以使用自定義數(shù)據(jù)類(lèi)型
可以使用XML集合
自定義數(shù)據(jù)類(lèi)型和XML集合必須在TempDb內(nèi)定義 Collation 字符串排序規(guī)則繼承自當(dāng)前數(shù)據(jù)庫(kù) 字符串排序規(guī)則繼承自TempDb數(shù)據(jù)庫(kù) 索引 索引必須在表定義時(shí)建立 索引可以在表創(chuàng)建后建立 約束 PRIMARY KEY, UNIQUE, NULL, CHECK約束可以使用，但必須在表建立時(shí)聲明 PRIMARY KEY, UNIQUE, NULL, CHECK. 約束可以使用，可以在任何時(shí)后添加，但不能有外鍵約束 表建立后使用DDL (索引，列) 不允許 允許. 數(shù)據(jù)插入方式 INSERT 語(yǔ)句 (SQL 2000: 不能使用INSERT/EXEC).
INSERT 語(yǔ)句, 包括 INSERT/EXEC.
SELECT INTO 語(yǔ)句.
Insert explicit values into identity columns (SET IDENTITY_INSERT). 不支持SET IDENTITY_INSERT語(yǔ)句 支持SET IDENTITY_INSERT語(yǔ)句 Truncate table 不允許 允許 析構(gòu)方式 批處理結(jié)束后自動(dòng)析構(gòu) 顯式調(diào)用 DROP TABLE 語(yǔ)句. 當(dāng)前會(huì)話結(jié)束自動(dòng)析構(gòu) (全局臨時(shí)表: 還包括當(dāng)其它會(huì)話語(yǔ)句不在引用表.) 事務(wù) 只會(huì)在更新表的時(shí)候有事務(wù)，持續(xù)時(shí)間比臨時(shí)表短 正常的事務(wù)長(zhǎng)度，比表變量長(zhǎng) 存儲(chǔ)過(guò)程重編譯 否 會(huì)導(dǎo)致重編譯 回滾 不會(huì)被回滾影響 會(huì)被回滾影響 統(tǒng)計(jì)數(shù)據(jù) 不創(chuàng)建統(tǒng)計(jì)數(shù)據(jù)，所以所有的估計(jì)行數(shù)都為1,所以生成執(zhí)行計(jì)劃會(huì)不精準(zhǔn) 創(chuàng)建統(tǒng)計(jì)數(shù)據(jù)，通過(guò)實(shí)際的行數(shù)生成執(zhí)行計(jì)劃。 作為參數(shù)傳入存儲(chǔ)過(guò)程 僅僅在SQL Server2008, 并且必須預(yù)定義 user-defined table type. 不允許 顯式命名對(duì)象 (索引, 約束). 不允許 允許，但是要注意多用戶的問(wèn)題 動(dòng)態(tài)SQL 必須在動(dòng)態(tài)SQL中定義表變量 可以在調(diào)用動(dòng)態(tài)SQL之前定義臨時(shí)表
看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對(duì)創(chuàng)新互聯(lián)的支持。
文章標(biāo)題：SQLServer表變量和臨時(shí)表的區(qū)別是什么
文章分享：http://www.jiaotiyi.com/article/gocssi.html