MSSQL索引視圖怎么用

191次閱讀

沒有評論

共計 3388 個字符，預計需要花費 9 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 MSSQL 索引視圖怎么用，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

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