建立索引檢視表

適用于:yesSQL Server (所有支援的版本) Yes Azure SQL Database

此文章會說明如何在檢視上建立索引。 對檢視建立的第一個索引必須是唯一的叢集索引。 建好唯一的叢集索引後,才可以建立其他非叢集索引。 為檢視表建立唯一的叢集索引,可以提升查詢效能,因為檢視表儲存在資料庫中的方式與包含叢集索引之資料表的儲存方式一樣。 查詢最佳化工具可以利用索引檢視表來加快查詢執行的速度。 不必在查詢中參考此檢視表,最佳化工具仍會考慮以該檢視表做為替代方式。

步驟

以下是建立索引檢視表所需要的步驟,這些步驟對於能否順利完成索引檢視表的實作是很重要的:

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

重要

在大量索引檢視表或較少但非常複雜的索引檢視表所參考的資料表上執行 DML1 時,這些參考的索引檢視表必須一併更新。 如此一來,DML 查詢效能可能會降低顯著,在某些情況下,甚至無法產生查詢計畫。 在這種情況下,請在生產環境使用之前測試 DML 查詢、分析查詢計畫並微調/簡化 DML 陳述式。

1 例如 UPDATE、DELETE 或 INSERT 作業。

索引檢視的必要 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_WARNINGS1 開啟 開啟 開啟 OFF
ARITHABORT 開啟 開啟 OFF OFF
CONCAT_NULL_YIELDS_NULL 開啟 開啟 開啟 OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER 開啟 開啟 開啟 OFF
         

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

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

重要

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

決定性檢視需求

索引檢視表的定義必須具決定性。 如果選取清單及 WHEREGROUP BY 子句中的所有運算式都具決定性,則檢視表也具決定性。 每當利用一組特定的輸入值來評估具決定性的運算式時,具決定性的運算式一律傳回相同的結果。 只有具決定性的函數可以參與具決定性的運算式。 例如,DATEADD 函式具決定性,因為它會針對它的三個參數之任何一組給定的引數值一律傳回相同的結果。 GETDATE 不具決定性,因為它一律被相同的引數叫用,但是,每當它被執行時,它所傳回的值都會變更。

若要判斷檢視表資料行是否具決定性,請使用 COLUMNPROPERTY 函數的 IsDeterministic 屬性。 如果檢視表含有結構描述繫結,您可以利用 COLUMNPROPERTY 函式的 IsPrecise 屬性來判斷其中具決定性的資料行是否精確。 如果是 TRUE,COLUMNPROPERTY 會傳回 1;如果是 FALSE,則傳回 0;如果輸入無效,則傳回 NULL。 這表示這個資料行不具決定性或不是精確資料行。

即使運算式具決定性,如果它包含浮點運算式,確切的結果仍取決於處理器架構或微碼的版本而定。 若要確保資料完整性,這類運算式在參與時可以只做為索引檢視表的非索引鍵資料行。 未含浮點運算式之具決定性的運算式稱為精確運算式。 只有精確的具決定性運算式可以參與索引鍵資料行和索引檢視表的 WHEREGROUP BY 子句。

其他需求

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

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

  • 當您建立索引時, IGNORE_DUP_KEY 索引選項必須設定為 OFF (預設設定) 。

  • 在檢視定義中,兩部分名稱 schema.tablename 必須參考資料表。

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

  • 檢視中所參考的任何使用者定義函式都必須由兩部分名稱< 架構 >參考< 函式 >

  • 使用者定義函式的資料存取屬性必須是 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。
       
  • 檢視表必須使用 WITH SCHEMABINDING 選項來建立。

  • 檢視必須只參考與檢視位於相同資料庫中的基底資料表。 檢視不可參考其他檢視。

  • 如果有 GROUP BY,VIEW 定義必須包含 COUNT_BIG(*),且不能包含 HAVINGGROUP BY 限制只適用於索引檢視表定義。 即使索引檢視表不符合這些 GROUP BY 限制,查詢還是可以在它的執行計畫中使用索引檢視表。

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

  • 檢視定義中的 SELECT 陳述式不得包含下列 Transact-SQL 元素:

    Transact-SQL 元素 (繼續) (繼續)
    COUNT ROWSET 函式 (OPENDATASOURCEOPENQUERYOPENROWSETOPENXML) OUTER 聯結 (LEFTRIGHTFULL)
    衍生資料表 (藉由在 FROM 子句中指定 SELECT 陳述式所定義) 自我聯結 使用 SELECT *SELECT <table_name>.* 指定資料行
    DISTINCT STDEVSTDEVPVARVARPAVG 通用資料表運算式 (CTE)
    float1textntextimageXMLfilestream 資料行 子查詢 OVER 子句,其中包含次序或彙總視窗函式
    全文檢索述詞 (CONTAINSFREETEXT) SUM 函式,參考可為 Null 值的運算式 ORDER BY
    CLR 使用者定義彙總函式 TOP CUBEROLLUPGROUPING SETS 運算子
    MIN, MAX UNIONEXCEPTINTERSECT 運算子 TABLESAMPLE
    資料表變數 OUTER APPLYCROSS APPLY PIVOT, UNPIVOT
    疏鬆資料行集合 內嵌 (TVF) 或多重陳述式資料表值函式 (MSTVF) OFFSET
    CHECKSUM_AGG STRING_AGG

    1 索引檢視表可以包含 float 資料行;不過,這類資料行不能併入叢集索引鍵中。

    重要

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

Datetime/Smalldatetime 建議

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

牽涉到將字元字串隱含轉換成 datetimesmalldatetime 的運算式被視為非決定性的。 如需詳細資訊,請參閱將常值日期字串轉換成 DATE 值的非決定性轉換

索引檢視表的效能考慮

在大量索引檢視表或較少但非常複雜的索引檢視表所參考的資料表上執行 DML (例如 UPDATEDELETEINSERT) 時,這些索引檢視表必須在 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
--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 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 o.OrderDate >= CONVERT(datetime,'03/01/2012',101)
      AND o.OrderDate < CONVERT(datetime,'04/01/2012',101)
    GROUP BY OrderDate
    ORDER BY OrderDate ASC;

如需詳細資訊,請參閱 CREATE VIEW (TRANSACT-SQL)

另請參閱