Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to analyze the materialized view of SQL SERVER. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

There has always been an impression that SQL SERVER does not have a materialized view, which leads to the high-end materialized view of ORACLE in the concept of developers and DBA, while SQL SERVER can only use rough traditional VIEW and slow performance.

NO NO NO, who said, is not materialized view, does not prove that there is no SQL SERVER has INDEX VIEW, this is materialized view.

First of all, why the materialized view is more advanced than the general VIEW.

Just imagine if you have a watch, and then at the same time, there are eight needs waiting for you, you need to have dinner with Uncle Zhang's daughter, and then at the same time you need to play a game with Aunt Li's son, and then Uncle Song's house is empty. You need to see their bear children for a while.

Are you exhausted? if you have a brother or sister who does all this work for you at this time, you can just take an uncle's house.

Reduce deadlocks and BLOCKED, generate multiple custom avatars, save time to generate data during the query, and make the entire query faster, which is the reason and purpose of this thing.

The principle of INDEX VIEW in SQL SERVER is to build a CLUSTER INDEX on VIEW to make the VIEW a physical table, and you can build multiple secondary indexes on this VIEW to make VIEW a physical table, reducing the entanglement that may be related to multiple original tables.

Speaking of which, it is still ugly to say that there must be conditions for the establishment of INDEX VIEW, and there must be some conditions for everything to be done these days.

What INDEX VIEW can't do.

That cannot be found in the following SQL statements

1 COUNT

2 OPENDATASOURCE OPENQUERY OPENROWSET

3 LEFT RIGHT FULL JOIN

4 SELECT *

5 DISTINCT

6 TOP

7 CUBE ROLLUP GROUPING SET

8 UNION

9 EXCEPT

10 INTERSECT

11 PIVOT

12 UNPIVOT

13 GROUP BY HAVING

14 conditions for change

None of the above can exist in INDEX VIEW, and the materialized view of SQL SERVER cannot be established if it exists. Someone immediately said that the materialized view of ORACLE does not have so many restrictions.

Hehe, hehe, Baidu, compared with SQL SERVER INDEX VIEW, may have an advantage in very few places.

In fact, using the brain to think, we know that the most fundamental thing that can not generate materialized view is that it can not predict and change, this is no longer any database system can run in this case, in general, SQL SERVER and ORACLE can not accept those unpredictable, complex changes occur in INDEX VIEW and materialized view.

Having said so much, how to build a materialized view of SQL SERVER

1 it takes two steps to change VIEW into INDEX VIEW

The figure above will not report an error in the establishment of INDEX VIEW. In the second step, creating the necessary CLUSTERED INDEX for VIEW will report an error. Let's see what the error is.

To explain here, it's easy to set up an INDEX VIEW.

Create the view by using the WITH SCHEMABINDING option.

Create the unique clustered index on the view.

Remove those uncertainties so that INDEX VIEW can update the data with clearer logic and synchronize with the source table.

What is the difference between VIEW and ordinary VIEW? the first is to use the view of INDEX VIEW, and the second is to delete the execution plan from the newly established view that does not use INDEX VIEW. Above is the execution plan of INDEX VIEW, and below is the execution plan of ordinary VIEW. Which is better or worse?

Moreover, a secondary index can be established on INDEX VIEW to continue to speed up the query speed of INDEX VIEW.

At this point, the important thing is not to build an INDEX VIEW, but to consider whether you want a complex VIEW to solve the problem at once, or whether you want to build multiple INDEX VIEW to solve the problem.

At the end of the day, if your system doesn't have bottlenecks, you don't have to use INDEX VIEW. Technology evolution, and requirements are all one-time problem outbreaks and limits.

After reading the above, do you have any further understanding of how to analyze the materialized view of SQL SERVER? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.