為什麼需要 SQL 開發規範？

SQL 是聲明式語言，寫法微小差異可能導致執行計劃天壤之別。統一規範能降低認知負擔、減少線上事故、提升團隊協作效率。以下 11 條規範提煉自大規模生產環境血淚教訓。

11 條核心規範

① 事務小而快，拒絕長事務

• 長事務持有鎖時間長 → 阻塞併發、膨脹事務日誌、增加死鎖概率、延遲日誌截斷/備份。
• 拆分大批量操作：分批提交（每 1000~5000 行）、避免在事務中調用外部服務/HTTP/RPC。
• 慎用 HOLDLOCK/SERIALIZABLE 隔離級別，除非業務極度要求幻讀保護。

② 避免重複訪問同一大表

• 多次掃描同一大表（如多個子查詢、CTE 重複引用） → 邏輯讀放大。
• 優先將篩選結果物化到 臨時表 / 表變量，再進行多表關聯：

-- 反例：三次掃描 orders 大表
SELECT * FROM orders WHERE cust_id IN (SELECT cust_id FROM vip);
SELECT * FROM orders WHERE status = 1;
SELECT * FROM orders WHERE amount > 1000;

-- 正例：一次掃描物化
SELECT * INTO #vip_orders FROM orders WHERE cust_id IN (SELECT cust_id FROM vip);
-- 後續複用 #vip_orders

③ 拒絕游標，拥抱集合思維

• 游標逐行處理（RBAR），效率極低，佔用 tempdb、鎖資源、難並行。
• 硬性紅線：操作超 1 萬行禁止游標，必須改寫為集合操作（JOIN、窗口函數、MERGE、批量 DML）。
• 若不得不游標：FAST_FORWARD（只讀、只進）、避免游標循環內再做表連接。

④ WHERE 條件順序 = 索引順序 = 選擇性從高到低

• 優化器雖會自動重排，但複雜查詢、統計信息過期時，順序仍影響計劃選擇。
• 編寫習慣：WHERE 高選擇性等值列 = ? AND 範圍列 > ? AND 低選擇性列 = ?。

⑤ 列不做運算，運算移等號右側

反例（索引失效）	正例（SARGable）
WHERE YEAR(create_time) = 2024	WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
WHERE DATEDIFF(day, dt, GETDATE()) < 7	WHERE dt >= DATEADD(day, -7, GETDATE())
WHERE ISNULL(name, '') = 'test'	WHERE name = 'test' OR name IS NULL
WHERE amount * 1.1 > 100	WHERE amount > 100 / 1.1

⑥ 存在性判斷用 EXISTS，不用 COUNT(*)

-- 慢：全表掃描計數
IF (SELECT COUNT(*) FROM big_table WHERE id = @id) > 0

-- 快：短路掃描
IF EXISTS (SELECT 1 FROM big_table WHERE id = @id)

COUNT(1) 比 COUNT(*) 略快（避免展開元數據），但 EXISTS 才是本質優化。

⑦ 優先用 >= 代替 >

整數列：WHERE id > 100 → WHERE id >= 101。優化器可直接定位到 101 的索引頁，而 > 需先定位 100 再前移。差異微小但習慣養成無成本。

⑧ OR / IN / UNION 互換藝術

• OR 往往導致索引掃描 → 改寫為 UNION ALL（去重用 UNION）分別走索引再合併。
• IN (subquery) 在舊版本可能退化為相關子查詢 → 改寫為 EXISTS 或 JOIN。
• IN (值列表) 值超過幾百個 → 考慮臨時表 + JOIN，避免編譯超時/計劃膨脹。

⑨ 連接列數據類型、長度、校對集必須一致

• VARCHAR(20) JOIN VARCHAR(50) → 隱式轉換、索引失效、校對集衝突報錯。
• 外鍵關聯列定義完全一致（含 NULL/NOT NULL）。

⑩ 存儲過程參數類型與表列定義強綁定

-- 反例：參數長度大於列，隱式截斷不報錯
CREATE PROC usp_get @name VARCHAR(100) AS SELECT * FROM t WHERE col = @name; -- col 是 VARCHAR(20)

-- 正例：參數長度 = 列長度，或用 TYPE
CREATE PROC usp_get @name VARCHAR(20) AS ...  -- 或
CREATE PROC usp_get @name AS t.col%TYPE ...  -- Oracle 風格，SQL Server 需手動對齊

⑪ 大批量 DML 分批提交，防鎖升級

• SQL Server 鎖升級閾值：單語句持有鎖 > 5000 個 → 行/頁鎖升級為表鎖（可通過 ALTER TABLE SET (LOCK_ESCALATION = AUTO/DISABLE) 控制）。
• 批量刪除/更新模板：

DECLARE @batch_size INT = 5000, @rows INT = 1;
WHILE @rows > 0
BEGIN
  DELETE TOP (@batch_size) FROM big_table WHERE create_time < '2023-01-01';
  SET @rows = @@ROWCOUNT;
  WAITFOR DELAY '00:00:00.1'; -- 釋放鎖、讓出 CPU
END

規範落地清單

1. 代碼評審 Checklist 化（含上述 11 條）。
2. CI/CD 管道集成 SQLFluff / TSQLLint 靜態掃描。
3. 慢查詢日誌 / Query Store 定期回顧，反饋至規範迭代。
4. 新人入職必讀《團隊 SQL 開發手冊》，老項目增量重構。

規範不是束縛，而是團隊共識的「護欄」。好規範讓正確的寫法成為最省力的寫法，讓錯誤無處遁形。