您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

五大提升 SQL 效能實戰技巧 (下)：完整程式碼範例與詳細解析

編輯：關於SqlServer

承接上篇，本文提供完整 T-SQL 程式碼範例，詳細展示各優化技巧的實作細節與效能比較。

1. 從 INSERT 取回 IDENTITY：觸發器自動化方案

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS
SELECT @@IDENTITY
GO

觸發器僅在 Products 表發生 INSERT 時啟動，確保每次成功插入皆自動回傳新身分識別值，應用程式端呼叫方式統一。

2. 內嵌檢視表 vs 暫存表：完整對比

暫存表版本 (高 I/O)

CREATE TABLE #Temp1 (OrderID INT NOT NULL, OrderDate DATETIME NOT NULL);
INSERT INTO #Temp1 SELECT TOP 5 OrderID, OrderDate FROM Orders ORDER BY OrderDate DESC;
SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity
FROM #Temp1 t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName ORDER BY p.ProductName;
DROP TABLE #Temp1;

內嵌檢視表版本 (低資源、短語法)

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

內嵌檢視表避免 tempdb 大量讀寫，資源消耗顯著降低。

3. 避免 LEFT JOIN：資料庫設計層面優化

若 Products 表的 CategoryID 允許 NULL，查詢所有產品含分類需 LEFT JOIN。改為建立「無分類」預設分類 (CategoryID = 999)，外鍵設 NOT NULL，即可改用 INNER JOIN：

-- 修改前
SELECT p.ProductName, c.CategoryName
FROM Products p LEFT JOIN Categories c ON p.CategoryID = c.CategoryID;

-- 修改後 (需資料庫設計配合)
SELECT p.ProductName, c.CategoryName
FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID;

此設計消除資源密集型 LEFT JOIN，對高併發系統效能提升顯著。

4. CROSS JOIN 實戰：每月銷售報表含零值月份

需求：列出過去 12 個月每位客戶的銷售額，含零銷售月份。

步驟 1：建立月份表 (@tblMonths)

DECLARE @tblMonths TABLE (sMonth VARCHAR(7));
DECLARE @i INT = 0, @dtEndDate DATETIME = '1997-05-05', @dtDate DATETIME;
SET @dtEndDate = DATEADD(DD, -1, CAST(CAST(MONTH(@dtEndDate)+1 AS VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + ' 23:59:59' AS DATETIME));
SET @dtStartDate = DATEADD(MM, -12, @dtEndDate);
WHILE @i < 12 BEGIN
    SET @dtDate = DATEADD(MM, -@i, @dtEndDate);
    INSERT @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR(4)) + '-' + RIGHT('0'+CAST(MONTH(@dtDate) AS VARCHAR(2)),2);
    SET @i = @i + 1;
END

步驟 2：建立有交易客戶表 (@tblCustomers)

DECLARE @tblCustomers TABLE (CustomerID CHAR(10), CompanyName VARCHAR(50), ContactName VARCHAR(50));
INSERT @tblCustomers SELECT DISTINCT c.CustomerID, c.CompanyName, c.ContactName
FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate;

步驟 3：CROSS JOIN 產生完整矩陣 (@tblFinal)

DECLARE @tblFinal TABLE (sMonth VARCHAR(7), CustomerID CHAR(10), CompanyName VARCHAR(50), ContactName VARCHAR(50), mSales MONEY);
INSERT @tblFinal SELECT m.sMonth, c.CustomerID, c.CompanyName, c.ContactName, 0
FROM @tblMonths m CROSS JOIN @tblCustomers c;

步驟 4：以實際銷售資料更新

UPDATE f SET mSales = mydata.mSales
FROM @tblFinal f
INNER JOIN (
    SELECT c.CustomerID,
           CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' + RIGHT('0'+CAST(MONTH(o.OrderDate) AS VARCHAR(2)),2) AS sMonth,
           SUM(od.Quantity * od.UnitPrice) AS mSales
    FROM Customers c
    INNER JOIN Orders o ON c.CustomerID = o.CustomerID
    INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
    WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate
    GROUP BY c.CustomerID, CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' + RIGHT('0'+CAST(MONTH(o.OrderDate) AS VARCHAR(2)),2)
) mydata ON f.CustomerID = mydata.CustomerID AND f.sMonth = mydata.sMonth;
SELECT * FROM @tblFinal ORDER BY CompanyName, sMonth;

此法一次產生 12個月 × 81客戶 = 972 列完整矩陣，完美解決報表零值顯示問題。