The cost of a View in SQL Server

lpwwtiir  于 2023-08-02  发布在  SQL Server
关注(0)|答案(2)|浏览(148)

When creating a View (let's say on SQL Server (2016), but I guess this is not DBMS specific):

Are the data of the View stored somewhere else and therefore required extra memory (as a duplication of data)?

In this case how are the data of the view updated by the engine when the queried tables change? Is there an extra calculation time cost?

Otherwise, can we say a View is just 'a way to save a SELECT' ?

hrirmatl

hrirmatl1#

View is just an angle that you set up to take a look at your data. View it self, by default does not contains data and if you take look at the execution plan of the view, you will notice that SQL Server is going to execute the query inside the view.

BUT this is not the whole story. If you materialized a view(Create an index on the View 's column(s)), then the View will also contain the data too. MSDN describe the Materialized View as below:
The Materialized View pattern describes generating prepopulated views of data in environments where the source data isn't in a suitable format for querying, where generating a suitable query is difficult, or where query performance is poor due to the nature of the data or the data store.

the question about Materialized Views , is that, what is the update frequency of the View 's data? MSDN says:

When the source data for the view changes, the view must be updated to include the new information. You can schedule this to happen automatically, or when the system detects a change to the original data. In some cases it might be necessary to regenerate the view manually.

Read more about materialized view

4urapxun

4urapxun2#

Vahid's answer is correct. I would add two things:

  • Materialized views have many constraints and complications. They are rarely needed and demand careful consideration.
  • Sometimes the query optimizer is not as good as it could be with gigantic views. If you try to select 1% of 1 million rows, even with the help of clustered index on the underlying table(s), you can sometimes overwhelm the optimizer and cause it to produce ridiculous query plans. I sometimes had to resort to table-valued functions — which are really parameterized views — to avoid that problem.

If your users are cognizant of query performance and are writing complicated, nested queries with more than (say) 4 tables, I recommend all views start with the letter "v" to remind the user it's a view. That gives them a clue when performance is mysteriously bad, and doesn't tempt them to smuggle in a complex view where a table or two would suffice.

相关问题