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 (age) from Persontenmilliongroup by Ageorder

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 view of the CREATE View Personage_vwwith schemabindingasselect age,count_big (*) as countage from dbo. Persontenmilliongroup by age--Creating an index for a view create UNIQUE CLUSTERED index Ivpersonageon PERSONAGE_VW (age)

This time we get the data from the indexed view:

SELECT * from PERSONAGE_VW

This time it comes out, because it's just the equivalent of using a clustered index from a 81-row table that will have 81 rows of data:

　　

　　

The query speed is much faster, but does this think the indexed view is a good choice? No, it just means it could be. As with any index, you need to remember the maintenance cost of the index. Maintaining the index slows down the execution of INSERT, UPDATE, and DELETE statements on the underlying table. This must be taken into account, which is a matter of balance, depending on each table and each index. Nonetheless, indexed views are a more powerful tool and are therefore carefully weighed.