Post reply

Error while creating clustered index view

  • October 2, 2008 at 8:24 am

    #193632

    Hi,

    I have created a view View3 from two inner views (View1 and View2). All the views are created with schemabinding option.

    Now when I try to create clustered index on View3 I get following error.

    Msg 1937, Level 16, State 1, Line 1

    Cannot create index on view 'Database.dbo.View3' because it references another view 'dbo.View1'. Consider expanding referenced view's definition by hand in indexed view definition.

    FYI - I have considered Count_Big(*) and all other conditions while creating View3. Is this something to do with NOEXPAND option? Any help?

    Regards - JL

  • October 2, 2008 at 9:04 am

    #880006

    It's tough to say without knowing definitions of your views...

    However, from the top of my head: if your view1 and view3 are using data from more than one database, attempt to index view3 will fail.

  • October 2, 2008 at 9:21 am

    #880018

    Well, I went through MSDN Q. & A. on creating indexed views and found the solution... There are two ways to handle this

    1. If you want to apply index on view then don't use inner views

    Or

    2. If you can't avoid inner views then apply index on inner views and don't index outer view.

    The second option works for me where the query time is reduced from 56 sec to 1 sec 🙂

    Regards - JL

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply