locked
How to create Index on views???? RRS feed

  • Question

  •  

     

    i get the error "

    Msg 1939, Level 16, State 1, Line 2

    Cannot create index on view 'MobileBillCallView' because the view is not schema bound.

     

    " when i execute beolw code

    CREATE

     

    NONCLUSTERED INDEX [IX_MobilePeriodID] ON [dbo].[MobileBillCallView]

    (

    [MobilePeriodID]

    ASC

    )

     

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    Wednesday, October 14, 2009 5:37 AM

Answers

  • Your view should look like :

    CREATE VIEW Test_view
    WITH SCHEMABINDING
    AS
    {
    }

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP
    • Proposed as answer by Melissa Suciadi Wednesday, October 14, 2009 6:24 AM
    • Marked as answer by Kalman Toth Wednesday, October 14, 2009 9:03 AM
    Wednesday, October 14, 2009 6:07 AM
  • Please use two part name (SCHEMANAME.OBJECTNAME) whenever you refer an object with schema binding option.


    Thanks, Leks
    • Marked as answer by Kalman Toth Wednesday, October 14, 2009 9:03 AM
    Wednesday, October 14, 2009 7:08 AM

All replies

  • You have to create the view WITH SCHEMABINDING. There are a number of restrictions on creating an indexed view. see the link
    http://technet.microsoft.com/en-us/library/cc917715.aspx
    • Proposed as answer by Melissa Suciadi Wednesday, October 14, 2009 6:23 AM
    Wednesday, October 14, 2009 5:42 AM
  • Your view should look like :

    CREATE VIEW Test_view
    WITH SCHEMABINDING
    AS
    {
    }

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP
    • Proposed as answer by Melissa Suciadi Wednesday, October 14, 2009 6:24 AM
    • Marked as answer by Kalman Toth Wednesday, October 14, 2009 9:03 AM
    Wednesday, October 14, 2009 6:07 AM
  • Your view should look like :

    CREATE VIEW Test_view
    WITH SCHEMABINDING
    AS
    {
    }

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP

    i got the below error  after doing that
    "

    Cannot schema bind view 'dbo.MobileBillCallView2' because name 'MobileBillCall' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

    "
    Wednesday, October 14, 2009 7:01 AM
  • Please use two part name (SCHEMANAME.OBJECTNAME) whenever you refer an object with schema binding option.


    Thanks, Leks
    • Marked as answer by Kalman Toth Wednesday, October 14, 2009 9:03 AM
    Wednesday, October 14, 2009 7:08 AM