sql-serverviewindexingclustered-indexindexed-view

how clustered index implemented on view


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 ?


Solution

  • 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.