前言

今天要介紹的是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資料表(紅框)

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

一般資料表運算式(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資料表

使用CTE單一查詢用法

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

顯示結果:

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

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