sql: 臨時表與表變量的區別

1、表變量

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

　　表變量的特點：

1. 表變量具有特定作用域(在當前批處理語句中，但不在任何當前批處理語句調用的存儲進程和函數中)，表變量在批處理結束后自動被清除。
2. 表變量較臨時表產生更少的存儲進程重編譯。
3. 針對表變量的事務僅僅在更新數據時生效，所以鎖和日志產生的數量會更少。
4. 由于表變量的作用域如此之小，而且不屬于數據庫的持久部份，所以事務回滾不會影響表變量。

　　表變量可以在其作用域內像正常的表1樣使用。更確切的說，表變量可以被當做正常的表或表表達式1樣在SELECT,DELETE,UPDATE,INSERT語句中使用，但是表變量不能在類似"SELECT select_list INTO table_variable"這樣的語句中使用。而在SQL Server2000中，表變量也不能用于INSERT INTO table_variable EXEC stored_procedure這樣的語句中。

　　表變量不能做以下事情:

1. 雖然表變量是1個變量，但是其不能賦值給另外一個變量。
2. check束縛，默許值和計算列不能援用自定義函數。
3. 不能為束縛命名。
4. 不能Truncate表變量。
5. 不能向標識列中插入顯式值(也就是說表變量不支持SET IDENTITY_INSERT ON)

2、臨時表

　　在深入臨時表之前，我們要了解1下會話(Session)，1個會話僅僅是1個客戶端到數據引擎的連接。在SQL Server Management Studio中，每個查詢窗口都會和數據庫引擎建立連接。1個利用程序可以和數據庫建立1個或多個連接，除此以外，利用程序還可能建立連接后1直不釋放知道利用程序結束，也可能使用完釋放連接需要時建立連接。

　　臨時表和Create Table語句創建的表有著相同的物理工程，但臨時表與正常的表不同的地方有：

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

　　2、局部臨時表(以"#"開頭命名的)作用域僅僅在當前的連接內，從在存儲進程中建立局部臨時表的角度來看，局部臨時表會在以下情況下被Drop：
　　　　a、顯示調用Drop Table語句
　　　　b、當局部臨時表在存儲進程內被創建時，存儲進程結束也就意味著局部臨時表被Drop。
　　　　c、當前會話結束，在會話內創建的所有局部臨時表都會被Drop。

　　3、全局臨時表(以"##"開頭命名的)在所有的會話內可見，所以在創建全局臨時表之前首先檢查其是不是存在，否則如果已存在，你將會得到重復創建對象的毛病。
　　　　a、全局臨時表會在創建其的會話結束后被Drop，Drop后其他會話將不能對全局臨時表進行援用。
　　　　b、援用是在語句級別進行，如:
　　　　　　1.新建查詢窗口，運行語句：

　　　　　　2.再次新建1個查詢窗口，每5秒援用1次全局臨時表

　　　　　　3.回到第1個窗口，關閉窗口。
　　　　　　4.下1次第2個窗口援用時，將產生毛病。

　　4、不能對臨時表進行分區。

　　5、不能對臨時表加外鍵束縛。

　　6、臨時表內列的數據類型不能定義成沒有在TempDb中沒有定義自定義數據類型(自定義數據類型是數據庫級別的對象，而臨時表屬于TempDb)。由于TempDb在每次SQL Server重啟后會被自動創建，所以你必須使用startup stored procedure來為TempDb創建自定義數據類型。你也能夠通過修改Model數據庫來到達這1目標。

　　7、XML列不能定義成XML集合的情勢，除非這個集合已在TempDb中定義。

　　臨時表既可以通過Create Table語句創建，也能夠通過"SELECT <select_list> INTO #table"語句創建。你還可以針對臨時表用"INSERT INTO #table EXEC stored_procedure"這樣的語句。
　　臨時表可以具有命名的束縛和索引。但是，當兩個用戶在同1時間調用同1存儲進程時，將會產生”There is already an object named ‘<objectname>’ in the database”這樣的毛病。所以最好的做法是不用為建立的對象進行命名，而使用系統分配的在TempDb中唯1的。

3、誤區

　　誤區1.表變量僅僅在內存中。

　　誤區2.臨時表僅僅存儲在物理介質中。

　　這兩種觀點都是毛病的，只有內存足夠，表變量和臨時表都會在內存中創建和處理。他們也一樣可以在任什么時候間被存入磁盤。

　　注意表變量的名字是系統分配的，表變量的第1個字符”@”其實不是1個字母，所以它其實不是1個有效的變量名。系統會在TempDb中為表變量創建1個系統分配的名稱，所以任何在sysobjects或sys.tables查找表變量的方法都會失敗。

　　正確的方法應當是我前面例子中的方法，我看到很多人使用以下查詢查表變量：

　　上述代碼看上去貌似很好用，但會產生多用戶的問題。你建立兩個連接，在第1個連接中創建臨時表，在第2個窗口中運行上面的語句能看到第1個連接創建的臨時表，如果你在第2個連接中嘗試操作這個臨時表，那末可能會產生毛病，由于這個臨時表不屬于你的會話。

　　誤區3.表變量不能具有索引。

    這個誤區也一樣毛病。雖然1旦你創建1個表變量以后，就不能對其進行DDL語句了，這包括Create Index語句。但是你可以在表變量定義的時候為其創建索引)比如以下語句。

    這個語句將會創建1個具有聚集索引的表變量。由于主鍵有了對應的聚集索引，所以1個系統命名的索引將會被創建在RowID列上。

    下面的例子演示你可以在1個表變量的列上創建唯1束縛和如何建立復合索引。

　　1) SQL 其實不能為表變量建立統計信息，就像其能為臨時表建立統計信息1樣。這意味著對表變量，履行引擎認為其只有1行，這也意味著針對表變量的履行計劃其實不是最優。雖然估計的履行計劃對表變量和臨時表都為1，但是實際的履行計劃對臨時表會根據每次存儲進程的重編譯而改變。如果臨時表不存在，在生成履行計劃的時候會產生毛病。

　　2) 1旦建立表變量后就沒法對其進行DDL語句操作。因此如果需要為表建立索引或加1列，你需要臨時表。

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

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

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

　　6) 排序規則：表變量使用當前數據庫的排序規則，臨時表使用TempDb的排序規則。如果它們不兼容，你還需要在查詢或表定義中進行指定。

　　7) 你如果希望在動態SQL中使用表變量，你必須在動態SQL中定義表變量。而臨時表可以提早定義，在動態SQL中進行援用。

4、如何選擇

　　微軟推薦使用表變量，如果表中的行數非常小，則使用表變量。很多”網絡專家”會告知你100是1個分界限,由于這是統計信息創建查詢計劃效力高低的開始。但是我還是希望告知你針對你的特定需求對臨時表和表變量進行測試。很多人在自定義函數中使用表變量，如果你需要在表變量中使用主鍵和唯1索引，你會發現包括數千行的表變量也仍然性能出色。但如果你需要將表變量和其它表進行join,你會發現由于不精準的履行計劃，性能常常會非常差。

　　為了證明這點，請看本文的附件。附件中代碼創建了表變量和臨時表.并裝入了AdventureWorks數據庫的Sales.SalesOrderDetail表。為了得到足夠的測試數據，我將這個表中的數據插入了10遍。然后以ModifiedDate 列作為條件將臨時表和表變量與原始的Sales.SalesOrderDetail表進行了Join操作，從統計信息來看IO差別顯著。從時間來看表變量做join花了50多秒，而臨時表僅僅花了8秒。

　　如果你需要在表建立后對表進行DLL操作，那末選擇臨時表吧。

　　臨時表和表變量有很多類似的地方。所以有時候并沒有具體的細則規定如何選擇哪個。對任何特定的情況，你都需要斟酌其各自優缺點并做1些性能測試。下面的表格會讓你比較其優略有了更詳細的參考。

5、總結

出處:http://www.cnblogs.com/CareySon/archive/2012/06/11/TableVariableAndTempTable.html

公共表達式、臨時表、表變量之間的區分

表變量：
表變量必須顯示定義，不能使用SELECT * INTO 表變量或INSERT INTO 表變量 SELECT的方式創建
表變量的作用域（或說生命周期）在當前批處理語句中，1旦履行終了，被自動清除。
表變量存儲在內存中，不支持非聚集索引，使用束縛、默許值、計算列的時候不能援用自定義函數，沒有鎖的機制，事務也只針對表變量更新數據，因此生成的日志很小。
當用戶訪問表變量的時候，不會產生日志。
表變量本身雖然是1個變量，但不能賦值給另外一個變量。
不能使用TRUNCATE命令。
表變量的統計信息是不可信的。
從MSSQL2008R2開始，表變量可以作為存儲進程的參數。
表變量可以存在于內存和磁盤。
表變量使用當前數據庫的排序規則。
動態SQL需要使用表變量的話，必須在動態SQL中定義。
 
臨時表：
分為局部臨時表（使用#開頭）和全局臨時表（使用##開頭）。
局部臨時表生命周期為當前會話，1旦會話結束會自動刪除。
全局臨時表生命周期為所有連接會話，但是當創建它的會話結束后，也會被自動刪除。
臨時表的名稱不能超過116個字符，由于系統會自動在后面添加標識碼。
臨時表具有表的1般屬性，但是不能分區，不能設置外鍵，臨時表的列數據類型，必須是TempDB中存在的數據類型，換句話說，臨時表不能使用自定義數據類型。（自定義數據類型是數據庫級別，而臨時表只存在于TempDB數據庫）
臨時表可使用CREATE、SELECT * INTO、INSERT INTO SELECT的方式創建。
臨時表上的統計是相對可信的。
臨時表可以存在于內存和磁盤。
臨時表使用TempDB數據庫的排序規則。
可以先創建臨時表，然后在動態SQL中使用。
 
公共表達式（CTE）：
CTE只是1個履行結果集，結果集，結果集（重要的話說3遍）。
CTE存在于外部查詢中，它對同1批處理中的其他語句不可見。
CTE不能被嵌套，也就是CTE里面不能再定義新的CTE。
同1批語句中可以創建多個CTE，后面的CTE可以援用前面的CTE。
可以在1次查詢中屢次援用同1個CTE。
對CTE中記錄的刪除、修改一樣可以作用到CTE對應的數據表。
CTE可以遞歸使用。

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈