SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


VIEWS 4


VIEWS 4

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14186 Visits: 12197
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

Alex Fekken
Alex Fekken
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 460
@Tom re " Of course if you don't mid giving complete nonsense results ro your users, that's completely different."

I do mind. But the data is only incorrect because of the bug in SQL Server that does not handle SELECT * correctly inside a view. Which is the point I was trying to make all along and everybody tries to ignore. :-)

Bugs should not be a means to discourage features.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7771 Visits: 25280
Alex-668179
I do mind. But the data is only incorrect because of the bug in SQL Server that does not handle SELECT * correctly inside a view.


It being a "bug" is your opinion and not an opinion shared by many others ...

I could/would call it a feature, as it allows the Database Engine to allow one to select data from a view in the most rapid time possible.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14186 Visits: 12197
Alex-668179 (4/2/2012)
@Tom re " Of course if you don't mid giving complete nonsense results ro your users, that's completely different."

I do mind. But the data is only incorrect because of the bug in SQL Server that does not handle SELECT * correctly inside a view. Which is the point I was trying to make all along and everybody tries to ignore. :-)

Bugs should not be a means to discourage features.

I agree that bugs should never be allowed to discourage the provision of features.

BUT

If you mean you would prefer "select *" when executed as part of a view to be recompiled if need be to fit the current schema definition, then I can understand your point of view, but it would be better for the view definition to be updated automatically as part of the schema change since (especially if schema changes are rare) checking at execute time is not a viable option - it costs too much.
If on the other hand you mean that a schema bound view which is invalidated solely because of a * in the select list shouldn't be invalidated but should be rebuilt as part of the schema change that would otherwise have invalidated it then there's no performance penalty on use of the view I don't believe you can suggest a case where it's workable, in fact I believe that no view such that redefining * to fit the current schema version will never render the view useless no matter what the schema change is can ever exist.
So I don't think the current implementation is a bug: it's the only rational way of handling schema changes for views (allowing views in the same database as the subject tables without schemabinding might be considered a design error, though; I don't think it is, but when I see a view without schemabinding I tend to suspect sloppy thinking, because it's very hard to dream up situations where leaving schemabinding out makes sense - certainly I've never managed to find a case where all the tables are in the database which contains the view).

Tom

Alex Fekken
Alex Fekken
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 460
"If you mean ..."

All I mean is that if you allow "SELECT *" then you should make it always behave like "SELECT *" and not just when you are lucky.

I know that optimisation can be very hard and tricky. But it is no excuse to justify breaking the functionality.

And I wonder if the people who do not consider this a bug (but a "feature") would think the same way about it if SQL Server would always evaluate "SELECT *" based on the current schema definition while Oracle would break the same way as SQL Server does now... and what does the SQL standard say anyway?
Britt Cluff
Britt Cluff
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1799 Visits: 253
Another reason to never use SELECT *

http://brittcluff.blogspot.com/
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 2766
Hi Hugo,
according to another QOD i.e view 5
there was condition that if a add a new column to the table (previously there were 5 columns) and didnt refresh the view then view return only 5 rows. It will return 6 column when we alter or refresh he view.
You told that view do not get effect untill we refresh it or alter.
so why in this question if we drop a column n thn do SELECT * FROM VOrder_Details,
in this condition also 5 columns should get return as view not altered nor refereshed.

I am confused with view behaviour. w00t
can you plz explain ?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10813 Visits: 11967
kapil190588 (10/10/2012)
Hi Hugo,
according to another QOD i.e view 5
there was condition that if a add a new column to the table (previously there were 5 columns) and didnt refresh the view then view return only 5 rows. It will return 6 column when we alter or refresh he view.
You told that view do not get effect untill we refresh it or alter.
so why in this question if we drop a column n thn do SELECT * FROM VOrder_Details,
in this condition also 5 columns should get return as view not altered nor refereshed.

I am confused with view behaviour. w00t
can you plz explain ?

One of the examples in the sample code I posted in that topic covered this scenario. I can't find the topic now (and I don't have the time to hunt it down), but if you look for the discussion and check the code again, I'm sure you'll recognize it.
In this scenario, since the view was not refreshed, it tries to fetch five columns from the base table. But since one of the five was dropped, the base table now has only four columns. So you get an error. I believe the message is something like "more column definitions then there are columns".


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 2766
Hi Hugo,

Ya i got the answer now..
So if a column is dropped from a base table which is using in view then it will throw an error..
but if after deleting a column if we add another column and without refreshing the view if we do Select query then it will show 5 columns and data will be wierd.
Right now?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10813 Visits: 11967
kapil190588 (10/10/2012)
Hi Hugo,

Ya i got the answer now..
So if a column is dropped from a base table which is using in view then it will throw an error..
but if after deleting a column if we add another column and without refreshing the view if we do Select query then it will show 5 columns and data will be wierd.
Right now?

If the view was created with SELECT *, then (and only then) the above is correct. That is because the metadata of the view stores the number of columns and their relative positions in the table, but not their names and data types. However, this should (in an ideal world) be purely academic, as nobody should ever create a view with SELECT *; you sould always enumerate the columns (which avoids all this weirdness).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search