July 13, 2012 at 3:42 am
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.
July 13, 2012 at 3:46 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 13, 2012 at 4:03 am
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.
July 13, 2012 at 4:26 am
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
July 13, 2012 at 5:26 am
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!
July 13, 2012 at 5:31 am
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).
July 13, 2012 at 5:40 am
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.
July 13, 2012 at 7:36 am
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