why can't we create a clustered index for the views.

  • hi,

    i created a view without schema binding.

    and i tried to create a clustered index for that view.

    But it was not allowing me to create a index for the views which were not having schema binding.

    Its works fine for the views having schemabinding.

    can any one suggest me what will be the reason?

    thanks,

    kumar.

  • If you want to create an Index on a View, the View must be created using the "WITH SCHEMABINDING" option

    This restriction has been enforced to avoid any changes to schema of any underlying tables and their columns

    Please check the below link

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


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • ya that's fine.

    if we create a view with schemabinding that will not allow us to make any changes to schema of any underlying tables and their columns.

    My ques is why it's not allowing to create a clustered index for non-schemabinding views.

  • This is a bit of a simplification - but basically a view that does not have SCHEMABINDING is just stored as a definition. When you reference the view the definition of that view is examined, incorporated into whatever query you are specifiying and the data is then retrieved.

    With SCHEMABINDING, which allows indexing the data is stored already as if it were a table. There is no need for the processing to construct the view and then retrieve the data - it is already there.

    This is why you can specifiy an index for SCHEMABINDING - the data is actually there. With the view that does not specify SCHEMABINDING there is just the definition of the view - no data is stored so an index cannot be specified.

    http://msdn.microsoft.com/en-us/library/ms190237(SQL.105).aspx

  • BrainDonor (7/13/2012)


    This is a bit of a simplification - but basically a view that does not have SCHEMABINDING is just stored as a definition. When you reference the view the definition of that view is examined, incorporated into whatever query you are specifiying and the data is then retrieved.

    With SCHEMABINDING, which allows indexing the data is stored already as if it were a table. There is no need for the processing to construct the view and then retrieve the data - it is already there.

    This is why you can specifiy an index for SCHEMABINDING - the data is actually there. With the view that does not specify SCHEMABINDING there is just the definition of the view - no data is stored so an index cannot be specified.

    http://msdn.microsoft.com/en-us/library/ms190237(SQL.105).aspx

    Is that right? My understanding was that the view is only 'solidified' once an index is created on it, and that

    WITH SCHEMABINDING is merely a prerequisite for an index to be created - which makes sense, as this prevents changes to the underlying table(s) that would break the view - a necessary precaution if the data is going to be stored in separate places!

  • Gazareth (7/13/2012)


    BrainDonor (7/13/2012)


    This is a bit of a simplification - but basically a view that does not have SCHEMABINDING is just stored as a definition. When you reference the view the definition of that view is examined, incorporated into whatever query you are specifiying and the data is then retrieved.

    With SCHEMABINDING, which allows indexing the data is stored already as if it were a table. There is no need for the processing to construct the view and then retrieve the data - it is already there.

    This is why you can specifiy an index for SCHEMABINDING - the data is actually there. With the view that does not specify SCHEMABINDING there is just the definition of the view - no data is stored so an index cannot be specified.

    http://msdn.microsoft.com/en-us/library/ms190237(SQL.105).aspx

    Is that right? My understanding was that the view is only 'solidified' once an index is created on it, and that

    WITH SCHEMABINDING is merely a prerequisite for an index to be created - which makes sense, as this prevents changes to the underlying table(s) that would break the view - a necessary precaution if the data is going to be stored in separate places!

    +1

    WITH SCHEMABINDING prevents any schema changes on the underlying tables which is necessary as creating clustered index on the view makes the data physically present. If SCHEMABINDING is not specified & a view (not indexed view) has been created, the columns that has been selected in the view definition can be dropped from the table making the view unusable, which should not be the case once the view is indexed (i.e. concerned data is physically stored).


    Sujeet Singh

  • Gazareth (7/13/2012)

    Is that right? My understanding was that the view is only 'solidified' once an index is created on it, and that

    WITH SCHEMABINDING is merely a prerequisite for an index to be created - which makes sense,

    You're right - that does make more sense. I always create the index as soon as I've created the SCHEMABINDING view, so have long since stopped thinking of it as two seperate steps. My mistake - thanks for putting it right.

  • BrainDonor (7/13/2012)


    Gazareth (7/13/2012)

    Is that right? My understanding was that the view is only 'solidified' once an index is created on it, and that

    WITH SCHEMABINDING is merely a prerequisite for an index to be created - which makes sense,

    You're right - that does make more sense. I always create the index as soon as I've created the SCHEMABINDING view, so have long since stopped thinking of it as two seperate steps. My mistake - thanks for putting it right.

    No worries, just wanted to make sure I had it right!

    I generally only use schemabinding when I intend to index the view too - so the two are linked in my mind as well 🙂

Viewing 8 posts - 1 through 8 (of 8 total)

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