MSSQL索引视图怎么用

2025-03-12 技术教程

这篇文章将为大家详细讲解有关MSSQL索引视图怎么用，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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)运行CREATEINDEX命令的用户必须是视图的属主；

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)函数可以出现于视图的selectlist中，但其不能是簇索引键列，同时，CLR函数也不能出现于视图的where子句和连接操作的on子句中。

8)视图中CLR函数和CLR用户自定义类型的方法必须是下列的属性设置：

? DETERMINISTIC =TRUE

? PRECISE = TRUE

? DATA ACCESS = NOSQL

? 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 aSELECT statement in the FROM clause)

? Self-joins

? Specifying columns by using SELECT * orSELECT table_name.*

? DISTINCT

? STDEV, STDEVP, VAR, VARP, or AVG

? Common table expression (CTE)

? float*, text, ntext, image, XML, orfilestream columns

? Subquery

? OVER clause, which includes ranking oraggregate window functions

? Full-text predicates (CONTAIN, FREETEXT)

? SUM function that references a nullableexpression

? 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-valuedfunctions

? OFFSET

? CHECKSUM_AGG

12) 索引视图可以包含float列，但这些列不能出现在簇索引中；

13) 如果存在GROUP BY ，则视图定义必须包含COUNT_BIG(*)且一定不要包含HAVING。这些GROUP BY限制仅用于视图定义中。一个查询可以在其计划中使用索引视图即使并不满足这条GROUPBY限制;

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)) ASRevenue,
OrderDate, ProductID, COUNT_BIG(*) ASCOUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader ASo
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 ONod.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)) ASRev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ONod.SalesOrderID=o.SalesOrderID
AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO