一、表變量

　　表變量在SQL Server 2000中首次被引入。表變量的具體定義包括列定義，列名，數(shù)據(jù)類型和約束。而在表變量中可以使用的約束包括主鍵約束，唯一約束，NULL約束和CHECK約束(外鍵約束不能在表變量中使用)。定義表變量的語句是正常使用Create Table定義表語句的子集。只是表變量通過DECLARE @local_variable語句進(jìn)行定義。

　　表變量的特征：

表變量擁有特定作用域(在當(dāng)前批處理語句中，但不在任何當(dāng)前批處理語句調(diào)用的存儲過程和函數(shù)中)，表變量在批處理結(jié)束后自動被清除。

表變量較臨時(shí)表產(chǎn)生更少的存儲過程重編譯。

針對表變量的事務(wù)僅僅在更新數(shù)據(jù)時(shí)生效，所以鎖和日志產(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這樣的語句中。

　　表變量不能做如下事情:

雖然表變量是一個(gè)變量，但是其不能賦值給另一個(gè)變量。

check約束，默認(rèn)值和計(jì)算列不能引用自定義函數(shù)。

不能為約束命名。

不能Truncate表變量。

不能向標(biāo)識列中插入顯式值(也就是說表變量不支持SET IDENTITY_INSERT ON)

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

DECLARE @tb1 Table(Id int,Name varchar(20),Age int)INSERT INTO @tb1 VALUES(1,'劉備',22)SELECT * FROM @tb1

　　輸出結(jié)果如下：

　　

二、臨時(shí)表

　　會話(Session)，一個(gè)會話僅僅是一個(gè)客戶端到數(shù)據(jù)引擎的連接。在SQL Server Management Studio中，每一個(gè)查詢窗口都會和數(shù)據(jù)庫引擎建立連接。一個(gè)應(yīng)用程序可以和數(shù)據(jù)庫建立一個(gè)或多個(gè)連接，除此之外，應(yīng)用程序還可能建立連接后一直不釋放直到應(yīng)用程序結(jié)束，也可能使用完釋放連接需要時(shí)建立連接。

　　臨時(shí)表是建立在系統(tǒng)臨時(shí)文件夾中的表，如果使用得當(dāng)，完全可以像普通表一樣進(jìn)行各種操作。

????? 臨時(shí)表分為兩類：本地臨時(shí)表 和 全局臨時(shí)表：

　　[1] 本地臨時(shí)表僅在當(dāng)前會話中可見，本地臨時(shí)表的名稱前面有一個(gè)編號符 (#)；

　　[2] 全局臨時(shí)表在所有會話中都可見，全局臨時(shí)表的名稱前面有兩個(gè)編號符 (##)。

????? 如果本地臨時(shí)表由存儲過程創(chuàng)建或由多個(gè)用戶同時(shí)執(zhí)行的應(yīng)用程序創(chuàng)建，則 SQL Server 必須能夠區(qū)分由不同用戶創(chuàng)建的表。為此，SQL Server 在內(nèi)部為每個(gè)本地臨時(shí)表的表名追加一個(gè)數(shù)字后綴。存儲在 tempdb 數(shù)據(jù)庫的 sysobjects 表中的臨時(shí)表，其全名由 Create Table 語句中指定的表名和系統(tǒng)生成的數(shù)字后綴組成。為了允許追加后綴，為本地臨時(shí)表指定的表名 table_name 不能超過 116 個(gè)字符。

????? 除非使用 Drop Table語句顯式除去臨時(shí)表，否則臨時(shí)表將在退出其作用域時(shí)由系統(tǒng)自動刪除。

????? 當(dāng)存儲過程完成時(shí)，將自動除去在存儲過程中創(chuàng)建的本地臨時(shí)表。由創(chuàng)建表的存儲過程執(zhí)行的所有嵌套存儲過程都可以引用此表。但調(diào)用創(chuàng)建此表的存儲過程的進(jìn)程無法引用此表。

????? 所有其它本地臨時(shí)表在當(dāng)前會話結(jié)束時(shí)自動刪除。

????? 全局臨時(shí)表在創(chuàng)建此表的會話結(jié)束且其它任務(wù)停止對其引用時(shí)自動除去。任務(wù)與表之間的關(guān)聯(lián)只在單個(gè) Transact-SQL 語句的生存周期內(nèi)保持。換言之，當(dāng)創(chuàng)建全局臨時(shí)表的會話結(jié)束時(shí)，最后一條引用此表的 Transact-SQL 語句完成后，將自動除去此表。?
????? 在存儲過程或觸發(fā)器中創(chuàng)建的本地臨時(shí)表與在調(diào)用存儲過程或觸發(fā)器之前創(chuàng)建的同名臨時(shí)表不同。如果查詢引用臨時(shí)表，而同時(shí)有兩個(gè)同名的臨時(shí)表，則不定義針對哪個(gè)表解析該查詢。嵌套存儲過程同樣可以創(chuàng)建與調(diào)用它的存儲過程所創(chuàng)建的臨時(shí)表同名的臨時(shí)表。嵌套存儲過程中對表名的所有引用都被解釋為是針對該嵌套過程所創(chuàng)建的表。

????? 當(dāng)創(chuàng)建本地或全局臨時(shí)表時(shí)，Create Table 語法支持除 Foreign Key 約束以外的其它所有約束定義。如果在臨時(shí)表中指定 Foreign Key 約束，該語句將返回警告信息，指出此約束已被忽略，表仍會創(chuàng)建，但不具有 Foreign Key約束。在Foreign Key約束中不能引用臨時(shí)表。

????? 考慮使用表變量而不使用臨時(shí)表。當(dāng)需要在臨時(shí)表上顯式地創(chuàng)建索引時(shí)，或多個(gè)存儲過程或函數(shù)需要使用表值時(shí)，臨時(shí)表很有用。通常，表變量提供更有效的查詢處理。

　　1、創(chuàng)建臨時(shí)表

--方法一：直接創(chuàng)建create table #臨時(shí)表名( 字段1 約束條件,字段2 約束條件,.....)create table ##臨時(shí)表名( 字段1 約束條件,字段2 約束條件,.....)--方法二：插入特定數(shù)據(jù)創(chuàng)建select * into #臨時(shí)表名 from 表名;select * into ##臨時(shí)表名 from 表名;

　　2、臨時(shí)表中插入數(shù)據(jù)

Create Table #臨時(shí)表名 (Number int Primary Key) Insert Into #臨時(shí)表名 Values (1)

　　3、查詢臨時(shí)表
????? select * from #臨時(shí)表名;
??????select * from ##臨時(shí)表名;

　　4、刪除臨時(shí)表
????? drop table #臨時(shí)表名;
??????drop table ##臨時(shí)表名;

　　5、清空臨時(shí)表的所有數(shù)據(jù)和約束

????? truncate table #臨時(shí)表名;
????? truncate table ##臨時(shí)表名;

?

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

　　1、臨時(shí)表的名稱不能超過116個(gè)字符，這是由于數(shù)據(jù)庫引擎為了辨別不同會話建立不同的臨時(shí)表，所以會自動在臨時(shí)表的名字后附加一串。

　　2、局部臨時(shí)表(以"#"開頭命名的)作用域僅僅在當(dāng)前的連接內(nèi)，從在存儲過程中建立局部臨時(shí)表的角度來看，局部臨時(shí)表會在下列情況下被Drop：
　　[1] 顯示調(diào)用Drop Table語句
　　[2] 當(dāng)局部臨時(shí)表在存儲過程內(nèi)被創(chuàng)建時(shí)，存儲過程結(jié)束也就意味著局部臨時(shí)表被Drop。
　　[3] 當(dāng)前會話結(jié)束，在會話內(nèi)創(chuàng)建的所有局部臨時(shí)表都會被Drop。

　　3、全局臨時(shí)表(以"##"開頭命名的)在所有的會話內(nèi)可見，所以在創(chuàng)建全局臨時(shí)表之前首先檢查其是否存在，否則如果已經(jīng)存在，將會返回重復(fù)創(chuàng)建對象的錯(cuò)誤。
　　(1) 全局臨時(shí)表會在創(chuàng)建其的會話結(jié)束后被Drop，Drop后其他會話將不能對全局臨時(shí)表進(jìn)行引用。
　　(2) 引用是在語句級別進(jìn)行，如:
　　[1] 新建查詢窗口，運(yùn)行語句：

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

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

　　While 1=1 BEGINSELECT * FROM ##tempWAITFOR delay '00:00:05'END

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

?　　　　　　

　　4、不能對臨時(shí)表進(jìn)行分區(qū)。

　　5、不能對臨時(shí)表加外鍵約束。

　　6、臨時(shí)表內(nèi)列的數(shù)據(jù)類型不能定義成在TempDb中沒有定義的自定義數(shù)據(jù)類型(自定義數(shù)據(jù)類型是數(shù)據(jù)庫級別的對象，而臨時(shí)表屬于TempDb)。由于TempDb在每次SQL Server重啟后會被自動創(chuàng)建，所以你必須使用startup stored procedure來為TempDb創(chuàng)建自定義數(shù)據(jù)類型。你也可以通過修改Model數(shù)據(jù)庫來達(dá)到這一目標(biāo)。

　　7、XML列不能定義成XML集合的形式，除非這個(gè)集合已經(jīng)在TempDb中定義。

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

三、誤區(qū)

　　1、表變量僅僅在內(nèi)存中。

　　2、臨時(shí)表僅僅存儲在物理介質(zhì)中。

　　以上兩種觀點(diǎn)都是錯(cuò)誤的，只有內(nèi)存足夠，表變量和臨時(shí)表都會在內(nèi)存中創(chuàng)建和處理。也同樣可以在任何時(shí)間被存入磁盤。

　　注意表變量的名字是系統(tǒng)分配的，表變量的第一個(gè)字符”@”并不是一個(gè)字母，所以它并不是一個(gè)有效的變量名。系統(tǒng)會在TempDb中為表變量創(chuàng)建一個(gè)系統(tǒng)分配的名稱，所以任何在sysobjects或sys.tables查找表變量的方法都會失敗。

　　正確的方法應(yīng)該是我前面例子中的方法，我看到很多人使用如下查詢查表變量：

　　select * from sysobjects where name like'#tempTables%'

　　上述代碼看上去貌似很好用，但會產(chǎn)生多用戶的問題。你建立兩個(gè)連接，在第一個(gè)連接中創(chuàng)建臨時(shí)表，在第二個(gè)窗口中運(yùn)行上面的語句能看到第一個(gè)連接創(chuàng)建的臨時(shí)表，如果你在第二個(gè)連接中嘗試操作這個(gè)臨時(shí)表，那么可能會產(chǎn)生錯(cuò)誤，因?yàn)檫@個(gè)臨時(shí)表不屬于你的會話。

　　3、表變量不能擁有索引。

　　這個(gè)誤區(qū)也同樣錯(cuò)誤。雖然一旦你創(chuàng)建一個(gè)表變量之后，就不能對其進(jìn)行DDL語句了，這包括Create Index語句。然而你可以在表變量定義的時(shí)候?yàn)槠鋭?chuàng)建索引)比如如下語句。

　　declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

　　這個(gè)語句將會創(chuàng)建一個(gè)擁有聚集索引的表變量。由于主鍵有了對應(yīng)的聚集索引，所以一個(gè)系統(tǒng)命名的索引將會被創(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ì)信息一樣。這意味著對于表變量，執(zhí)行引擎認(rèn)為其只有1行，這也意味著針對表變量的執(zhí)行計(jì)劃并不是最優(yōu)。雖然估計(jì)的執(zhí)行計(jì)劃對于表變量和臨時(shí)表都為1，但是實(shí)際的執(zhí)行計(jì)劃對于臨時(shí)表會根據(jù)每次存儲過程的重編譯而改變。如果臨時(shí)表不存在，在生成執(zhí)行計(jì)劃的時(shí)候會產(chǎn)生錯(cuò)誤。

　　2) 一旦建立表變量后就無法對其進(jìn)行DDL語句操作。因此如果需要為表建立索引或者加一列，你需要臨時(shí)表。

　　3) 表變量不能使用select …into語句，而臨時(shí)表可以。

　　4) 在SQL Server 2008中，你可以將表變量作為參數(shù)傳入存儲過程。但是臨時(shí)表不行。在SQL Server 2000和2005中表變量也不行。

　　5) 作用域:表變量僅僅在當(dāng)前的批處理中有效,并且對任何在其中嵌套的存儲過程等不可見。局部臨時(shí)表只在當(dāng)前會話中有效，這也包括嵌套的存儲過程。但對父存儲過程不可見。全局臨時(shí)表可以在任何會話中可見，但是會隨著創(chuàng)建其的會話終止而DROP，其它會話這時(shí)就不能再引用全局臨時(shí)表。

　　6) 排序規(guī)則：表變量使用當(dāng)前數(shù)據(jù)庫的排序規(guī)則，臨時(shí)表使用TempDb的排序規(guī)則。如果它們不兼容，你還需要在查詢或者表定義中進(jìn)行指定。

　　7) 你如果希望在動態(tài)SQL中使用表變量，你必須在動態(tài)SQL中定義表變量。而臨時(shí)表可以提前定義，在動態(tài)SQL中進(jìn)行引用。

四、情景選擇

　　微軟推薦使用表變量，如果表中的行數(shù)非常小，則使用表變量。很多”網(wǎng)絡(luò)專家”會告訴你100是一個(gè)分界線,因?yàn)檫@是統(tǒng)計(jì)信息創(chuàng)建查詢計(jì)劃效率高低的開始。但是我還是希望告訴你針對你的特定需求對臨時(shí)表和表變量進(jìn)行測試。很多人在自定義函數(shù)中使用表變量，如果你需要在表變量中使用主鍵和唯一索引，你會發(fā)現(xiàn)包含數(shù)千行的表變量也依然性能卓越。但如果你需要將表變量和其它表進(jìn)行join,你會發(fā)現(xiàn)由于不精準(zhǔn)的執(zhí)行計(jì)劃，性能往往會非常差。

　　如果你需要在表建立后對表進(jìn)行DLL操作，那么選擇臨時(shí)表吧。

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

五、表變量和臨時(shí)表的對比

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

特性	表變量	臨時(shí)表
作用域	當(dāng)前批處理	當(dāng)前會話，嵌套存儲過程，全局：所有會話
使用場景	自定義函數(shù)，存儲過程，批處理	自定義函數(shù)，存儲過程，批處理
創(chuàng)建方式	DECLARE statement only.只能通過DECLEARE語句創(chuàng)建	CREATE TABLE 語句 SELECT INTO 語句.
表名長度	最多128字節(jié)	最多116字節(jié)
列類型	可以使用自定義數(shù)據(jù)類型 可以使用XML集合	自定義數(shù)據(jù)類型和XML集合必須在TempDb內(nèi)定義
Collation	字符串排序規(guī)則繼承自當(dāng)前數(shù)據(jù)庫	字符串排序規(guī)則繼承自TempDb數(shù)據(jù)庫
索引	索引必須在表定義時(shí)建立	索引可以在表創(chuàng)建后建立
約束	PRIMARY KEY, UNIQUE, NULL, CHECK約束可以使用，但必須在表建立時(shí)聲明	PRIMARY KEY, UNIQUE, NULL, CHECK. 約束可以使用，可以在任何時(shí)后添加，但不能有外鍵約束
表建立后使用DDL (索引，列)	不允許	允許.
數(shù)據(jù)插入方式	INSERT 語句 (SQL 2000: 不能使用INSERT/EXEC).	INSERT 語句, 包括 INSERT/EXEC. SELECT INTO 語句.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	不支持SET IDENTITY_INSERT語句	支持SET IDENTITY_INSERT語句
Truncate table	不允許	允許
析構(gòu)方式	批處理結(jié)束后自動析構(gòu)	顯式調(diào)用 DROP TABLE 語句.? 當(dāng)前會話結(jié)束自動析構(gòu) (全局臨時(shí)表: 還包括當(dāng)其它會話語句不在引用表.)
事務(wù)	只會在更新表的時(shí)候有事務(wù)，持續(xù)時(shí)間比臨時(shí)表短	正常的事務(wù)長度，比表變量長
存儲過程重編譯	否	會導(dǎo)致重編譯
回滾	不會被回滾影響	會被回滾影響
統(tǒng)計(jì)數(shù)據(jù)	不創(chuàng)建統(tǒng)計(jì)數(shù)據(jù)，所以所有的估計(jì)行數(shù)都為1,所以生成執(zhí)行計(jì)劃會不精準(zhǔn)	創(chuàng)建統(tǒng)計(jì)數(shù)據(jù)，通過實(shí)際的行數(shù)生成執(zhí)行計(jì)劃。
作為參數(shù)傳入存儲過程	僅僅在SQL Server2008, 并且必須預(yù)定義 user-defined table type.	不允許
顯式命名對象 (索引, 約束).	不允許	允許，但是要注意多用戶的問題
動態(tài)SQL	必須在動態(tài)SQL中定義表變量	可以在調(diào)用動態(tài)SQL之前定義臨時(shí)表

轉(zhuǎn)載于:https://www.cnblogs.com/xinaixia/p/5821548.html

總結(jié)

以上是生活随笔為你收集整理的T-SQL 之 表变量和临时表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。