建立索引檢視表

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體

本文說明如何在檢視上建立索引。 檢視上建立的第一個索引必須是唯一的叢集索引。 在建立唯一的叢集索引後，您可以建立更多非叢集索引。 在檢視上建立唯一叢集索引可提升查詢效能，因為檢視在資料庫中的儲存方式與具有叢集索引的資料表的儲存方式相同。 查詢最佳化工具可以使用索引檢視表來加速查詢執行。 最佳化工具不必在查詢中參考此檢視即可考慮該檢視進行替代。

步驟

建立索引檢視表需要下列步驟，而且對索引檢視表的成功實作至關重要：

1. 確認檢視中將參考的所有現有資料表的 SET 選項正確。
2. 在您建立任何資料表和檢視之前，請先確認工作階段的 SET 選項已正確設定。
3. 確認檢視定義具確定性。
4. 請確認基底資料表的擁有者與檢視的擁有者相同。
5. 透過使用 WITH SCHEMABINDING 選項建立檢視。
6. 在檢視上建立唯一的叢集索引。

在對大量的索引檢視表或較少而複雜的索引檢視表所參考的資料表上執行 UPDATE、DELETE 或 INSERT 作業（資料操作語言或 DML）時，這些參考的索引檢視表也必須更新。 因此，DML 查詢效能可能會大幅降低，在某些情況下，甚至無法產生查詢計劃。

在這種情況下，請在生產使用之前測試 DML 查詢、分析查詢計劃並微調/簡化 DML 陳述式。

索引檢視表所需必備的 SET 選項

在執行查詢的情況下啟用不同的 SET 選項時，評估相同的運算式可能會在資料庫引擎中產生不同的結果。 例如，在 SET 選項 CONCAT_NULL_YIELDS_NULL 設定為 ON 之後，運算式 'abc' + NULL 會傳回值 NULL。 但是，在 CONCAT_NULL_YIELDS_NULL 設定為 OFF 之後，相同運算式會產生 abc。

為了確保檢視可以正確維護並傳回一致的結果，索引檢視表需要數個 SET 選項的固定值。 每當發生下列狀況時，必須將下表中的 SET 選項設定為 Required value 資料行中顯示的值：

• 建立檢視和檢視上的後續索引。
• 在建立檢視時檢視中參考的基底資料表。
• 當在參與索引檢視表的任何資料表上執行插入、更新或刪除作業時。 此需求包括大量複製、複寫和分散式查詢等作業。
• 查詢最佳化工具會使用索引檢視表來產生查詢計劃。

設定選項	必填值	預設伺服器值	預設 OLE DB 與 ODBC 值	預設 DB-Library 值
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS 1	ON	ON	ON	OFF
ARITHABORT	ON	ON	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	ON	ON	OFF

¹ 將 ANSI_WARNINGS 設定為 ON 會隱含將 ARITHABORT 設定為 ON。

如果您使用的是 OLE DB 或 ODBC 伺服器連線，必須修改的唯一值是 ARITHABORT 設定。 所有 DB-Library 值必須在伺服器層級使用 sp_configure 或使用 SET 命令在應用程式中正確設定。

重要

我們強烈建議您在伺服器上的任何資料庫中建立第一個索引檢視表或計算資料行上的索引後，立即將 ARITHABORT 使用者選項設定為 ON 伺服器範圍。

確定性視圖需求

索引檢視表的定義必須具確定性。 如果選取清單中的所有運算式以及 WHERE 和 GROUP BY 子句都具確定性，則檢視具確定性。 確定性運算式在使用一組特定的輸入值進行計算時，一律會傳回相同的結果。 只有決定性函數才能參與決定性運算式。 例如，DATEADD 函數具確定性，因為對於其三個參數的任何指定引數值集，它一律會傳回相同的結果。 GETDATE 不具確定性，因為它一律使用相同的引數叫用，但它傳回的值在每次執行時都會變更。

若要判斷檢視資料行是否具確定性，請使用 IsDeterministic 函數的 屬性。 若要判斷具有結構描述繫結的檢視中的確定性資料行是否精確，請使用 IsPrecise 函數的 COLUMNPROPERTY 屬性。 如果為 COLUMNPROPERTY，1 傳回 TRUE；如果為 0，則傳回 FALSE；如果輸入無效，則傳回 NULL。 這表示資料行不確定或不精確。

即使運算式具確定性，如果它包含 float 運算式，確切的結果可能取決於處理器架構或微碼版本。 為了確保資料完整性，此類運算式只能作為索引檢視表中的非主鍵資料行使用。 不包含 float 運算式的確定性運算式稱為精確運算式。 只有精確的確定運算式才能參與鍵欄以及索引檢視表的 WHERE 或 GROUP BY 子句。

其他需求

除了 SET 選項和決定性函數需求之外，還必須符合下列需求

• 執行 CREATE INDEX 的使用者必須是檢視的擁有者。

• 在建立索引時，IGNORE_DUP_KEY 索引選項必須設定為 OFF (預設設定)。

• 在檢視定義中，必須以由兩部分組成的名稱來引用資料表 <schema>.<tablename>。

• 檢視中參考的使用者定義函數必須使用 WITH SCHEMABINDING 選項來建立。

• 檢視中參考的任何使用者定義函數必須由兩部分組成的名稱 <schema>.<function> 參考。

• 使用者定義函數的資料存取屬性必須是 NO SQL，且外部存取屬性必須是 NO。

• 通用語言執行平台 (CLR) 函數可以顯示在檢視的選取清單中，但不能是叢集索引鍵定義的一部分。 CLR 函數不能顯示在檢視的 WHERE 子句或檢視中 ON 運算的 JOIN 子句中。

• 檢視定義中使用的 CLR 使用者定義型別的 CLR 函數和方法必須具有如下表所示的屬性集。

  財產	注意
  DETERMINISTIC = TRUE	必須明確宣告為 Microsoft .NET Framework 方法的屬性。
  PRECISE = TRUE (精確 = 真)	必須明確宣告為 .NET Framework 方法的屬性。
  資料存取 = 無 SQL	透過將 DataAccess 屬性設定為 DataAccessKind.None，並將 SystemDataAccess 屬性設定為 SystemDataAccessKind.None 來決定。
  禁止外部訪問 = 否	對於 CLR 常式，此屬性預設為 NO。

• 此檢視必須使用 WITH SCHEMABINDING 選項建立。

• 此檢視必須僅參考與該檢視位於相同資料庫中的基底資料表。 此檢視無法參考其他檢視。

• 如果 GROUP BY 存在，則 VIEW 定義必須包含 COUNT_BIG(*)，且不得包含 HAVING。 這些 GROUP BY 限制僅適用於索引檢視表定義。 即使查詢不符合這些 GROUP BY 限制，查詢仍可在執行計畫中使用索引檢視表。

• 如果檢視定義包含 GROUP BY 子句，則唯一叢集索引的索引鍵只能參考 GROUP BY 子句中指定的資料行。

• 檢視定義中的 SELECT 陳述式不得包含下列 Transact-SQL 語法：

  Transact-SQL 函式	可能的替代方案
  COUNT	使用 COUNT_BIG
  ROWSET 函式 (OPENDATASOURCE、OPENQUERY、OPENROWSET 和 OPENXML)
  算術平均數 (AVG)	使用 COUNT_BIG 和 SUM 作為個別欄
  統計彙總函數 (STDEV、STDEVP、VAR 和 VARP)
  SUM 函數參考可為空值的運算式	在 SUM() 內使用 ISNULL 使表達式不可為 Null。
  其他彙總函式 (MIN、MAX、CHECKSUM_AGG 和 STRING_AGG)
  使用者定義彙總函式 (SQL CLR)

  SELECT 子句	Transact-SQL 元素	可能的替代方案
  WITH cte AS	通用資料表運算式 (CTE) WITH
  SELECT	子查詢
  SELECT	SELECT [ <table>. ] *	明確命名資料行
  SELECT	SELECT DISTINCT	使用 GROUP BY
  SELECT	SELECT TOP
  SELECT	OVER 子句，其中包含排名或匯總視窗函式
  FROM	LEFT OUTER JOIN
  FROM	RIGHT OUTER JOIN
  FROM	FULL OUTER JOIN
  FROM	OUTER APPLY
  FROM	CROSS APPLY
  FROM	衍生資料表運算式 (也就是在 SELECT 子句中使用 FROM)
  FROM	自我連接
  FROM	資料表變數
  FROM	內嵌資料表值函式
  FROM	多重陳述式資料表值函式
  FROM	PIVOT，UNPIVOT
  FROM	TABLESAMPLE
  FROM	FOR SYSTEM_TIME	直接查詢時態性記錄資料表
  WHERE	全文檢索述詞 (CONTAINS、FREETEXT、CONTAINSTABLE、FREETEXTTABLE)
  GROUP BY	CUBE、ROLLUP 或 GROUPING SETS 運算子	為每個 GROUP BY 欄位組合定義個別的索引檢視表
  GROUP BY	HAVING
  設定運算子	UNION, UNION ALL, EXCEPT, INTERSECT	分別在 OR 子句中使用 AND NOT、AND 和 WHERE
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  來源資料行類型	可能的替代方案
  已取代的大型數值資料行類型 (text、ntext 和 image)	將資料行分別移轉至 varchar(max)、nvarchar(max) 和 varbinary(max)。
  xml 或 FILESTREAM 欄
  索引鍵中的 float1 欄位
  稀疏列集

  ¹ 索引檢視表可以包含 float 資料行；但是，此類資料行不能包含在叢集索引鍵中。

  重要

  使用時態查詢（使用 FOR SYSTEM_TIME 子句的查詢）不支援索引檢視。

datetime 和 smalldatetime 的使用建議

在索引檢視表中參考 datetime 和 smalldatetime 字串常值時，建議使用具有確定性的日期格式樣式，將常值明確轉換成想要的日期類型。 如需具決定性之日期格式樣式的清單，請參閱 CAST 和 CONVERT。 如需有關確定性和非確定性運算式的詳細資訊，請參閱此頁面中的考量一節。

涉及將字元字串隱含轉換為 datetime 或 smalldatetime 的運算式視為非確定性。 如需詳細資訊，請參閱將常值日期字串轉換成 DATE 值的非決定性轉換。

索引檢視表的效能考量

當您在由大量索引檢視表或較少但複雜的索引檢視表所參考的資料表上執行 DML (例如 UPDATE、DELETE 或 INSERT) 時，這些索引檢視表也必須在 DML 執行期間更新。 因此，DML 查詢效能可能會大幅降低，在某些情況下，甚至無法產生查詢計劃。 在這種情況下，請在生產使用之前測試 DML 查詢、分析查詢計劃並微調/簡化 DML 陳述式。

若要防止資料庫引擎使用索引檢視表，請在查詢中包含 OPTION (EXPAND VIEWS) 提示。 此外，如果任何列出的選項設定不正確，這會阻止最佳化工具使用檢視上的索引。 如需有關 OPTION (EXPAND VIEWS) 提示的詳細資訊，請參閱 SELECT。

其他考量

• 索引檢視表中資料行的 large_value_types_out_of_row 選項設定繼承自基底資料表中對應資料行的設定。 此值使用 sp_tableoption 來設定。 從運算式形成的欄的預設設定為 0。 這表示大型實值型別以資料列方式儲存。

• 索引視圖可以建立在分區資料表上，且索引視圖本身也可以分區。

• 刪除檢視時，檢視上的所有索引都會刪除。 在卸除叢集索引時，也會卸除檢視上的所有非叢集索引和自動建立的統計資料。 維護檢視上的使用者建立的統計資料。 非叢集索引可以個別卸除。 卸除檢視上的叢集索引會移除儲存的結果集，而最佳化工具會回到像處理標準檢視一樣處理此檢視。

• 可以停用資料表和檢視上的索引。 停用資料表上的叢集索引時，也會停用與資料表關聯的檢視上的索引。

權限

若要建立檢視，使用者需要在資料庫中擁有 CREATE VIEW 權限，並在建立檢視的結構描述上擁有 ALTER 權限。 如果基底資料表駐留在不同的結構描述中，則至少需要此資料表上的 REFERENCES 權限。 如果建立索引的使用者與建立檢視的使用者不同，那麼僅建立索引時，需在檢視上擁有 ALTER 權限（由結構描述上的 ALTER 涵蓋）。

只有在檢視的擁有者與參考的資料表相同的情況下，才能建立索引。 此概念也稱為檢視與資料表之間的完整擁有權鏈結。 通常，當資料表和檢視駐留在相同結構描述內時，相同結構描述擁有者適用於此結構描述內的所有物件。 因此，可以建立檢視，而不一定是該檢視的擁有者。 另一方面，結構描述內的個別物件也具有不同的明確擁有者。 如果擁有者與結構描述擁有者不同，於 sys.tables 中的 principal_id 列會包含值。

建立索引檢視表：T-SQL 範例

下列範例會在 AdventureWorks 資料庫中的該檢視上建立檢視和索引。

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;

--Create view with SCHEMABINDING.
IF OBJECT_ID('Sales.vOrders', 'view') IS NOT NULL
    DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
    WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate,
    ProductID,
    COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od,
    Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate,
    ProductID;
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (
    OrderDate,
    ProductID
);
GO

接下來的兩個查詢會展示如何使用索引檢視圖，儘管該檢視未在 FROM 子句中指定。

--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
    OrderDate,
    ProductID
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700
        AND 800
GROUP BY OrderDate,
    ProductID
ORDER BY Rev DESC;
GO

--This query will also use the above indexed view.
SELECT OrderDate,
    SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
        AND o.OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
GROUP BY OrderDate
ORDER BY OrderDate ASC;

最後，此範例顯示直接從索引檢視表進行查詢。 查詢優化器自動使用已編索引檢視表僅在 SQL Server 的特定版本中受支援。 在 SQL Server Standard 版本上，您必須使用 NOEXPAND 查詢提示直接查詢索引檢視表。 Azure SQL Database 和 Azure SQL 受控實例支持自動使用索引檢視，而不需指定 NOEXPAND 提示。 如需詳細資訊，請參閱資料表提示 (Transact-SQL)。

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

如需詳細資訊，請參閱 CREATE VIEW。

相關內容

• CREATE INDEX (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• SET ANSI_PADDING (Transact-SQL)
• SET ANSI_WARNINGS (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• SET NUMERIC_ROUNDABORT (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)