建立索引檢視表

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure 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 選項設定為 [必要值] 資料行中顯示的值：

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

OLE DB 與 ODBC 值		 預設

DB-Library 值
ANSI_NULLS 開啟 開啟 開啟 OFF
ANSI_PADDING 開啟 開啟 開啟 OFF
ANSI_WARNINGS 1 開啟 開啟 開啟 OFF
ARITHABORT 開啟 開啟 OFF OFF
CONCAT_NULL_YIELDS_NULL 開啟 開啟 開啟 OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER 開啟 開啟 開啟 OFF

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

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

重要

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

確定性檢視需求

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

若要判斷檢視資料行是否具確定性，請使用 COLUMNPROPERTY 函數的 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 子句或檢視中 JOIN 運算的 ON 子句中。

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

    屬性 注意
    DETERMINISTIC = TRUE 必須明確宣告為 Microsoft .NET Framework 方法的屬性。
    PRECISE = TRUE 必須明確宣告為 .NET Framework 方法的屬性。
    DATA ACCESS = NO SQL 透過將 DataAccess 屬性設定為 DataAccessKind.None，並將 SystemDataAccess 屬性設定為 SystemDataAccessKind.None 來決定。
    EXTERNAL ACCESS = NO 對於 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)
    參考可為 Null 運算式的 SUM 函數 在 ISNULL 內使用 SUM() 使運算式不可為 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 子句中使用 WHERE、AND NOT 和 AND
    ORDER BY ORDER BY
    ORDER BY OFFSET
    來源資料行類型 可能的替代方案
    已取代的大型數值資料行類型 text、ntext 和 image 將資料行分別移轉至 varchar(max)、nvarchar(max) 和 varbinary(max)。
    xml 或 FILESTREAM 資料行
    索引鍵中的 float1 資料行
    疏鬆資料行集

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

    重要

    時態查詢 (使用 FOR SYSTEM_TIME 子句的查詢) 不支援索引檢視表。

datetime 和 smalldatetime 建議

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

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

索引檢視表的效能考量

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

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

各種其他考量

  • 索引檢視表中資料行的 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
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
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 2016 (13.x) Service Pack 1 之前，只有特定版本的 SQL Server 支援由查詢最佳化工具自動使用索引檢視表。 在 SQL Server Standard 版本上，您必須使用 NOEXPAND 查詢提示直接查詢索引檢視表。 自 SQL Server 2016 (13.x) Service Pack 1 以來，所有版本都支援自動使用索引檢視表。 Azure SQL 資料庫和 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 (TRANSACT-SQL)。

下一步