Share via

建立索引檢視

  • 發行項

更新： 2006 年 12 月 12 日

在檢視上建立叢集索引之前，檢視必須符合下列需求：

  • 執行 CREATE VIEW 陳述式時，ANSI_NULLS 及 QUOTED_IDENTIFIER 選項必須已經設為 ON。OBJECTPROPERTY 函數透過 ExecIsAnsiNullsOn 或 ExecIsQuotedIdentOn 屬性來報告檢視的選項設定值。

  • 建立檢視要參考之資料表的所有 CREATE TABLE 陳述式執行時的 ANSI_NULLS 選項，必須已經設為 ON。

  • 檢視絕不能參考任何其他檢視，只能參考基底資料表。

  • 檢視參考的所有基底資料表必須和檢視位於同一個資料庫，且擁有者必須和檢視相同。

  • 檢視必須使用 SCHEMABINDING 選項來建立。結構描述繫結將檢視繫結到其基底資料表的結構描述中。

  • 檢視中所參考的使用者自訂函數，必須已用 SCHEMABINDING 選項來建立。

  • 資料表與使用者自訂函數，在檢視中必須以兩部分名稱來參考。不允許使用一部分、三部分以及四部分名稱。

  • 檢視中的運算式所參考的所有函數必須具有決定性。從 OBJECTPROPERTY 函數的 IsDeterministic 屬性可以得知使用者自訂函數是否具有決定性。如需詳細資訊，請參閱＜決定性與非決定性函數＞。

    ms191432.note(zh-tw,SQL.90).gif附註：
    當您在 SQL Server 2005 中參考索引檢視的 datetime 和 smalldatetime 字串常值時，我們建議您使用決定性的日期格式樣式，將常值明確轉換成您想要的日期類型。如需決定性的日期格式樣式清單，請參閱＜CAST 和 CONVERT (Transact-SQL)＞。除非相容性層級是設定為 80 或舊版，否則牽涉到將字元字串隱含轉換成 datetime 或 smalldatetime 的運算式是視為非決定性的。這是因為結果需視伺服器工作階段的 LANGUAGE 與 DATEFORMAT 設定而定。例如，運算式 CONVERT (datetime, '30 listopad 1996', 113) 的結果需視 LANGUAGE 設定而定，因為字串 'listopad' 在不同的語言中表示不同的月份。同樣地，在運算式 DATEADD(mm,3,'2000-12-01') 中，SQL Server 會根據 DATEFORMAT 設定解譯字串 '2000-12-01'。

    除非相容性層級是設定為 80 或舊版，否則非 Unicode 字元資料與定序之間的隱含轉換也是視為非決定性的。
    在包含這些運算式的檢視上建立索引不允許在 90 相容性模式。但是，包含這些來自升級資料庫的運算式的現有檢視是可以維護的。如果您使用包含字串到日期之隱含轉換的索引檢視，請確定資料庫和應用程式中 LANGUAGE 和 DATEFORMAT 設定是一致的，以避免索引檢視損毀。

  • 如果檢視定義使用彙總函數，SELECT 清單必須也包括 COUNT_BIG (*)。

  • 使用者自訂函數的資料存取屬性必須是 NO SQL，而外部存取屬性必須是 NO。

  • Common Language Runtime (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。

    如需有關如何設定 CLR 常式方法之屬性的詳細資訊，請參閱＜Custom Attributes for CLR Routines＞。

    ms191432.Caution(zh-tw,SQL.90).gif注意：
    我們不建議設定 CLR 常式方法 (與方法的功能抵觸) 的屬性。這麼做可能會導致資料損毀。

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

    • 使用 * 或 table_name**.*** 語法指定資料行。資料行名稱必須明確指出。

    • 作為簡單運算式來使用的資料表資料行名稱，不可在一個以上的檢視資料行中指定。如果對資料行的所有 (或一個) 參考是複雜運算式的一部分或函數的參數，就可以多次參考資料行。例如，下列 SELECT 清單是無效的：

      SELECT ColumnA, ColumnB, ColumnA

      此 SELECT 清單是有效的：

      SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB

    • 用於 GROUP BY 子句的資料行運算式，或彙總結果的運算式。

    • 衍生資料表。

    • 一般資料表運算式 (CTE)。

    • 資料列集函數。

    • UNION、EXCEPT 或 INTERSECT 運算子。

    • 子查詢。

    • 外部或自我聯結。

    • TOP 子句。

    • ORDER BY 子句。

    • DISTINCT 關鍵字。

    • COUNT (允許 COUNT_BIG(*))。

    • AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP 彙總函數。如果在查詢中指定 AVG(expression) 參考索引檢視，當檢視 SELECT 清單包含 SUM(expression) 和 COUNT_BIG(expression) 時，最佳化工具可以經常計算所需的結果。例如，索引檢視 SELECT 清單不得包含運算式 AVG(column1)。如果檢視 SELECT 清單包含運算式 SUM(column1) 與 COUNT_BIG(column1)，SQL Server 便可計算出參考檢視與指定之 AVG(column1) 的查詢平均值。

    • 參考可為 Null 值之運算式的 SUM 函數。

    • OVER 子句，其中包含次序或彙總視窗函數。

    • CLR 使用者自訂彙總函數。

    • 全文檢索述詞 CONTAINS 或 FREETEXT。

    • COMPUTE 或 COMPUTE BY 子句。

    • CROSS APPLY 或 OUTER APPLY 運算子。

    • PIVOT 或 UNPIVOT 運算子

    • 資料表提示 (僅適用相容性層級 90 或更高)

    • 聯結提示。

    • Xquery 運算式的直接參考。可以接受間接參考，例如結構描述繫結之使用者自訂函數內的 Xquery 運算式。

  • 如果已指定 GROUP BY，則檢視 SELECT 清單必須包含 COUNT_BIG(*) 運算式，而且檢視定義不得指定 HAVING、CUBE 或 ROLLUP。

CREATE INDEX 陳述式的要求

對檢視建立的第一個索引必須是唯一的叢集索引。建好唯一的叢集索引後，才可以建立其他非叢集索引。檢視上索引的命名慣例與資料表的索引一樣。唯一的差別在於是以檢視名稱取代資料表名稱。如需詳細資訊，請參閱＜CREATE INDEX (Transact-SQL)＞。

CREATE INDEX 陳述式必須符合下列要求，以及一般的 CREATE INDEX 要求：

  • 執行 CREATE INDEX 陳述式的使用者必須是檢視的擁有者。
  • 下列 SET 選項在 CREATE INDEX 陳述式執行時必須設為 ON：
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
  • NUMERIC_ROUNDABORT 選項必須設定為 OFF。這是預設值。
  • 如果資料庫是以 80 或更低的相容性模式來執行，ARITHABORT 選項必須設定為 ON。
  • 當您建立叢集或非叢集索引時，IGNORE_DUP_KEY 選項必須設定為 OFF (預設值)。
  • 檢視不得包含 text、ntext 或 image 資料行，即使 CREATE INDEX 陳述式中並未參考這些資料行。
  • 如果檢視定義中的 SELECT 陳述式指定 GROUP BY 子句，則唯一叢集索引的索引鍵便只能參考指定於 GROUP BY 子句中的資料行。
  • 構成索引鍵資料行值的不精確運算式必須參考檢視下基底資料表的儲存資料行。此資料行可能是一般的儲存資料行，或是保存的計算資料行。其他不精確運算式不能是索引檢視之索引鍵資料行的一部分。

考量因素

索引檢視中資料行的 large_value_types_out_of_row 選項設定是繼承基底資料表對應的資料行設定。此值可透過 sp_tableoption 設定。由運算式形成的資料行其預設值為 0。這表示大數值類型是儲存在 in-row。如需詳細資訊，請參閱＜使用大數值資料類型＞。

建立叢集索引之後，任何嘗試修改檢視之基底資料表的連接必須也要擁有建立檢視所需的相同選項設定。如果執行陳述式的連接沒有正確的選項設定，SQL Server 會產生錯誤，並且回復任何影響檢視之結果集的 INSERT、UPDATE 或 DELETE 陳述式。如需詳細資訊，請參閱＜影響結果的 SET 選項＞。

卸除檢視時會卸除檢視的所有索引。如果卸除叢集索引，也會卸除檢視的所有非叢集索引。但會保留使用者在檢視所建立的統計。每個非叢集索引則可以分別卸除。卸除檢視的叢集索引會刪除儲存的結果集，使最佳化工具回到以標準檢視的方式處理檢視。

雖然 CREATE UNIQUE CLUSTERED INDEX 陳述式中只指定構成叢集索引鍵的資料行，但資料庫中會儲存檢視完整的結果集。就如基底資料表的叢集索引一樣，叢集索引的 B 型樹狀目錄中只包含索引鍵資料行，但資料列則包含檢視結果集中的所有資料行。

如果您要在現有系統上的檢視中新增索引，則必須結構描述繫結您要放置索引的檢視。您可以執行下列作業：

  • 卸除檢視並指定 WITH SCHEMABINDING 來重建檢視。
  • 您可以建立一個與現有檢視具有相同文字，但不同名稱的第二個檢視。即使新的檢視並未被查詢的 FROM 子句直接參考，最佳化工具也會考慮使用位於其中的索引。
    ms191432.note(zh-tw,SQL.90).gif附註：
    除非卸除或變更檢視而使它不再具有結構描述繫結，否則無法卸除以 SCHEMABINDING 子句所建立之檢視中的參與檢視或資料表。此外，如果這些陳述式影響到檢視的定義，則在已繫結結構描述之檢視的參與資料表上執行 ALTER TABLE 陳述式將會失敗。

您必須確定新的檢視符合索引檢視的所有需求。您可能需要將檢視以及其參考的所有基底資料表的擁有權，變更為屬於同一位使用者所擁有。

您可以停用資料表與檢視的索引。停用資料表的叢集索引時，也會停用與資料表相關之檢視的索引。如需詳細資訊，請參閱＜停用索引＞。

範例

下列範例在該檢視上建立檢視和索引。內含使用索引檢視的兩項查詢。

USE AdventureWorks;
GO
--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;
GO
--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
--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 ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can 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 DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

請參閱

概念

設計索引檢視
疑難排解索引檢視的 DBCC 錯誤

其他資源

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)

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2006 年 12 月 12 日
新增內容：
  • 在檢視需求清單中新增索引檢視定義不允許 Xquery 運算式的直接參考。

2006 年 7 月 17 日
新增內容：
  • 新增如果 SELECT 陳述式包含 PIVOT 或 UNPIVOT 運算子，就無法索引檢視。

2006 年 4 月 14 日
新增內容：
  • 新增「範例」一節。
  • 新增如果資料庫是以 80 或更低的相容性模式來執行，ARITHABORT 選項必須設定為 ON 才能建立索引檢視的說明附註。
  • 新增如果檢視的定義包含次序或彙總視窗函數，就無法索引檢視。