locked
Create Clustered index on a view RRS feed

  • Question

  • I have a view that takes forever to run. Can we create Clustered index on a view? Below is my code. Pl advise how we can create clustered index on this view?

    SELECT DisTINCT Field1, Field2, Field3, Field4

      FROM

    dbo.Vw_Test(NOLOCK)

    WHERE

    Field1 <= 100

    ORDER BY FIELD1


    • Edited by Kalman Toth Tuesday, February 11, 2014 3:27 PM spelling
    Tuesday, February 11, 2014 2:44 PM

Answers

  • I think you may have a slight misconception about the concept and implementation of views. A view doesn't "run".

    Typically, when a query selects from a view (such as in the query you posted), the optimizer will "expand" the view into the query, and it will try to find the best query plan for that situation.

    So in this particular case, the optimizer might choose to use an index on "Field1". For a completely different query that uses the view, the optimizer might choose a completely different query plan.

    Usually, when a query is slow, there are not enough useful indexes on the tables that are used in the query. This includes the tables that are referenced in the view.

    Indexing the view is not the best place to start. It is usually best to start with the indexes on the base tables, because their cost w.r.t. locking/contention is lower, their space requirement is lower, and typically more queries will benefit from them.


    Gert-Jan

    • Proposed as answer by Naomi N Wednesday, February 12, 2014 10:03 PM
    • Marked as answer by Fanny Liu Friday, February 28, 2014 11:12 AM
    Tuesday, February 11, 2014 5:24 PM

All replies

  • Tuesday, February 11, 2014 2:55 PM
  • In order to have index on the view, the view must be created with "schema binding" option

    http://msdn.microsoft.com/en-us/library/ms191432.aspx


    Satheesh
    My Blog


    Tuesday, February 11, 2014 3:23 PM
  • >I have a view that takes forever to run. Can we create Clustered index on a view?

    With index it becomes an indexed view.

    However, not all views are good candidates to become indexed views. Also indexed view carries a substantial overhead when changing the underlying tables.

    Can you post the view definition? First choice is to optimize the query.

    Query optimization:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012






    • Edited by Kalman Toth Tuesday, February 11, 2014 6:24 PM
    Tuesday, February 11, 2014 3:28 PM
  • Indexed views behave differently in different editions of SQL Server.  What edition and version are you using?

    Thanks, Andrew
    My blog...

    Tuesday, February 11, 2014 3:30 PM
  • The original comment was in error, so it has been deleted.

    Tom


    • Edited by Tom Cooper Wednesday, February 12, 2014 11:29 PM
    Tuesday, February 11, 2014 3:40 PM
  • Hello Desigal59,

    Yes you can certainly create a Clustered index over a view. But firstly, how much data does your query pull? Why is it taking such a long time even though you have a filter?

    Second, to create a view with an index, you must define that view with schema binding option and when you create a view with schema binding option you cannot modify underlying tables definition much if it affects the view neither you can drop them too and when you use a schema binding option your queries must include two part names like schemaname.tablename

    use below to create a schema binding view...

    CREATE VIEW dbo.Vw_name WITH SCHEMABINDING
    AS
    SELECT column1, col2, col3
    FROM tbl_name
    GO
    
    CREATE UNIQUE CLUSTERED INDEX IDX_V1 
        ON dbo.Vw_name (col1);

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Tuesday, February 11, 2014 3:41 PM
  • I think you may have a slight misconception about the concept and implementation of views. A view doesn't "run".

    Typically, when a query selects from a view (such as in the query you posted), the optimizer will "expand" the view into the query, and it will try to find the best query plan for that situation.

    So in this particular case, the optimizer might choose to use an index on "Field1". For a completely different query that uses the view, the optimizer might choose a completely different query plan.

    Usually, when a query is slow, there are not enough useful indexes on the tables that are used in the query. This includes the tables that are referenced in the view.

    Indexing the view is not the best place to start. It is usually best to start with the indexes on the base tables, because their cost w.r.t. locking/contention is lower, their space requirement is lower, and typically more queries will benefit from them.


    Gert-Jan

    • Proposed as answer by Naomi N Wednesday, February 12, 2014 10:03 PM
    • Marked as answer by Fanny Liu Friday, February 28, 2014 11:12 AM
    Tuesday, February 11, 2014 5:24 PM
  • The only challenge is the underlying table has LOT OF RECORDS, Such as 400,000,000 records. Hence building the view on the underlying table will ALSO Take forever to run. ANy suggestion how else to optimise this view/query?
    Wednesday, February 12, 2014 9:45 PM
  • ANy suggestion how else to optimise this view/query?

    For suggestions, we'd need to see the view's definition, the definition of the tables and their row count, and all the indexes on these tables. Also, it would help to see the current actual query plan.



    Gert-Jan

    Wednesday, February 12, 2014 9:55 PM
  • Tom,

    Where did the OP post the view's definition? The above query was a query from a view, not the view itself.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, February 12, 2014 10:02 PM
  • You cant have CLUSTERED index on your view.

    I would suggest creating an index on your table instead of the view.  I wold also include field2,field3, and field4 as part of index "INCLUDES".

    If you use this view in a where clause  for example select * from VW_name where  field 2= 'x' . then It might change the order of your index. in this scenario it is better to create an index on field2 and include field 1, etc..

    Ben


    Business Intelligence Architect

    Wednesday, February 12, 2014 10:05 PM
  • You are correct.  Thanks.
    Wednesday, February 12, 2014 11:29 PM
  • You cant have CLUSTERED index on your view.

    Why not?  All modern editions of SQL Server allow creating an index on a view, in which cause the first view index must be both clustered and unique.  That said, there are many restrictions on indexed views.  We can't really say if an indexed view is the right tool for the job without more information.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, February 13, 2014 12:08 AM