轉：SQL資料庫中暫存資料表、臨時變數和WITH AS關鍵詞建立“暫存資料表”的區別

標籤：itblog   row   事務   str   就會   空間   表名   body   serve   

原文連結：https://www.cnblogs.com/zhaowei303/articles/4204805.html

SQL資料庫中資料處理時，有時候需要建立暫存資料表，將查詢後的結果集放到暫存資料表中，然後在針對這個資料進行操作。

　　建立“暫存資料表”（邏輯上的暫存資料表，可能不一定是資料庫的）的方法有一下幾種：

　　1.with tempTableName as方法（05之後出現）：

　　with temptable as 其實並沒有建立暫存資料表，只是子查詢部分（subquery factoring），定義一個SQL片斷，該SQL片斷會被整個SQL語句所用到。有的時候，是為了讓SQL語句的可讀性更高些，也有可能是在UNION ALL的不同部分，作為提供資料的部分。特別對於UNION ALL比較有用。因為UNION ALL的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，所以可以使用WITH AS短語，則只要執行一遍即可。

　　樣本：

　　with Snd    as    (        select * from category         where cgtype=2        and [email protected]    ),    thrd    as    (        select c.* from category c        inner join Snd s        on c.parentid=s.cgid        and c.cgtype=3    ),    forth    as    (        select c.* from category c        inner join thrd t        on c.parentid=t.cgid        and c.cgtype=4    )

　　注意：上述代碼，要是在with Snd前面加一句  “decalare @FstCgid int =1234”，必須以“;”結尾，否則會報錯；

　　備忘：如果WITH AS短語所定義的表名被調用兩次以上，則最佳化器會自動將WITH AS短語所擷取的資料放入一個TEMP表裡，如果只是被調用一次，則不會。而提示materialize則是強制將WITH AS短語裡的資料放入一個全域暫存資料表裡。

　　

　　2.暫存資料表方法

　　暫存資料表與永久表相似，只是它的建立是在Tempdb中，它只有在一個資料庫連接結束後或者由SQL命令DROP掉，才會消失，否則就會一直存在（暫存資料表一般被建立後，如果在執行的時候，沒有通過DROP Table的操作，第二次就不能再被建立）。暫存資料表在建立的時候都會產生SQL Server的系統日誌，雖它們在Tempdb中體現，是分配在記憶體中的，它們也支援物理的磁碟，但使用者在指定的磁碟裡看不到檔案。

　　暫存資料表分為本地和全域兩種，本地暫存資料表的名稱都是以“#”為首碼，只有在本地當前的使用者串連中才是可見的，當使用者從執行個體中斷連線時被刪除。全域暫存資料表的名稱都是以“##”為首碼，建立後對任何使用者都是可見的，當所有引用該表的使用者中斷連線時被刪除。

　　樣本：　

if object_id(‘tempdb..#tempCategory‘) is not null drop table #tempCategory    create table #tempCategory(        num int,        CGName varchar(50),        CGID int,        PartnerID int,        UpdTime datetime,        Operator varchar(50)    )

 

　　備忘：暫存資料表的一些特性：

　　1)添加、修改、刪除列。例如，列的名稱、長度、資料類型、精度、小數位元以及為空白性均可進行修改，只是有一些限制而已。

　　2)可添加或刪除主鍵和外鍵約束。

　　3)可添加或刪除 UNIQUE 和 CHECK 條件約束及 DEFAULT 定義(對象)。

　　4)可使用 IDENTITY 或 ROWGUIDCOL 屬性添加或刪除標識符列。雖然 ROWGUIDCOL 屬性也可添加至現有列或從現有列刪除，但是任何時候在表中只能有一列可具有該屬性。  

　　5)表及表中所選定的列登入為全文索引。

 

　　3.表變數方法

　　表變數建立的文法類似於暫存資料表，區別就在於建立的時候，必須要為之命名。表變數是變數的一種，表變數也分為本地及全域的兩種，本地表變數的名稱都是以“@”為首碼，只有在本地當前的使用者串連中才可以訪問。全域的表變數的名稱都是以“@@”為首碼，一般都是系統的全域變數，像我們常用到的，如@@Error代表錯誤的號，@@RowCount代表影響的行數。

　　樣本：

DECLARE @News Table 　　( 　　News_id int NOT NULL, 　　NewsTitle varchar(100), 　　NewsContent varchar(2000), 　　NewsDateTime datetime 　　)

 

　

　　上述三種方法的區別：

　　暫存資料表和表變數的區別：

　　1)表變數是儲存在記憶體中的，當使用者在訪問表變數的時候，SQL Server是不產生日誌的，而在暫存資料表中是產生日誌的;

　　2)在表變數中，是不允許有非叢集索引的;

　　3)表變數是不允許有DEFAULT預設值，也不允許有約束;

　　4)暫存資料表上的統計資訊是健全而可靠的，但是表變數上的統計資訊是不可靠的;

　　5)暫存資料表中是有鎖的機制，而表變數中就沒有鎖的機制。

　　暫存資料表和表變數的選擇：

　　1)使用表變數主要需要考慮的就是應用程式對記憶體的壓力，如果代碼的運行執行個體很多，就要特別注意記憶體變數對記憶體的消耗。我們對於較小的資料或者是通過計算出來的推薦使用表變數。如果資料的結果比較大，在代碼中用於臨時計算，在選取的時候沒有什麼分組的彙總，就可以考慮使用表變數。

　　2)一般對於大的資料結果，或者因為統計出來的資料為了便於更好的最佳化，我們就推薦使用暫存資料表，同時還可以建立索引，由於暫存資料表是存放在Tempdb中，一般預設分配的空間很少，需要對tempdb進行調優，增大其儲存的空間。

　　CTE和WITH AS短語結合使用提高SQL查詢效能：

　　cet要比表變數效率高得多！

　　表變數實際上使用了暫存資料表，從而增加了額外的I/O開銷，因此，表變數的方式並不太適合資料量大且頻繁查詢的情況。

註：預存程序中局部暫存資料表是事務級的如：

圖片1

 

圖片2:

圖1的寫法 A和B屬於兩個不同的事務，所以會報錯。而圖2的寫法是一個事務裡面則成功執行。

2. 其他寫法就是不用拼接SQL語句，直接寫就行

    select * into #TempProducts from [dbo].[Sys_Product];

    select * from #TempProducts;

3. 一個#號的暫存資料表是事務層級的，就是說一個暫存資料表只存在於一個事務裡面。

轉：SQL資料庫中暫存資料表、臨時變數和WITH AS關鍵詞建立“暫存資料表”的區別