Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

VIEWS 4 Expand / Collapse
Author
Message
Posted Monday, April 2, 2012 4:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:17 PM
Points: 197, Visits: 459
@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.
Post #1276996
Posted Monday, April 2, 2012 4:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1277003
Posted Monday, April 2, 2012 6:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:38 AM
Points: 7,850, Visits: 9,600
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
Post #1277028
Posted Monday, April 2, 2012 6:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:17 PM
Points: 197, Visits: 459
"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?
Post #1277036
Posted Monday, April 2, 2012 7:01 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Another reason to never use SELECT *

http://brittcluff.blogspot.com/
Post #1277041
Posted Wednesday, October 10, 2012 1:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:51 AM
Points: 1,938, Visits: 2,372
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.
can you plz explain ?



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1370728
Posted Wednesday, October 10, 2012 3:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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.
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
Post #1370794
Posted Wednesday, October 10, 2012 4:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:51 AM
Points: 1,938, Visits: 2,372
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/
Post #1370809
Posted Wednesday, October 10, 2012 4:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #1370831
Posted Thursday, January 10, 2013 5:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:30 AM
Points: 971, Visits: 564
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..
Post #1405356
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse