Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the MSSQL indexed view. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1) make sure that the set options of the table referenced by the indexed view is correct

2) make sure that the set option of the session before creating tables and views is correct

3) make sure that the view definition is deterministic

4) make sure you want to create a view in WITH SCHEMABINDING option

5) make sure that a unique cluster index is created on the view first

6) the correct values of set options mentioned above are as follows:

? ANSI_NULLS ON

? ANSI_PADDING ON

? ANSI_WARNINGS* ON

? ARITHABORT ON

? CONCAT_NULL_YIELDS_NULL ON

? NUMERIC_ROUNDABORF OFF

? QUOTED_IDENTIFIER ON

1) the user running the CREATE INDEX command must be the owner of the view

2) when creating an index, IGNORE_DUP_KEY option must be set to OFF (default)

3) the table name in the view definition must contain the schema name, for example: schema.tablename

4) user-defined functions referenced in the view must be created in WITH SCHEMABINDING option

5) any user-defined function name referenced in the view must contain the schema name, for example: schema.function

6) the data access property of the user-defined function must be NO SQL, and the external access property must be NO

7) the Common language runtime (CLR) function of the general language runtime can appear in the select list of the view, but it cannot be a cluster index key column, and the CLR function cannot appear in the where clause of the view and the on clause of the join operation.

8) the methods of the CLR function and CLR user-defined type in the view must be the following property settings:

? DETERMINISTIC = TRUE

? PRECISE = TRUE

? DATA ACCESS = NO SQL

? EXTERNAL ACCESS = NO

9) the view must be created in WITH SCHEMABINDING option

10) the view must reference only the base table in the same database. Other views cannot be referenced in the view.

11) the select statement in the view definition cannot contain the following T-SQL elements:

? 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) an indexed view can contain float columns, but these columns cannot appear in a cluster index

13) if a GROUP BY exists, the view definition must contain COUNT_BIG (*) and must not include HAVING. These GROUP BY restrictions are used only in view definitions. A query can use indexed views in its plan even if it does not meet this GROUP BY restriction

14) if the view definition contains a GROUP BY clause, the unique cluster index can only refer to the columns determined in the GROUP BY clause.

Well, the previous said so many restrictions and requirements, let people see a headache, do not think so serious, a lot of technology will write a lot of requirements and restrictions, but we can still use it freely? Instead of talking, take a look at how we create and use indexed views below.

USE AdventureWorks2012

GO

-- to support indexed view settings related options

SET NUMERIC_ROUNDABORT OFF

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT

QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

-- create indexed views with 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 the first unique cluster index on the view

CREATE UNIQUE CLUSTERED INDEX IDX_V1

ON Sales.vOrders (OrderDate, ProductID)

GO

This query can use indexed views even if the view is not determined in the FROM clause

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

-- the query can use the above indexed view

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

This is the end of this article on "how to use MSSQL indexed View". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.