T-SQL 設計模式精講：Iterator、Intersector、Qualifier 三大核心模式與最佳實務

前言：將軟體設計模式引入 T-SQL 開發

設計模式不僅屬於物件導向語言。在 T-SQL 中識別並套用固定模式，能提升程式碼可讀性、維護性與效能穩定性。本文深度解析三大核心模式：Iterator (迭代)、Intersector (交集/聯結)、Qualifier (限定/篩選)，並對比新舊寫法優劣。

模式一：Iterator (迭代模式) —— 游標的標準化應用

適用場景

• 需逐筆處理結果集 (如動態建立索引、逐資料庫執行維護指令)。
• 邏輯無法集合化 (Set-based) 表達時的最後手段。

標準範本 (含資源釋放對稱原則)

DECLARE tables CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FOR READ ONLY;  -- 唯讀游標減少鎖負擔

DECLARE @table VARCHAR(128);

OPEN tables;
FETCH NEXT FROM tables INTO @table;  -- 建議使用 FETCH NEXT

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_help @table;  -- 業務邏輯
    FETCH NEXT FROM tables INTO @table;
END

CLOSE tables;      -- 對應 OPEN
DEALLOCATE tables; -- 對應 DECLARE，釋放記憶體結構

關鍵最佳實務

• CLOSE + DEALLOCATE 雙重釋放： CLOSE 釋放結果集鎖，DEALLOCATE 釋放游標結構記憶體。僅 DEALLOCATE 雖可隱含關閉，但違反對稱原則，難以除錯。
• 優先使用 FOR READ ONLY / FAST_FORWARD： 唯讀、順向游標效能最佳，避免不必要的鎖升級。
• 集合化替代： 90% 以上迭代需求可改寫為動態 SQL 批次或 sp_msforeachdb/sp_msforeachtable 系統程序，應優先嘗試。

模式二：Intersector (交集/聯結模式) —— 關聯查詢的標準化

推薦：ANSI SQL-92 明確 JOIN 語法

SELECT c.companyname, o.orderid
FROM customer c
INNER JOIN orders o ON c.customerid = o.customerid;

• 聯結條件與篩選條件 (WHERE) 完全分離，語意清晰。
• 支援 LEFT/RIGHT/FULL OUTER JOIN，語法一致。
• 查詢優化器更易產生最佳執行計畫。

不推薦：舊式 WHERE 聯結 (SQL-89)

-- 反面教材
SELECT c.companyname, o.orderid
FROM customer c, orders o
WHERE c.customerid = o.customerid;

嚴重缺陷：

• 外部聯結語法 (*=, =*) 模稜兩可，易產生錯誤結果 (如 Cartesian Product 與 Outer Join 混淆)。
• SQL Server 未來版本將移除支援 (已在兼容等級 100+ 棄用)。
• 聯結條件散落在 WHERE 中，維護困難。

進階變體：CROSS APPLY / OUTER APPLY

針對表值函數、XML 節點展開、動態衍生資料集，提供「行級聯結」能力，屬於 Intersector 模式的現代擴充。

模式三：Qualifier (限定/篩選模式) —— WHERE vs HAVING 的效能真相

正統用法：WHERE 子句預先篩選

SELECT city, COUNT(*) AS NumberCity
FROM customers
WHERE city LIKE 'A%'
GROUP BY city;

• 在聚合前過濾列，減少參與 GROUP BY 的資料量，降低 CPU/IO。
• 可利用索引 Seek 直接定位符合條件的列。

反模式：HAVING 子句事後篩選

SELECT city, COUNT(*) AS NumberCity
FROM customers
GROUP BY city
HAVING city LIKE 'A%';

效能陷阱：

• 邏輯上：先全表掃描 → 全部分群聚合 → 再篩選群組。資料量大時災難性。
• 雖然 SQL Server 優化器內部常將簡單 HAVING 轉換為 WHERE (執行計畫相同)，但不可依賴此優化：複雜運算式、函數呼叫、參數化查詢可能阻礙轉換。
• 語意上：HAVING 設計用於「聚合結果篩選」 (如 HAVING COUNT(*) > 100)，濫用於列篩選違反語意。

最佳實務口訣

列級篩選用 WHERE，群組級篩選用 HAVING，切勿混淆。

總結：模式思維提升 T-SQL 工藝

內化這三大模式，配合 Set-based 思維、執行計畫分析、參數化查詢，即可寫出高效、穩健、可維護的企業級 T-SQL 程式碼。