1. 首页
  2. IT资讯

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  

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8484829/viewspace-2150067/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/184878.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code