四大 SQL Server 擴展技巧大幅提升查詢效能與擴展性

資料庫應用效能瓶頸多半源於查詢設計與索引策略。本文深入解析四項實戰證明有效的技巧：INSERT 後高效取回 IDENTITY、內嵌檢視取代暫存表、避免濫用 LEFT JOIN 與 NULL、善用 CROSS JOIN 產生完整維度組合，並附贈 WHERE/HAVING、DISTINCT/GROUP BY 選擇指南。

技巧一：INSERT 後立即取回 IDENTITY 值的最佳實務

問題

多連線環境下，SELECT @@IDENTITY 易受觸發器、複寫干擾，回傳錯誤值。

方案 A：同一批次包含 INSERT 與 SCOPE_IDENTITY()

SET NOCOUNT ON;
INSERT INTO Products (ProductName) VALUES (N'Chalk');
SELECT SCOPE_IDENTITY() AS NewID;  -- 僅回傳目前範圍(Stored Proc/Trigger/Batch)產生的 IDENTITY

方案 B：OUTPUT 子句（SQL 2005+，最強健）

DECLARE @NewIDs TABLE (ID INT);
INSERT INTO Products (ProductName)
OUTPUT INSERTED.ProductID INTO @NewIDs
VALUES (N'Chalk');
SELECT ID FROM @NewIDs;  -- 支援多筆 INSERT、合併陳述式、避免觸發器干擾

方案 C：觸發器自動回傳（舊版相容）

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS
SELECT SCOPE_IDENTITY() AS NewID;  -- 任何 INSERT 皆自動回傳新 ID
GO

技巧二：內嵌檢視取代暫存表，減少 tempdb I/O

暫存表寫法（高 I/O）

CREATE TABLE #RecentOrders (OrderID INT, OrderDate DATETIME);
INSERT #RecentOrders SELECT TOP 5 OrderID, OrderDate FROM Orders ORDER BY OrderDate DESC;
SELECT p.ProductName, SUM(od.Quantity) AS Qty
FROM #RecentOrders r
JOIN [Order Details] od ON r.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName;
DROP TABLE #RecentOrders;

內嵌檢視寫法（零 tempdb 寫入，優化器可整體規劃）

SELECT p.ProductName, SUM(od.Quantity) AS Qty
FROM (
    SELECT TOP 5 OrderID, OrderDate FROM Orders ORDER BY OrderDate DESC
) r
JOIN [Order Details] od ON r.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName;

效能差異：內嵌檢視消除建立/插入/刪除暫存表的開銷，優化器可將子查詢與外部聯結整合為單一執行計畫，大幅降低邏輯讀取與 CPU。

技巧三：避免不必要的 LEFT JOIN 與 NULL 比較

為什麼 LEFT JOIN 昂貴

• 需保留左表所有列，即使右表無配對，仍需產生 NULL 延伸列。
• 阻礙優化器推導連結順序、下推述詞、使用索引求值。
• NULL 語意導致三值邏輯，WHERE col = NULL 永遠為 UNKNOWN，易寫錯條件。

重構策略

1. 預設值法：在右表建立「無分類」預設列，將 FK 設為 NOT NULL，改用 INNER JOIN。
2. TABLE 變數暫存左表鍵值，再 UPDATE 填入右表值（兩步驟但常比單一 LEFT JOIN 快）：

   DECLARE @Keys TABLE (ProductID INT PRIMARY KEY, CategoryName NVARCHAR(50));
   INSERT @Keys SELECT ProductID, N'' FROM Products;
   UPDATE k SET CategoryName = c.CategoryName
   FROM @Keys k JOIN Categories c ON k.ProductID = c.ProductID; -- 假設有對應關係表

3. 必要時才 LEFT JOIN：報表需顯示「無銷售月份」等完整維度時，見技巧四。

技巧四：善用 CROSS JOIN 產生完整維度矩陣（如月份×客戶）

場景

報表需顯示每位客戶每月銷售額，即使該月無銷售也要顯示 0，繪圖才不會斷點。

實作步驟

-- 1. 產生所有月份
DECLARE @Months TABLE (MonthKey CHAR(7) PRIMARY KEY); -- 'YYYY-MM'
DECLARE @Start DATE = '2023-01-01', @End DATE = '2023-12-31';
WHILE @Start <= @End
BEGIN
    INSERT @Months VALUES (FORMAT(@Start, 'yyyy-MM'));
    SET @Start = DATEADD(MONTH, 1, @Start);
END

-- 2. 取得有銷售的客戶
DECLARE @Customers TABLE (CustomerID NCHAR(5) PRIMARY KEY, CompanyName NVARCHAR(100));
INSERT @Customers
SELECT DISTINCT CustomerID, CompanyName
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

-- 3. CROSS JOIN 產生完整矩陣
DECLARE @Matrix TABLE (MonthKey CHAR(7), CustomerID NCHAR(5), CompanyName NVARCHAR(100), Sales MONEY DEFAULT 0);
INSERT @Matrix (MonthKey, CustomerID, CompanyName)
SELECT m.MonthKey, c.CustomerID, c.CompanyName
FROM @Months m CROSS JOIN @Customers c;

-- 4. 以實際銷售資料 UPDATE 矩陣
UPDATE m SET Sales = s.TotalSales
FROM @Matrix m
JOIN (
    SELECT 
        FORMAT(o.OrderDate, 'yyyy-MM') AS MonthKey,
        c.CustomerID,
        SUM(od.Quantity * od.UnitPrice) AS TotalSales
    FROM Orders o
    JOIN [Order Details] od ON o.OrderID = od.OrderID
    JOIN Customers c ON o.CustomerID = c.CustomerID
    WHERE o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY FORMAT(o.OrderDate, 'yyyy-MM'), c.CustomerID
) s ON m.MonthKey = s.MonthKey AND m.CustomerID = s.CustomerID;

-- 5. 最終報表
SELECT MonthKey, CustomerID, CompanyName, Sales
FROM @Matrix
ORDER BY CompanyName, MonthKey;

適用性判斷

• ✅ 適用：需呈現「完整維度組合」（時間×實體、門市×商品等），且組合數可控（< 100 萬列）。
• ❌ 不適用：單純為了篩選而用 CROSS JOIN 再加 WHERE/DISTINCT，此時 INNER JOIN 效能遠優於笛卡兒積再過濾。

拾遺補零：查詢撰寫小撇步

結語

沒有銀彈，只有持續的「測量 → 分析 → 重構 → 再測量」。將這四大技巧納入程式碼審查清單，配合執行計畫分析，能系統性提升 SQL Server 應用的擴展性與回應速度。