SQL Server materialized View Learning notes

First, the basic knowledge

Excerpt from http://www.cnblogs.com/kissdodog/p/3385161.html

SQL Server Index  -  Index ( materialized ) View < Nineth article >

An indexed view is actually a view that "materialized" a set of unique values into a clustered index (the vernacular is that the unique value column in the view is clustered, and then the data is stored on the hard disk), increasing the query speed.

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. If the view references any user-defined functions, then these functions must also be schema-bound;
2. Views cannot reference any other view-only tables and UDFs can be referenced;
3. 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;
4. All objects referenced by views and views must be in the same database;
5. The ANSI_NULLS and QUOTED_IDENTIFIER options must be turned on when creating the view and all the underlying tables;
6. Any function that the view references must be deterministic;

7, must add with SCHEMABINDING, is bound to the schema.

8. After creating the view, you must immediately create a clustered clustered unique index, and must be in the first column (the first column is a unique value, similar to the primary key).

9. Only two-part named tables or UDFs, such as DBO, are supported. SalesOrder.

10. When multiple table joins are involved, the writing of the left JOIN or right join is not supported and can only be connected in from...where or inner join. (This is sometimes very deadly only with inner join too painful)

11. Do not support table.* this lazy and easy way, you have to write the column you want clearly.

-- Create Schema binding view CREATE View Personage_vwwith schemabindingasselect age,count_big (*-- Creating an index for a view creation UNIQUE CLUSTERED INDEX Ivpersonageon personage_vw (age)

indexed views in SQL Server also have Query Rewriting the function,

The so-called query rewrite is that if the qualifying data is on an indexed view, and the query columns are included on the indexed view, you can override the query based on the original table directly by querying the indexed view.

If there are no columns to query on the materialized view, the materialized view is not valid. Go directly to the table to query.

Second, detailed view of materialized view implementation and maintenance principles

Http://www.cnblogs.com/wy123/p/6041122.html

SQL Server materialized View Learning notes