How it is possible to create clustered index on a view.

Question

How it is possible to create clustered indexes on a view in SQL Server 2008. View is not a real table so there is no sense in physical arrangement of the data that clustered index creates.

Where do I miss the point?

Answer 1

Though a view is not a real object, the clustered index is.

And the rows the view returns can be sorted and stored.

However, to be indexable, the view should satisfy a number of conditions.

Mostly they make sure that the results are persisted and the updates to the underlying table can be easily tracked in a view (so that the index will not have to be rebuilt each time the underlying table is updated).

For instance, SUM(*) and COUNT_BIG(*) are distributive functions:

SUM(set1) + SUM(set2) = SUM(set1 + set2)
COUNT_BIG(set1) + COUNT_BIG(set2) = COUNT_BIG(set1 + set2)

, so it's easy to recalculate values of SUM and COUNT_BIG when the table is changed, using only the view rows and the values of the columns affected.

However, it's not the case with other aggregates, so they are not allowed in an indexed view.

Answer 2

An index always exists on disk. When you create the index, you are materialising the rows of the view on disk even if the view itself is not "real" rows.

MSDN White paper with an explanation

Answer 3

This is a somewhat simplified explanation. There's lots of technical hoo-hah going on under the hood, but it sounded like you wanted a general "wassup" explanation.

A view is, essentially, a pre-written and stored query; whenever you access the view, you're retrieving and plugging that pre-written query into your current query. (Leastways this is how I think of it.)

So these "basic" views read data that's stored in tables already present in the database/on the hard drive. When you build a clustered index on a view, what you are really doing is making a second physical copy of the data that is referenced by the view. For example, if you have table A, create view vA as "select * from A", and then build a clustered index on that view, what you end up with is two copies of the data on the hard drive.

This can be useful if table A is very large, and you want quick access to a small subset of the table (such as only 2-3 columns, or only where Status = 1, or you want quick access to the data that requires an ugly join to produce.)

The fun comes in when you update table A (really, any of the tables referenced by the view), as any changes to the "base" table must also be made to the "view" table. Not a good idea in heavily used OLTP systems.

FYI, I believe SQL's "Indexed Views" are called "Materialized Views" in Oracle. For my money, Materialized View is a much better name/description.