| Author |
Topic |
|
joerage
Starting Member
4 Posts |
 Posted - 2005-07-18 : 23:42:26
|
| I know that there are restrictions on the view when creating a clustered index on it. One of them it that the view must be created with schemabinding.I would like to know why is that necessary?Thanks,Joe |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-07-19 : 11:38:38
|
| Schema binding prevents you from modifying the underlying schema of the view that would otherwise invalidate it. For example, if you renamed a table/column that altered the view's resultset. Look into BOL for more info.Did you already read:http://www.sqlteam.com/item.asp?ItemID=1015Nathan Skerl |
 |
|
|
joerage
Starting Member
4 Posts |
Posted - 2005-07-19 : 11:55:13
|
| Thanks for the link...but what I would like to know is why that restriction exists?In other words, what is the difference between a clustered and non-clustered index that makes the schemabinding restriction mandatory? |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-07-19 : 12:12:38
|
| Think of the indexed view as a table with a clustered index. SQL Server actually stores the complete resultset of the indexed view (normal views are not stored on disk). If you were permitted to alter the referenced tables within the view you would alter the stored resultset, thus "invalidating the view." SQL Server prohibits this via schemabinding -- coupling the view definition to the dependent tables/columns.If you were to drop the clustered index SQL would of course drop all the non-clustered ones... as they point to the clustered?? Not sure what you mean by "difference between a clustered and non-clustered index that makes the schemabinding restriction mandatory." Schemabinding is a restriction of indexed views. An indexed view without a clustered index on it is just a view.Nathan Skerl |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-07-19 : 12:21:40
|
| there are many limitations on indexed views, and in my opinion indexed views don't work very well in the real world. I have yet to really be able to use them in a situation that worked completely to my satisfaction.I think indexed views are a feature microsoft implemented solely to get a good score on the TPC benchmarks.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-07-19 : 12:23:48
|
| there are many limitations on indexed views, and in my opinion indexed views don't work very well in the real world. I have yet to really be able to use them in a situation that worked completely to my satisfaction.I think indexed views are a feature microsoft implemented solely to get a good score on the TPC benchmarks.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-19 : 12:41:21
|
| What eyechart said.Kristen |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-07-19 : 12:43:57
|
quote:
What eyechart said.
twice! Nathan Skerl |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-07-19 : 13:10:02
|
| somehow I posted that a couple minutes apart. weird.-ec |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-07-19 : 13:12:34
|
It was worth repeating.  Nathan Skerl |
|
|
|
joerage
Starting Member
4 Posts |
Posted - 2005-07-19 : 13:31:20
|
| thanks guys! i will put indexed views aside then, i had never used it myself, but from the link given above, the performance boost was looking promising. Anyway, yesterday i was more curious in the theorical explanation as I had my 70-229 exam this morning... And today, its more the practicality that I am interested in, but it seems not worth knowing the theory on that :(That makes me think of a quote i like a lot:"In theory, there is no difference between theory and practice. But, in practice, there is"-- Jan L.A. van de Snepscheut |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-19 : 14:21:02
|
| I am no fan of indexed views either...What ec & co said.I am an avid fan of the WITH SCHEMABINDING option though.rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-20 : 03:39:47
|
| What Rocky said!"In theory, there is no difference between theory and practice. But, in practice, there is"I like that one ...Kristen |
|
|
|
|
Clustered Index Views