四大 T-SQL 技巧提升 SQL Server 應用擴展性與效能

資料庫效能瓶頸多源於設計與查詢。本文精選四項經實證有效的技巧：正確擷取 IDENTITY、善用 LEFT JOIN 替代子查詢、CROSS JOIN 產生組合、避免隱性轉換導致索引失效。

1. 正確擷取 INSERT 後的 IDENTITY 值

錯誤做法：在不同連線、或中間夾雜其他陳述式後呼叫 @@IDENTITY，易受觸發器插入其他表干擾回傳錯誤值。

正確模式：同一連線、緊接著 INSERT 後立即呼叫。

INSERT INTO Products (ProductName) VALUES ('Chalk');
SELECT @@IDENTITY AS NewID;  -- 同一批次、同一連線

最佳實務 (SQL 2005+)：使用 SCOPE_IDENTITY() 僅回傳目前範圍 (Stored Procedure/Trigger/Batch) 產生的身分值，不受巢狀觸發器影響。

2. LEFT JOIN 替代 NOT IN / NOT EXISTS 子查詢

子查詢在大資料集下易產生糟糕計畫。改寫為 LEFT JOIN ... WHERE RightTable.PK IS NULL 通常能獲得更佳的 Hash/Anti-Semi Join 計畫。

-- 原寫法
SELECT * FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

-- 改寫
SELECT c.* FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

3. CROSS JOIN 產生笛卡兒積：報表輔助表、測試資料生成

-- 產生 2020-2025 年每月報表期間
SELECT y.YearVal, m.MonthVal
FROM (VALUES (2020),(2021),(2022),(2023),(2024),(2025)) y(YearVal)
CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m(MonthVal);

4. 避免隱性資料類型轉換導致索引掃描

WHERE VarcharCol = 123 會隱性將欄位轉換為 INT，導致索引無法尋找。務必確保比較雙方資料類型一致，或使用顯性 CAST/CONVERT。

效能驗證流程

1. 擷取實際執行計畫 (Ctrl+M)。
2. 觀察 Index Seek vs Index Scan、Key Lookup、Implicit Conversion 警告。
3. 使用 SET STATISTICS IO, TIME ON 量化邏輯讀取與 CPU 時間。
4. 在代表性資料量下測試，並對比改寫前後。