how clustered index implemented on view

Question

I can create clustered or non-clustered index on view and SQL SERVER says, we can have multiple non-clustered index (max 249), but only one clustered index exist on table or view. Because, records are sorted, where they physically stored and we can't have multiple sorting order. So, my question is, since View doesn't physically stored in database. Then, what if I create clustered index on view and base table already exist clustered index. How clustered index implemented on view ? How clustered index works and useful on view ?

Brach

Answer 1

A view is just a stored query with no materialized data unless it has a clustered index.

In many cases, people think that any old view can be "sped up" by adding a clustered index. This is very seldom the case. Typically you create an indexed view to pre-aggregate certain computations, such as SUM or COUNT_BIG. If you create an indexed view that doesn't aggregate and hence has the same number of rows / pages as the base table, you haven't achieved anything (of course, like anything, there are exceptions - the index on the view could be skinnier than the base table, for example, leading to fewer pages).

As an aside, you can't create a non-clustered index on a view unless you first create a clustered index. And you should also keep in mind that, like indexes on a table, an indexed view is not free. While it may speed up some queries, SQL Server has to maintain the index throughout the DML portion of your workload.

You really should read documents like this one, which goes into more detail about the performance benefits of indexed views, and this one, which documents restrictions and limitations among other things.

Carboniferous

from your first line, when we create clustered index on view, that view become materialized view or we can create clustered index only on materialized view ? – Brach

A view becomes materialized when you create a clustered index on it. – Carboniferous

since i tagged sql server on my post, so, I'm asking one more question, does sql server have materialized view ? – Brach

Yes, but it's called an indexed view. – Carboniferous

ok.. means has same properties as materialized view in oracle. – Brach

Answer 2

A clustered index on a view is basically a new table that gets automatically updated when the referenced tables get changed. So you buy quicker access time with pre collected data but you are paying with a significantly higher (double in many cases) storage use.

Visit

Answer 3

When we add a unique clustered index to a view, we ‘materialize’ it. In other words, the ‘virtual table’ persists to disk, with its own page structure, and we can treat it just like a normal table. Any aggregations defined by the indexed view are now pre-computed, and any joins pre-joined, so the engine no longer has to do this work at execution time. SQL Server creates statistics for the indexed view, different from those of the underlying tables, to optimize cardinality estimations.

You can read more from this blog.

Vikki