T-SQL -- Common Table Expression (CTE) 教學重點筆記

開始使用SQL Server 2005後所新增的語法來改寫T-SQL，我真的喜歡它們。如果你已經有安裝MS SQL Server 2005 / 2008 / 2008 R2…等版本，那一定不要錯過在SQL Server 2005所新增的Common Table Expression(一般資料表運算式, CTE)。

CTE我就不寫教學了，我是看這一篇「一般資料表運算式」學的，看一下語法看一下教學，基本使用上是沒有問題的，以下是一些重點筆記：

• 可以把CTE當成"Temp View"
• 如果在Store Procedure使用CTE，會造成每次執行都會re-compiler，效能低落
• 資料集未以Table存放於Database時，適合用CTE
• View通常用來分解大型查詢，如果只用一次，那View不是好辦法
• 子查詢只能從所在的"陳述式"存取，如果有多個相同的子查詢，必須含重覆程式碼，難閱讀及維護
• CTE能在同批次下，重覆使用(像View一樣)
• CTE只能用於其後第一個陳述式(Select, Insert, Delete, Update)，此陳述式可多次取用CTE
• CTE後面可接另一個CTE，使用逗號(,)

遞迴CTE

1. 建立傳回最上層的查詢(Anchor Member)
2. 撰寫遞迴查詢(Recursive Member)
3. Union All
4. 確認不會傳回的資料列

 因為是遞迴，所以有兩點須注意：

1. 你必須明確終止"子句條件"
2. 或由CTE協助終止
   
   1. Recursive Member傳回"零"記錄
   2. 設定 MaxRecursion
   3. CTE預設最大遞迴數為 100
3. Recursive Member只能參考CTE一次
   

;With RecurCTE(c1, c2, c3, ...cn) As 
(
  -- Anchor Member Query
  Union All
  -- Recursive Member Query
)
Select * From RecurCTE
Option (MaxRecursion 10) -- 最大遞迴數 10

遞迴CTE限制

遞迴CTE不能使用於：

• Select Distinct
• Group by
• Having
• Top
• Left/Right Outer Join

相信我，把CTE學起來，會對你在T-SQL的撰寫上有很大的幫助。

Reference：

• 一般資料表運算式 (簡單易懂)
• 使用一般資料表運算式 (了解一下語法)
• WITH common_table_expression (Transact-SQL)
• MSSQL