SQL Server indexed view with query overrides (materialized view) and indexed views

Often listen to the students of Oracle materialized view, one of the functions of materialized view is that you can implement query rewriting, it sounds like a tall feeling, SQL Server also has the function similar to Oracle materialized view, just called indexed view. To tell the truth, or materialized views sound more appropriate, compared to the normal view, materialized view is directly stored in the data in SQL Server indexed view also has the function of query rewriting, so-called query rewrite, that is, if the qualifying data on the indexed view, and the query columns are included in the indexed view, You can override queries based on the original table directly by querying the indexed view

It's still customary to do a test environment with code first.

--Create two tables, a header, a detail, and just use it as a demoCREATETABLEHeadtable (HeadidIntPRIMARYKEY, HeadinfoVARCHAR (50), DatastatusTINYINT, CreateDateDatetime)GOCREATETABLEDetailtable (HeadidInt, DetailidIntIdentity1,1) PRIMARY KEY, DatailinfoVARCHAR (50))GO--Write DataDECLARE@iInt=0While@i<200000BEGININSERTInto headtableVALUES (@i,NEWID (),RAND ()*10,-rand () *< Span style= "color: #800000; Font-weight:bold; " >100) insert into Detailtable (headid,datailinfo) values (@i,newid ()) set @i=@i+ 1endgo       

Indexed view Creation

So how do you create an indexed view? Syntax is not the same as creating a normal view, but does not allow select *, the table name to add scheme, because this is not specifically said indexed view, the details are not much to say.

With SCHEMABINDINGas    SELECTJOIN=0GO      

The indexed view requires that the first column created is a unique clustered index, so create a unique clustered index, as follows

For other indexes, you can create an index on a table

　　

　

Query rewriting

It says that the query rewrite is the query statement based on the original table, directly on the indexed view query implementation, then look at the query rewrite what it looks like? Below to observe such a query, SQL is obviously based on the original table query, and ordinary query does not have two, but the observation execution plan will find: This execution plan went to an index lookup, it is clear that headtable on the createdate is not indexed, The index that goes here is the index on the creatdate column on V_indexviewtest, which is the second index created on the indexed view.

　　

If the query statement so write, such as, in the query conditions to add an indexed view of the columns not in the query will not be rewritten, the direct walk is based on the original table query, and ordinary query is not two. In fact, the principle is not difficult to understand, because the view does not contain headinfo this column, if the query column to add this field, the view is not this field, it can only be based on the original table query.

　　

Why queries are rewritten

As we can see above, for the right query, the query is rewritten, that is, the query is implemented directly based on the indexed view, so why is it directly based on the view? is still a performance consideration, because the view has "cured" the result set of the original table after the unique clustered index has been created, the difference between the view and the normal view is that the data itself is stored directly in the view, rather than a query, where the dataset in the view is equivalent to a "subset" of the original table. 　　Because it is a subset, this result set is necessarily smaller than the original table, then the same query fields and query conditions, not only can reduce the table and the link between the table, and the result set is smaller, from this view query, the same conditions can be faster return results, so query rewrite is not difficult to understand. As long as the query fields and query criteria are the same, the query based on the original table and the direct Query index view are the same as follows

　　

When does an indexed view update

The query overrides are described above, and if conditions permit, queries based on the original table are implemented directly from the indexed view. 　　Some people may not rest assured that after all, the data are based on physical tables to do additions and deletions, and the data in the indexed view is physically present, then there will be a concern that the view-based query will not be accurate? After all, is my good query, you default to me to the index view, the query results will be consistent with the original table query?

Then you need to check how the data in the indexed view is updated. We do such a test, in the base table, that is, detailtable query a piece of data to see exactly what happened in the execution plan can be clearly seen, not only Wang Detailtable wrote a piece of data, while the query based on the indexed view also writes a data to the indexed view , you can confidently use indexed views without worrying about inconsistencies in the data in the indexed view and the data in the base tables.

However, it is important to note that the writing at this time is written to the base table of colleagues, also written to the indexed view, the impact on the write is certain, if the write efficiency requirements are very high, it is necessary to be cautious. In fact, the index view is also a kind of redundancy write to achieve query efficiency improvement.

Changing a view-based query

As stated above, some view-based queries are directly anchored to the view, returned from the query results in the view, such as

But if you really don't want to query from the view, I just want to compare the difference between the original table and the view-based query (the efficiency), what should I do? This is good to do, you can query hints, the query to be based on the original table implementation, that is, expand the indexed view of the OPTION (EXPAND views) This query hint is to expand the view, from the original table query, by default is not expanded, such as, you can force the expansion of the indexed view, from the original table query

What about the efficiency comparison? as follows, the coarse look, this efficiency difference is quite large, visible, SQL Server by default, load efficiency is still a certain consideration

From the indexed view here, one is to reduce the join between tables, but the result set of the indexed view is smaller, and it is better to filter the data that meets the criteria. So, by default, queries to SQL are rewritten from view queries.

Query hint for indexed view: with (noexpand) force not expanded, OPTION (EXPAND views) Force expansion

Summarize:

This paper analyzes the indexed view in SQL Server and the query rewriting function brought by indexed view, and it can improve the query efficiency to some extent by curing the result set of the base table through indexed view, especially when super large multi-table join, the original result is saved as an indexed view directly. 　　Improving efficiency by reducing join and IO between tables by querying indexed views is an optimal choice. It is important to note that SQL server has a very limited number of indexed views, which can be referenced in linked books or msnd, and not all cases can be implemented using indexed views.

SQL Server indexed view with query overrides (materialized view) and indexed views