SQL Server 2005 索引檢視表進階：限制條件、需求與範例

建立索引檢視表的強制限制

檢視表定義 不得包含 以下構造：

• TOP, DISTINCT, UNION, OUTER JOIN, SELF JOIN, SUBQUERY, DERIVED TABLE
• 彙總函數：AVG, STDEV, VAR, COUNT(*) (需用 COUNT_BIG(*))
• 非確定性運算式、CONVERT 產生非精確結果、浮點數運算
• COMPUTE, ORDER BY (除非搭配 TOP，但 TOP 也被禁止)
• 全文謂詞 (CONTAINS, FREETEXT)、OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML
• 參考已停用叢集索引的基表、跨資料庫參考、參考其他檢視表
• text/ntext/image/filestream/xml 欄位、TABLE HINT (如 NOLOCK)
• 可為 NULL 運算式上的 SUM

GROUP BY 額外限制

• 必須包含 COUNT_BIG(*)。
• 不得有 HAVING, CUBE, ROLLUP, GROUPING()。

註：查詢優化器在符合條件時仍可使用索引檢視表，即使查詢本身不符合上述限制。

索引建立需求

1. 執行者必須為檢視表擁有者。
2. 唯一叢集索引鍵僅能引用 GROUP BY 列。
3. 不得啟用 IGNORE_DUP_KEY。

會話必要 SET 選項 (建立索引前後必須一致)

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON

範例：銷售彙總索引檢視表

-- 基表: SalesOrderDetail (OrderID, ProductID, Qty, UnitPrice)
CREATE VIEW dbo.SalesSummary
WITH SCHEMABINDING
AS
SELECT ProductID,
       COUNT_BIG(*) AS Cnt,
       SUM(Qty) AS TotalQty,
       SUM(Qty * UnitPrice) AS TotalAmount
FROM dbo.SalesOrderDetail
GROUP BY ProductID
GO

-- 建立唯一叢集索引
CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary_ProductID
ON dbo.SalesSummary (ProductID)
GO

-- 查詢自動使用索引檢視表 (即使未直接查詢 View)
SELECT ProductID, SUM(Qty) FROM SalesOrderDetail GROUP BY ProductID
-- 優化器將改用 IX_SalesSummary_ProductID 掃描，避免掃描大表

效能效益

• 預先計算彙總，查詢從掃描百萬列變為掃描數千列索引。
• 適合「寫少讀多」的報表/儀表板場景。
• 維護成本：每次基表 DML 需同步更新索引檢視表，寫入效能下降 ~10-20%。