SQL Server index-index (materialized) View < Nineth >

first, the basic concept of indexed views

An indexed view is actually a view that "materialized" a set of unique values into a clustered index, which is materialized almost as a table, and its data is stored in one copy (it consumes hard disk space, but the query is fast, for example, you can set count (), sum () in an indexed view). The advantage is that it provides a very quick way to find the information behind the view. After the first index (a clustered index that must be for a unique set of values) , SQL Server can also establish additional indexes on the view by using the clustered key from the first index as the reference point. The restrictions are as follows:

1. The view must use the SCHEMABINDING option;
2. If the view references any user-defined functions, then these functions must also be schema-bound;
3. Views cannot reference any other view-only tables and UDFs can be referenced;
4. All tables and UDFs referenced in the view must take two-part naming conventions such as: dbo. Customers), and must also have the same owner as the view;
5. All objects referenced by views and views must be in the same database;
6. The ANSI_NULLS and QUOTED_IDENTIFIER options must be turned on when creating the view and all the underlying tables;
7. Any function that the view references must be deterministic;

Example:

　　CREATE VIEW CUSTOMERORDERS_VW  with SCHEMABINDING  as SELECT ....　　

When an index is created, the first index created on the view must be clustered and unique :

　　CREATE UNIQUE CLUSTERED INDEX ivcustomerorders  on CUSTOMERORDERS_VW (Accountnumber,salesorderid,productid)

Once the command is executed, there is a clustered index of the view. Indexes are basically the same as tables, and maintenance costs are also required.

Ii. Examples of indexed view functions

Persontenmillion is a table of 10 million records, let's execute the following SQL statement:

SELECT Age,COUNT  from persontenmillionGROUP  by ageORDER  by Age

Grouping a table of 10 million records for each age, you can imagine the time it takes.

　　

1 minutes and 31 seconds, this query statement if it is on the page, the page has shown that the page is not responding.

Let's refine the query above, we create an indexed view as follows:

--Create a schema-bound viewCREATE VIEWPERSONAGE_VW withSCHEMABINDING asSELECTAge,count_big (*) asCountage fromdbo. PersontenmillionGROUP  by Age--Create an index for the viewCREATE UNIQUE CLUSTERED INDEXIvpersonage onPERSONAGE_VW (age)

This time we get the data from the indexed view:

SELECT *  from Personage_vw

SQL Server index-index (materialized) View < Nineth >