iT邦幫忙

第 11 屆 iT 邦幫忙鐵人賽

1
Modern Web

淺談資料庫&ASP.net&C# 入門系列 第 33

[iT鐵人賽Day33] SQL Server 暫存表(@ # ##)與CTE (Common Table Expressions)

前言

今天要介紹的是SQL Server的暫存表以及CTE的用法,因為在之後我打算寫ASP.NET自製分頁控制項的應用,會用到一點暫存表與CTE的SQL指令,所以這篇就來稍微介紹一下暫存表和CTE的用法,暫存表會介紹@Table跟#Table和##Table的用法與差異。

使用暫存表

SQL Server 產生暫存的方式還滿多種的,要使用哪中方式就要取決於不同的時機,資料量的大小、使用方法的頻率等...,適當的時機使用暫存表對於效能會有很大的幫助。

1.暫存表 [#Table]

記憶體存在於tempdb,可建立索引(index),當SQL Server關閉時,#Table會自動DROP,但最好自己手動DROP。

建立方式方式1:

Select * into [#Table_name] from [資料表名稱] where 條件

上面完成後,確認暫存表是否存在

Select * from #Table_name

使用後記得刪除

DROP TABLE #Table_name

建立方式方式2:

CREATE TABLE [#Table_name]
(
    [欄位1] 資料型態,
    [欄位2] 資料型態
)
INSERT INTO #Table_name ([欄位1],[欄位2])

SELECT [欄位1],[欄位2]
FROM  [資料表名稱]  //要參考的資料表名稱

使用後記得刪除

DROP TABLE #Table_name

如果沒有加#的話,就會創一個真正的資料表出來。

2.暫存表 [##Table]

double#與上述1的[#Table_name]都是暫存資料表,兩者均在tempdb資料庫中建立起資料表(存於DISK中),可利用 DROP TABLE 刪除暫存資料表,或是建立該暫存資料表的連線結束時,SQL Server 會自動將其刪除。

差異

兩者的差異在於,#Table只有建立者可以使用,其他人不可使用(其他資料庫),而##Table則是全域的資料表,所有人均可取用(其他資料庫也可使用)。

##Table用法如下,只是多加一個#:

方式1:
Select * into [##Table_name] from [資料表名稱] where 條件

方式2:

CREATE TABLE [##Table_name]
(
    [欄位1] 資料型態,
    [欄位2] 資料型態
)
INSERT INTO ##Table_name ([欄位1],[欄位2])

SELECT [欄位1],[欄位2]
FROM  [資料表名稱]  //要參考的資料表名稱

最後記得手動刪除
Drop TABLE ##Table_name

3.暫存表 [@Table]

稱為資料表變數,將資料表當作是一個變數來使用,當批次指令執行完成後即自動從記憶體中被刪除(可參考下範例)。

所以@Table與#Table & ##Table的差異在於,前者不需使用DROP指令,當此次指令執行完後會自動被刪除;後者
需使用DROP或是待連線結束後暫存表才會消失。

@Table使用方法:

DECLARE  @Table_name AS TABLE
(
   [欄位1] 資料型態,
   [欄位2] 資料型態
)

INSERT INTO @Table_name([欄位1],[欄位2])
SELECT [欄位1],[欄位2]
FROM  [資料表名稱]  //要參考的資料表名稱

SELECT *
FROM @Table_name

範例:

建立一個@Table讓它30秒後執行。

DECLARE  @Table AS TABLE
(
    ID INT,
    [NAME] NVARCHAR(20)
)

INSERT INTO @Table(ID,NAME)
SELECT id,name 
from UserInformation

SELECT *
FROM @Table
WAITFOR DELAY '00:00:30'

30秒前,從tempdb暫存資料表中可以看到有一個@temp資料表(紅框)

https://ithelp.ithome.com.tw/upload/images/20191006/20119925nY2KUifTzK.png

30秒SQL執行完畢後,會發現,@Table從資料庫記憶體中消失了。

https://ithelp.ithome.com.tw/upload/images/20191006/20119925F0CDyqje1W.png

一般資料表運算式(Common Table Expressions, CTE)

簡稱CTE,與暫存資料表(#table)不同的是,使用CTE查詢完的當下就會從記憶體中消失,可以減少重覆計算所耗的I/O、CPU和執行時間,如果同樣的查詢需使用很多次時,非常適合使用CTE,例如分頁。

使用方式:

WITH [CTE名稱] ([CTE的欄位名稱A],[CTE的欄位名稱B],... ) as (SELECT [欄位1],[欄位2],...  FROM [資料表名稱])
SELECT * FROM CTE //一定要直接顯示,不直接的話就會立馬消失了,因為CTE查詢完的當下就會從記憶體中消失。

範例:

Customers資料表

https://ithelp.ithome.com.tw/upload/images/20191006/20119925qcPgDknwPT.png

使用CTE單一查詢用法

with cte_name (品牌,國家) as (select name,address from Customers)
select * from cte_name

顯示結果:

https://ithelp.ithome.com.tw/upload/images/20191006/20119925AH9UgwYTbL.png

感謝閱讀,以上若有任何問題或錯誤,麻煩務必糾正及建議,感謝。

參考資料
SQL - 檢視(view),暫存資料表(#Table),資料表變數(@Table),衍生資料表(子查詢),一般資料表(CTE)
資料庫暫存表 @[TableName] , # [TableName],## [TableName] 解說
TempDB的基本調教
#TEMPTABLE 、##TEMPTABLE 、@TEMPTABLE 區別?!
通用資料表運算式(CTE)
如何在 T-SQL 中宣告變數


上一篇
[iT鐵人賽Day32]請出示驗證票(Ticket),才能入場買餅乾(Cookie)吃唷!(FormsAuthentication 授權驗證 會員登入)
下一篇
[iT鐵人賽Day34]SQL Server 實用的排序函數 ROW_NUMBER()
系列文
淺談資料庫&ASP.net&C# 入門36

尚未有邦友留言

立即登入留言