網頁

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:

2 則留言:

  1. 看到你提的第二點
    "如果在Store Procedure使用CTE,會造成每次執行都會re-compiler,效能低落"
    真有恍然大悟的感覺。
    之前修改資深前輩寫的Store Procedure,因為是大型的資料查詢所以執行速度頗慢(大約12~14秒才執行完畢),後來我建立了一個table變數(有設定primary key)來取代裡面的CTE,沒想到效能突然暴增(大約2秒就執行完畢),不過一直不是很瞭解原因,這下終於知道了。

    回覆刪除
  2. Dear Kenny Hsu
    沒有什麼東西是有利無弊,但很多東西需要…時間、經驗…去Try才會了解。像CTE這種東西,MSDN只會跟你說可以使用於SP…,其他的三七二十一MSDN也沒交代。

    用對地方,CTE才是好物。

    也感謝你的分享。

    回覆刪除

感謝您的留言,如果我的文章你喜歡或對你有幫助,按個「讚」或「分享」它,我會很高興的。