SQL Server 索引視圖效能優化實戰（一）：建立與驗證

一般視圖僅為儲存的查詢定義，不佔儲存空間，每次查詢即時執行。索引視圖將結果集物化並建立聚集索引，查詢時直接掃描索引，大幅提升彙總查詢效能。

建立索引視圖的前置條件

• 視圖定義需符合 SCHEMABINDING、COUNT_BIG(*)、確定性函數等限制。
• Session 設定：ANSI_NULLS ON、ANSI_PADDING ON、ANSI_WARNINGS ON、CONCAT_NULL_YIELDS_NULL ON、QUOTED_IDENTIFIER ON、NUMERIC_ROUNDABORT OFF。
• 第一個索引必須為唯一聚集索引，才能物化視圖。

完整示範腳本

-- 1. 建立測試表並灌入 3 萬筆資料
CREATE TABLE SalesHistory (
    SaleID INT IDENTITY(1,1),
    Product VARCHAR(30),
    SaleDate SMALLDATETIME,
    SalePrice MONEY
);
GO

DECLARE @i SMALLINT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('Computer', DATEADD(mm, @i, '2006-03-10'), DATEPART(ms, GETDATE()) + @i + 57);
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('BigScreen', DATEADD(mm, @i, '2006-03-02'), DATEPART(ms, GETDATE()) + @i + 13);
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('PoolTable', DATEADD(mm, @i, '2007-02-11'), DATEPART(ms, GETDATE()) + @i + 29);
    SET @i += 1;
END
GO

-- 2. 一般視圖（無索引）
CREATE VIEW vw_SalesByProduct
AS
SELECT Product, COUNT_BIG(*) AS ProductCount, SUM(SalePrice) AS TotalSales
FROM dbo.SalesHistory
GROUP BY Product;
GO

-- 查詢計畫：Table Scan（全表掃描）
SELECT Product, TotalSales, ProductCount
FROM vw_SalesByProduct
WHERE Product = 'Computer';

-- 3. 索引視圖（加 SCHEMABINDING、ISNULL、COUNT_BIG）
SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON; SET QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF;
GO

CREATE VIEW dbo.vw_SalesByProduct_Indexed
WITH SCHEMABINDING
AS
SELECT Product,
       COUNT_BIG(*) AS ProductCount,
       SUM(ISNULL(SalePrice,0)) AS TotalSales
FROM dbo.SalesHistory
GROUP BY Product;
GO

-- 4. 建立唯一聚集索引 → 視圖物化
CREATE UNIQUE CLUSTERED INDEX idx_SalesView
ON vw_SalesByProduct_Indexed (Product);
GO

-- 5. 驗證空間佔用與查詢計畫
EXEC sp_spaceused 'vw_SalesByProduct_Indexed';
-- 查詢計畫：Clustered Index Seek（極快）
SELECT Product, TotalSales, ProductCount
FROM vw_SalesByProduct_Indexed
WHERE Product = 'Computer';

效果對比

下篇預告

索引視圖雖快，但維護成本高（基表異動需同步更新索引）。下篇將討論效能測試方法、適用場景判斷與權衡策略。