MSSQL索引视图怎么用

142次阅读

没有评论

共计 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