MSSQL索引檢視（indexed view）之簡述及使用

乍一聽到這個名字，可能感到有點陌生，這個物件是幹嘛的呢？原理是什麼？不用著急，我們看看下面的內容，慢慢就明白了。顧名思義，索引檢視就是建有索引的檢視，這是MSSQL提供的一項技術，用於提升某些SQL語句的查詢效能。索引檢視上的第一個索引必須是唯一簇索引，之後，可以在檢視上建立更多的非簇索引，因為檢視上的簇索引儲存方式和表上的簇索引一樣，因此，檢視上的簇索引可以提升某些查詢的效能。查詢最佳化器可以透過索引檢視來加速查詢的執行，同時，索引檢視的名字不必出現於相關查詢中。看到這裡，常用Oracle的同學是否開始看出了物化檢視（materialized view）的意思？，呵呵，繼續往下看。

聽起來不錯，原來MSSQL也提供了類似Oracle中物化檢視的東西，只是名字不同，先別高興，MSSQL的這個東西雖然不錯，但似乎限制和要求還挺多的，似乎比Oracle的物化檢視還多，接下來看看使用索引檢視時的要求吧。

1)    確保索引檢視參考的表的set options都是正確的；

2)    確保建立表和檢視前會話的set option是正確的；

3)    確保檢視定義是確定性的；

4)    確保要以WITH SCHEMABINDING option建立檢視；

5)    確保首先在檢視上建立唯一簇索引；

6)    上面提到的set options正確值如下所示：

?  ANSI_NULLS                       ON

?  ANSI_PADDING                     ON

?  ANSI_WARNINGS*                  ON

?  ARITHABORT                        ON

?  CONCAT_NULL_YIELDS_NULL       ON

?  NUMERIC_ROUNDABORF           OFF

?  QUOTED_IDENTIFIER                 ON

此外，還會有如下要求：

1)    執行CREATE INDEX命令的使用者必須是檢視的屬主； 

2)    建立索引時，IGNORE_DUP_KEY option必須被設定為OFF（預設值）；

3)    檢視定義中的表名必須包含模式名，例如：schema.tablename；

4)    檢視中參考的使用者自定義函式必須以WITH SCHEMABINDING option建立；

5)    檢視中參考的任何使用者定義的函式名必須包含模式名，例如： schema.function；

6)    使用者定義後函式的資料存取屬性必須是NO SQL，並且，外部存取屬性必須是NO；

7)    通用語言執行時Common language runtime (CLR)函式可以出現於檢視的select list中，但其不能是簇索引鍵列，同時，CLR函式也不能出現於檢視的where子句和連線操作的on子句中。

8)    檢視中CLR函式和CLR使用者自定義型別的方法必須是下列的屬性設定：

?  DETERMINISTIC = TRUE

?  PRECISE = TRUE

?  DATA ACCESS = NO SQL

?  EXTERNAL ACCESS = NO

9)    檢視必須以WITH SCHEMABINDING option建立；

10)  檢視必須僅參考同一個資料庫中的基表。檢視中不能參考其他檢視。

11)  檢視定義中的select語句不能包含下述T-SQL元素:

?  COUNTROWSET

?  functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)

?  OUTER joins (LEFT, RIGHT, or FULL)

?  Derived table (defined by specifying a SELECT statement in the FROM clause)

?  Self-joins

?  Specifying columns by using SELECT * or SELECT table_name.*

?  DISTINCT

?  STDEV, STDEVP, VAR, VARP, or AVG

?  Common table expression (CTE)

?  float*, text, ntext, image, XML, or filestream columns

?  Subquery

?  OVER clause, which includes ranking or aggregate window functions

?  Full-text predicates (CONTAIN, FREETEXT)

?  SUM function that references a nullable expression

?  ORDER BY

?  CLR user-defined aggregate function

?  TOP

?  CUBE, ROLLUP, or GROUPING SETS operators

?  MIN, MAX

?  UNION, EXCEPT, or INTERSECT operators

?  TABLESAMPLE

?  Table variables

?  OUTER APPLY or CROSS APPLY

?  PIVOT, UNPIVOT

?  Sparse column sets

?  Inline or multi-statement table-valued functions

?  OFFSET

?  CHECKSUM_AGG

 

12)  索引檢視可以包含float列，但這些列不能出現在簇索引中；

13)  如果存在GROUP BY ，則檢視定義必須包含COUNT_BIG(*)且一定不要包含HAVING。這些GROUP BY限制僅用於檢視定義中。一個查詢可以在其計劃中使用索引檢視即使並不滿足這條GROUP BY限制;

14)  如果檢視定義包含一個GROUP BY子句，則唯一簇索引只能參考GROUP BY子句中確定的列。

好了，前面說了這麼多限制和要求，讓人看了頭疼，不用想那麼嚴重，很多技術都會寫諸多的要求和限制，可我們不還是能自由自在的使用嗎？別光說不練了，看看下面我們怎麼建立和使用索引檢視吧。


USE AdventureWorks2012;  
GO  
--為了支援索引檢視設定相關options
SET NUMERIC_ROUNDABORT OFF;  
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,  
    QUOTED_IDENTIFIER, ANSI_NULLS ON;  
GO  
--以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 UNIQUE CLUSTERED INDEX IDX_V1   
    ON Sales.vOrders (OrderDate, ProductID);  
GO  
--該查詢能使用索引檢視即使FROM子句中並未確定該檢視
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  
--該查詢可以使用以上索引檢視  
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