GilaMonster (7/29/2013)
L' Eomot Inversé (7/29/2013)
I think this is about the first time I've disagreed with Gail about anything. She's better at most SQL Server things than me, but here she's wrong: there are very good reasons for using SCHEMABINDING even when you don't want to index the view.I never said there aren't reasons to use it. I said you don't need it, i.e. it's not required.
Yes, so you did - point taken. However, I think most development shops do need it.
The problem with it on all views is that it makes changing the tables incredibly difficult, even when making changes such as setting a column's nullability that won't have any effect on the results of the view. Personally I'm a fan of proper impact analysis, proper testing and carefully regulated schema changes rather than putting SchemaBinding everywhere.
All one has to do is alter the views to remove schemabinding before doing the table changes and alter them back again afterwards, except in those cases where the change requires a change to the definition of the view (e.g. a column it returns has been deleted) in which case it required that change anyway without schema binding. Yes it would be a pain to do that by hand, but keeping two versions of every view definition (one with schemabinding and one without) in the form of ALTER VIEW scripts is not hard, so the only issue is whether you have some code which can run the right set of alter view scripts, that's needed only assuming that you have too many views, most of which don't use the tables that are changing, for it to make sense just to run the whole lot (apart from the ones which need real changes, of course, but they have to be treated specially whether you have schemabinding or not).
It's a trade-off between the risk of someone making a mistake and you having to recover and the pain of altering each view twice every time you change an underlying table. Having to recover from that potential make would be a much bigger pain, but in a really professional shop with lots of manual checks and testing and everything carefully regulated it may be a low enough risk that the pain of using schemabinding offsets it; then it makes more sense to leave schembinding undone than to do it. however, I believe that most work is done in shops which are nowhere near up to that standard (as demonstrated by the performance of really big contractors like EDS and their like, especially when they have to get it right while coping with requirements changes from the public servants who contracted them).
Tom