表變數和臨時表的區別

　　小夥伴們有知道什麼是表變數的嗎?臨時表又是什麼呢?這兩者有何區別呢?下面就讓小編來為大家介紹一下吧，希望大家喜歡。

　　一、表變數

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

　　表變數的特徵：

　　表變數擁有特定作用域在當前批處理語句中，但不在任何當前批處理語句呼叫的儲存過程和函式中，表變數在批處理結束後自動被清除。

　　表變數較臨時表產生更少的儲存過程重編譯。

　　針對表變數的事務僅僅在更新資料時生效，所以鎖和日誌產生的數量會更少。

　　由於表變數的作用域如此之小，而且不屬於資料庫的持久部分，所以事務回滾不會影響表變數。

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

　　表變數不能做如下事情:

　　雖然表變數是一個變數，但是其不能賦值給另一個變數。

　　check約束，預設值和計算列不能引用自定義函式。

　　不能為約束命名。

　　不能Truncate表變數。

　　不能向標識列中插入顯式值也就是說表變數不支援SET IDENTITY_INSERT ON

　　二、臨時表

　　在深入臨時表之前，我們要了解一下會話Session，一個會話僅僅是一個客戶端到資料引擎的連線。在SQL Server Management Studio中，每一個查詢視窗都會和資料庫引擎建立連線。一個應用程式可以和資料庫建立一個或多個連線，除此之外，應用程式還可能建立連線後一直不釋放知道應用程式結束，也可能使用完釋放連線需要時建立連線。

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

　　1、臨時表的名稱不能超過116個字元，這是由於資料庫引擎為了辨別不同會話建立不同的臨時表，所以會自動在臨時表的名字後附加一串。

　　2、區域性臨時表以"#"開頭命名的作用域僅僅在當前的連線內，從在儲存過程中建立區域性臨時表的角度來看，區域性臨時表會在下列情況下被Drop：

　　a、顯示呼叫Drop Table語句

　　b、當局部臨時表在儲存過程內被建立時，儲存過程結束也就意味著區域性臨時表被Drop。

　　c、當前會話結束，在會話內建立的所有區域性臨時表都會被Drop。

　　3、全域性臨時表以"##"開頭命名的在所有的會話內可見，所以在建立全域性臨時表之前首先檢查其是否存在，否則如果已經存在，你將會得到重複建立物件的錯誤。

　　a、全域性臨時表會在建立其的會話結束後被Drop，Drop後其他會話將不能對全域性臨時表進行引用。

　　b、引用是在語句級別進行，如:

　　1.新建查詢視窗，執行語句：

　　CREATE TABLE ##tempRowID int

　　INSERT INTO ##temp VALUES3

　　2.再次新建一個查詢視窗，每5秒引用一次全域性臨時表

　　While 1=1

　　BEGIN

　　SELECT * FROM ##temp

　　WAITFOR delay '00:00:05'

　　END

　　3.回到第一個視窗，關閉視窗。

　　4.下一次第二個視窗引用時，將產生錯誤。

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

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

　　6、臨時表內列的資料型別不能定義成沒有在TempDb中沒有定義自定義資料型別自定義資料型別是資料庫級別的物件，而臨時表屬於TempDb。由於TempDb在每次SQL Server重啟後會被自動建立，所以你必須使用startup stored procedure來為TempDb建立自定義資料型別。你也可以通過修改Model資料庫來達到這一目標。

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

　　臨時表既可以通過Create Table語句建立，也可以通過"SELECT INTO #table"語句建立。你還可以針對臨時表用"INSERT INTO #table EXEC stored_procedure"這樣的語句。

　　臨時表可以擁有命名的約束和索引。但是，當兩個使用者在同一時間呼叫同一儲存過程時，將會產生”There is already an object named ‘’ in the database”這樣的錯誤。所以最好的做法是不用為建立的物件進行命名，而使用系統分配的在TempDb中唯一的。

　　三、誤區

　　誤區1.表變數僅僅在記憶體中。

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

　　這兩種觀點都是錯誤的，只有記憶體足夠，表變數和臨時表都會在記憶體中建立和處理。他們也同樣可以在任何時間被存入磁碟。

　　注意表變數的名字是系統分配的，表變數的第一個字元”@”並不是一個字母，所以它並不是一個有效的變數名。系統會在TempDb中為表變數建立一個系統分配的名稱，所以任何在sysobjects或sys.tables查詢表變數的方法都會失敗。

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

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

　　上述程式碼看上去貌似很好用，但會產生多使用者的問題。你建立兩個連線，在第一個連線中建立臨時表，在第二個視窗中執行上面的語句能看到第一個連線建立的臨時表，如果你在第二個連線中嘗試操作這個臨時表，那麼可能會產生錯誤，因為這個臨時表不屬於你的會話。

　　誤區3.表變數不能擁有索引。

　　這個誤區也同樣錯誤。雖然一旦你建立一個表變數之後，就不能對其進行DDL語句了，這包括Create Index語句。然而你可以在表變數定義的時候為其建立索引比如如下語句。

　　declare @MyTableVariable table RowID intPRIMARY KEY CLUSTERED

　　這個語句將會建立一個擁有聚集索引的表變數。由於主鍵有了對應的聚集索引，所以一個系統命名的索引將會被建立在RowID列上。

　　下面的例子演示你可以在一個表變數的列上建立唯一約束以及如何建立複合索引。

　　declare @temp TABLE

　　RowID int NOT NULL,

　　ColA int NOT NULL,

　　ColB char1UNIQUE,

　　PRIMARY KEY CLUSTEREDRowID, ColA

　　1 SQL 並不能為表變數建立統計資訊，就像其能為臨時表建立統計資訊一樣。這意味著對於表變數，執行引擎認為其只有1行，這也意味著針對表變數的執行計劃並不是最優。雖然估計的執行計劃對於表變數和臨時表都為1，但是實際的執行計劃對於臨時表會根據每次儲存過程的重編譯而改變。如果臨時表不存在，在生成執行計劃的時候會產生錯誤。

　　2 一旦建立表變數後就無法對其進行DDL語句操作。因此如果需要為表建立索引或者加一列，你需要臨時表。

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

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

　　5 作用域:表變數僅僅在當前的批處理中有效,並且對任何在其中巢狀的儲存過程等不可見。區域性臨時表只在當前會話中有效，這也包括巢狀的儲存過程。但對父儲存過程不可見。全域性臨時表可以在任何會話中可見，但是會隨著建立其的會話終止而DROP，其它會話這時就不能再引用全域性臨時表。

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

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

　　四、如何選擇

　　微軟推薦使用表變數，如果表中的行數非常小，則使用表變數。很多”網路專家”會告訴你100是一個分界線,因為這是統計資訊建立查詢計劃效率高低的開始。但是我還是希望告訴你針對你的特定需求對臨時表和表變數進行測試。很多人在自定義函式中使用表變數，如果你需要在表變數中使用主鍵和唯一索引，你會發現包含數千行的表變數也依然效能卓越。但如果你需要將表變數和其它表進行join,你會發現由於不精準的執行計劃，效能往往會非常差。

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

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

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

　　五、總結

　　特性表變數臨時表

　　作用域當前批處理當前會話，巢狀儲存過程，全域性：所有會話

　　使用場景自定義函式，儲存過程，批處理自定義函式，儲存過程，批處理

　　建立方式DECLARE statement only.只能通過DECLEARE語句建立CREATE TABLE 語句

　　SELECT INTO 語句.

　　表名長度最多128位元組最多116位元組

　　列型別可以使用自定義資料型別

　　可以使用XML集合自定義資料型別和XML集合必須在TempDb內定義

　　Collation字串排序規則繼承自當前資料庫字串排序規則繼承自TempDb資料庫

　　索引索引必須在表定義時建立索引可以在表建立後建立

　　約束PRIMARY KEY, UNIQUE, NULL, CHECK約束可以使用，但必須在表建立時宣告PRIMARY KEY, UNIQUE, NULL, CHECK. 約束可以使用，可以在任何時後新增，但不能有外來鍵約束

　　表建立後使用DDL 索引，列不允許允許.

　　資料插入方式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不允許允許

　　析構方式批處理結束後自動析構顯式呼叫 DROP TABLE 語句.

　　當前會話結束自動析構 全域性臨時表: 還包括當其它會話語句不在引用表.

　　事務只會在更新表的時候有事務，持續時間比臨時表短正常的事務長度，比表變數長

　　儲存過程重編譯否會導致重編譯

　　回滾不會被回滾影響會被回滾影響

　　統計資料不建立統計資料，所以所有的估計行數都為1,所以生成執行計劃會不精準建立統計資料，通過實際的行數生成執行計劃。

　　作為引數傳入儲存過程僅僅在SQL Server2008, 並且必須預定義 user-defined table type.不允許

　　顯式命名物件 索引, 約束.不允許允許，但是要注意多使用者的問題

　　動態SQL必須在動態SQL中定義表變數可以在呼叫動態SQL之前定義臨時表

　　公共表示式、臨時表、表變數之間的區別

　　表變數：

　　表變數必須顯示定義，不能使用SELECT * INTO 表變數或INSERT INTO 表變數 SELECT的方式建立

　　表變數的作用域或者說生命週期在當前批處理語句中，一旦執行完畢，被自動清除。

　　表變數儲存在記憶體中，不支援非聚集索引，使用約束、預設值、計算列的時候不能引用自定義函式，沒有鎖的機制，事務也只針對表變數更新資料，因此生成的日誌很小。

　　當用戶訪問表變數的時候，不會產生日誌。

　　表變數本身雖然是一個變數，但不能賦值給另一個變數。

　　不能使用TRUNCATE命令。

　　表變數的統計資訊是不可信的。

　　從MSSQL2008R2開始，表變數可以作為儲存過程的引數。

　　表變數可以存在於記憶體和磁碟。

　　表變數使用當前資料庫的排序規則。

　　動態SQL需要使用表變數的話，必須在動態SQL中定義。

　　臨時表：

　　分為區域性臨時表使用#開頭和全域性臨時表使用##開頭。

　　區域性臨時表生命週期為當前會話，一旦會話結束會自動刪除。

　　全域性臨時表生命週期為所有連線會話，但是當建立它的會話結束後，也會被自動刪除。

　　臨時表的名稱不能超過116個字元，因為系統會自動在後面新增標識碼。

　　臨時表具有表的一般屬性，但是不能分割槽，不能設定外來鍵，臨時表的列資料型別，必須是TempDB中存在的資料型別，換句話說，臨時表不能使用自定義資料型別。自定義資料型別是資料庫級別，而臨時表只存在於TempDB資料庫

　　臨時表可以使用CREATE、SELECT * INTO、INSERT INTO SELECT的方式建立。

　　臨時表上的統計是相對可信的。

　　臨時表可以存在於記憶體和磁碟。

　　臨時表使用TempDB資料庫的排序規則。

　　可以先建立臨時表，然後在動態SQL中使用。

　　公共表示式CTE：

　　CTE只是一個執行結果集，結果集，結果集重要的話說三遍。

　　CTE存在於外部查詢中，它對同一批處理中的其他語句不可見。

　　CTE不能被巢狀，也就是CTE裡面不能再定義新的CTE。

　　同一批語句中可以建立多個CTE，後面的CTE可以引用前面的CTE。

　　可以在一次查詢中多次引用同一個CTE。

　　對CTE中記錄的刪除、修改同樣可以作用到CTE對應的資料表。

　　CTE可以遞迴使用。

猜你喜歡