【SQL】Derived table, Subquery, CTE, Temporary table, Table variable

這篇主要是面試被考到 CTE，但我實際上不只沒寫過，連聽都沒聽過，查了一下發現暫存資料集的眉眉角角比想像的多很多，所以做一下整理，有誤還請指出。

Derived table & Subquery  
衍生資料表就是具名的子查詢

ex. 查詢俱樂部='內湖'，所有人的英文名字

-- Subquery
SELECT [EngFirstName]
FROM [Member] tM
WHERE Id IN
( 
    SELECT [MemberId]
    FROM [MemberClub] tMC
    INNER JOIN [Club] tC ON tMC.ClubId = tc.Id
    WHERE tc.ChineseAbbr = '內湖'
)
 
-- Derived table
SELECT [EngFirstName]
FROM [Member] tM
INNER JOIN 
( 
    SELECT [MemberId]
    FROM [MemberClub] tMC
    INNER JOIN [Club] tC ON tMC.ClubId = tc.Id
    WHERE tc.ChineseAbbr = '內湖'
) derived
ON tM.Id = derived.MemberId

兩組會 SELECT 出一樣的結果，但在第二種我們給了子查詢別名，就讓這個子查詢成了衍生資料表。

CTE (Common Table Expressions)
應用上近似於 一次性的檢視表 或 整理過可讀性的衍生表。
主要應用是 提高可讀性 與 遞迴產生資料集(樹狀階層式結構)。

WITH cteNeihuMemberIds ([MemberId]) as
(
    SELECT [MemberId]
    FROM [MemberClub] tMC
    INNER JOIN [Club] tC ON tMC.ClubId = tc.Id
    WHERE tc.ChineseAbbr = '內湖'
)
 
SELECT [EngFirstName]
FROM [Member] tM
INNER JOIN cteNeihuMemberIds
ON tM.Id = cteNeihuMemberIds.MemberId
 
-- X
-- Can only use one time
SELECT [Name]
FROM [Role] tR
INNER JOIN RoleRecord tRR
ON tR.Id = tRR.RoleId
INNER JOIN cteNeihuMemberIds
ON tRR.MemberId = cteNeihuMemberIds.MemberId

ref: CTE vs subquery；它的強項遞迴查詢: 暗黑執行緒、MSDN

Temporary table

CREATE TABLE #tmp (Col1 int, Col2 nvarchar(50));

基本上等同於資料表(有資料庫物件存在 tempdb)，叢集、非叢集索引都可以有，可以做 CRUD 操作，跟真的資料表唯一差別是 Session(Connection) 結束會 DROP(如果是 sp 建的，則 sp 結束 DROP)，但最好是我們記得自己手動 DROP 它。

Table variable

DECLARE @tmp TABLE (Col1 int, Col2 nvarchar(50));

是 temp table 的簡化版，一樣會有資料庫物件(我本來也以為沒有)，可以 CRUD，但沒有非叢集索引(< 2014的版本)和篩選索引(< 2016的版本)，它的 scope 就是變數的 life scope，試下面範例

DECLARE @temp TABLE (col1 INT)
INSERT @temp VALUES(1)
SELECT * FROM @temp
GO
SELECT * FROM @temp

關於 GO，延伸閱讀：批次

應用上 table variable 適用於小資料集、少量操作，temp table 適合大資料集、大量操作，table variable 除了語法限制(沒有 TRUNCATE、SELECT INTO等)，最佳化也會以小資料集為前提規劃執行計畫。 另外 table variable 不受交易 rollback 的影響。

這部分細節蠻多的，參考：SO(DBA)、暗黑執行緒、蔡煥麟老師、MSDN