1

If we create an index to a view, we materialize the view.

Why is the view materialized when it is indexed ? What is the signification as opposed to a non-materialized view ?

To my understanding, a normal view don't exist physically. Only its definition is stored, and each reference to the view actually executes the view definition all over again. So when we insert through a view, we insert directly into the table. Is it correct ?

If the view is materialized, it will become a physical table with its data. Then in this case, would modification to the base table is not updated in this view (that has materialized and now lives its own life) anymore ?

Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286
Kenny
  • 1,902
  • 6
  • 32
  • 61

2 Answers2

2

Let's think about a table with a clustered index for a minute. When you choose your clustering key, SQL Server creates a b tree, the leaves of which are the actual data. Non-clustered indexes work the same way, except the leaf nodes are tuples that represent your clustering key (so you can traverse the clustered index and get back to the actual data).

Extending the example, when you index a view, you first need to provide a clustered index. What would you expect to live at the leaves of that index? The data of course! :) And any non-clustered indexes on the view will behave exactly like their analogs on a physical table.

As to your question about a materialized view becoming stale, it doesn't. That is, SQL Server knows that the view relies on the table (which is why the view needs to be schema bound so you can't drop one of its constituent tables), and so any DML operations against the constituent tables are also reflected in the table. You can convince yourself of this by creating an indexed view and then looking at a query plan of a simple update to one of the underlying tables. You should see a corresponding update for the indexed view.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
0

A view is simply a select statement that is saved and can be selected from, for convenience. Inserting/updating through a view does go directly to the table to perform its operation.

An indexed view is stored, indexed, just as a table.

Jeffrey Van Laethem
  • 2,601
  • 1
  • 20
  • 30