什麼規則確定 SQL Server 何時將 CTE 用作“優化圍欄”？

不久前，Brent Ozar 發表了一篇文章，詳細介紹了 SQL Server 和 PostgreSQL 之間的一些差異：

SQL Server 和 PostgreSQL 之間的兩個重要區別

第一點（“CTE 是優化柵欄”）引起了我的注意，因為很明顯，在提供的範例中，SQL Server 將 CTE 和主查詢組合在一起並將其優化為單個查詢（與PostgreSQL）。

但是，這種行為似乎與我在其他部落格和培訓課程中看到的範例相反，在這些範例中，SQL Server 確實將 CTE 視為優化圍欄，從而可以更好地使用索引、提高性能等。例如：

更好的選擇明星的方法

因此，似乎 SQL Server 有時會“尊重” CTE 作為優化圍欄。是否有任何好的資源可以記錄 SQL Server 可靠地將 CTE 作為優化圍欄（或相反行為）的已知案例的特定列表？

…SQL Server 將可靠地將 CTE 視為優化圍欄的已知案例列表

任何此類列表都將依賴於觀察到的行為，而不能保證可靠性。

SQL Server 查詢優化器從不將公用表表達式本身視為優化柵欄，儘管某些結構顯然難以優化。遞歸 CTE 就是一個很好的例子。

CTE 的處理方式與視圖/內聯函式/子查詢/派生表非常相似，並內聯到查詢中。任何觀察到的“圍欄”行為都取決於優化器是否無法或決定不跨原則上的可滲透邊界進行優化。

一般來說，CTE 越簡單、越“相關”，優化器就越有可能移動位。

已經提出了允許優化器考慮或強制其實現 CTE 的“結果”的功能，但尚未實現：

• 優化器應考慮 CTE 的具體化結果
• 附加查詢提示：OPTION (MATERIALIZE (cte_name,…,n)

同時，最常見的解決方法是在臨時表或表變數中顯式實現中間結果集。這顯然需要一個不限於單個語句的場景。

引用自：https://dba.stackexchange.com/questions/221555