rmechaber (4/2/2012)
Nope, sorry, my statement is correct. I said "updates will fail if they try to ALTER any table upon which your custom views were based" and that is true. Perhaps I should have been more specific in stating that "if the ALTER TABLE command, say, dropped columns that were included in the view", but I thought that would have been clear from my reply.
So you are happier to have the upgrade deliver incorrect data to your users (which you may not detect until this incorrect data has prolifereated into all corners of the business and done heaven alone knows what damage), or stop your code working at all, instead of having a failure at the upgrade stage? Really?
Whether you decide this is desirable or not is, of course, up to you. But, you asked for an example where WITH SCHEMABINDING might not be recommended, and I gave you one.
Someone else asked for an example, not me, and as far as I can tell you haven't given one.
You could certainly proceed as you suggest, and not apply database changes without reviewing/testing to see if any custom views would be invalidated. But that presumes that you will be at the company forever or that your successor will follow strict guidelines in updating the application. Also assumes that you will always have access to the T-SQL that the vendor wants to run in the future. For some of the "black box" apps out there that you might want to run queries against, I myself would view schema-bound views as a time bomb.
"black box"??? But you are writing custome views against it? Either it's a black box or you can see inside it, not both, but you are asserting that it is a black box and you know enough about what's inside to write views against it, which seems to me to be a contradiction.
As for strict update guidelines, I would expect that to be a given: anything (such as an upgrade of 3rd party software) that may invalidate code on which my emloyer depends (whether by making it deliver incorrect results or by making it crash with an error message or be in any other way invalid) has to be checked before it can go into production; it may need new versions of the code on which we depend, or it may result in a decision not to apply the upgrade, or it may result in us withdrawing features that we can no longer provide. It's preferable (not essential) that the checking be automated (schemabinding is a mechanism for automatically checking some things). It doesn't matter whether I'm still in charge or it's my successor or his successor or whoever - no matter who is in charge, his or her primary objective will be to keep the company afloat and the customers happy, and allowing unchecked changes to inflict damage on the company or on its customers is incompatible with that objective.
No access to the third party SQL? So you don't know the schema, how on earth do you think you can write custom views against it and maintain them?
Of course there are cases where you can't schema bind your custom views: this happens any time you can't create those views in the database that contains the tables (and views) that the views use, because a view that references tables or views by 3-part or 4-part names can't be schema bound. If you have a grey box where you can see inside but not put things inside, you can't use schemabinding for views on that box that you write, because you can't do them using only 2-part names. That just makes doing the checking harder, because you can't use schemabinding to do it for you. If the third party wants to operate that way (and I've known it happen) you are maybe stuck with it (and maybe not: but read the license very carefully and make sure what you plan to do is allowed before messing about inside the grey box). Obviously schemabinding is appropriate only in cases where it is possible (and not going to get you sued) to have it.
Again, this isn't something that is characteristic of most mainstream production environments. But it is an example of a potential pitfall of schema-bound views.
It doesn't appear to me to be an example of that, unless you mean specifically the grey box case where the third party won't let you do mods, but in that case schemabinding is irrelevant because as noted above it isn't possible.
It's maybe an example of the pitfalls of writing custom views against a schema over which you have no control, which may in future change in an unpredictable manner, and which may take on a new form of which you have no knowledge at all. I had troubles like that with a certain third party schema once (their app had bugs that they weren't interested in fixing, so we fixed things our side of the API but that required us to understand the schema. Fortunately the third party concerned never did issue any updates to the schema while we were still using his rubbish, and even more fortunately we quite soon found better options than that third party.
Tom