April 2, 2012 at 4:20 pm
@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.
April 2, 2012 at 4:31 pm
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.
April 2, 2012 at 6:12 pm
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
April 2, 2012 at 6:48 pm
"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?
April 2, 2012 at 7:01 pm
Another reason to never use SELECT *
http://brittcluff.blogspot.com/
October 10, 2012 at 1:16 am
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/
October 10, 2012 at 3:37 am
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".
October 10, 2012 at 4:03 am
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/
October 10, 2012 at 4:42 am
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).
January 10, 2013 at 5:15 am
Hugo Kornelis (3/27/2012)
What exactly are you confused about? This question is not at odds with the "unexpected results" quote in Books Online, as this error is just one of the possible unexpected results you can get; you only get this when the number of columns has been reduced. Other weird stuff is possible as well - see for instance this demo, that removes the "first" column of the table, then adds a new one.Finally, note that just adding columns does not affect the results from the view; if you comment out the alter table drop column in the code above, the view will keep returning correct results. But dropping the table and recreating it with the columns in a different order (or reordering the columns through the SSMS table designer - which does the exact same thing under the covers) will have similar effects as the code above.
Thanks Hugo..
--
Dineshbabu
Desire to learn new things..
Viewing 10 posts - 61 through 69 (of 69 total)
You must be logged in to reply to this topic. Login to reply