September 14, 2009 at 7:25 am
Well, at the end of the day people can shove 'best practice' down your throat all day - but what matters at the end of the day is a working solution, and if that's how you go about it then fair play to you.
I'm not going to shove it down your throat because I get a bit over-whelmed by it all really - especially with coding - you post a simple question on a forum, and you get one person answering the question, and 15 others telling you that if you're asking that question then you've fundamentally misunderstood the universe.
One thing though - make sure that your client code doesn't retrieve columns from result sets using ordinals, otherwise bubbling up select *s can be entirely dangerous. Oh, and sys.sql_expression_dependencies is only in SS2008+. Under 2005 you have to use sys.sql_dependencies which isn't a reliable source of dependency information (i.e. it's easily broken).
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 7:39 am
Matt Whitfield (9/14/2009)
I'm not going to shove it down your throat because I get a bit over-whelmed by it all really - especially with coding - you post a simple question on a forum, and you get one person answering the question, and 15 others telling you that if you're asking that question then you've fundamentally misunderstood the universe.
Ah, that rarest of things, a non-judgemental forum poster 😉
One thing though - make sure that your client code doesn't retrieve columns from result sets using ordinals, otherwise bubbling up select *s can be entirely dangerous.
Please, don't even suggest that I use ordinals. Really ! Bleahhh, ooh I think I got got some on me ....
Oh, and sys.sql_expression_dependencies is only in SS2008+. Under 2005 you have to use sys.sql_dependencies which isn't a reliable source of dependency information (i.e. it's easily broken).
D'oh, since most of my clients are still on 2000 ...
Seriously, thanks for the posts. You are a gentleman.
September 14, 2009 at 7:48 am
ben.mcintyre (9/14/2009)
D'oh, since most of my clients are still on 2000 ...
Doh. In 2000 sys.sql_dependencies is sysdepends - and it exhibits the same broken behaviour. But then on 2000 you're out of luck with both the Recursive CTE and the DDL trigger anyway! 😀
ben.mcintyre (9/14/2009)
Seriously, thanks for the posts. You are a gentleman.
No worries, a pleasure.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 10:02 am
I have run into this scenario using views in SQL 2000 as well. Does anyone know if this is also happens in later versions of SQL?
September 14, 2009 at 11:27 am
If you can't think of a case where it is sensible to use SELECT *, then I suspect that your experience is rather narrow. As Alex pointed out, there are times when you want to select ALL columns regardless of what they are. This would be particularly useful in a table function where you would not want to make any assumptions about what columns will be needed by applications referencing the function. Documented or not, the behavior being discussed is not acceptable. As I said earlier, asterisk expansion in any given context must be either static or dynamic. It can't be half and half. Acceptable behaviors would be:
1) The expression is expanded at compile time and continues return the same columns even if new columns are added.
2) The expression is expanded at run time and returns whatever columns exist at that time.
3) A run time exception is raised if the columns don't match the columns that existed at compile time.
Any of these behaviors would be acceptable if documented. Returning bad data without raising an exception is not acceptable, ever.
gth
September 14, 2009 at 12:55 pm
george.hames (9/14/2009)
Any of these behaviors would be acceptable if documented. Returning bad data without raising an exception is not acceptable, ever.
Nope, it's not. But that's how it is...
As for SELECT * - even if I want to return all columns to the user I still generally build something that does some meta-data inspection and returns a specific column set. For example, our website uses a data retrieval system that does three things:
1) Gets the rows from a particular table
2) Resolves any foreign keys in-line - bringing back descriptions from the foreign table based on the content of the referenced row in a configurable manner
3) Checks for an ownership chain that can be validated (i.e. organisations are linked to purchases are linked to licenses, for example: the proc makes sure that if data is being retrieved on behalf of organisation x, then there is no chance that data can be retrieved that belongs to organisation y).
However, the proc that does that makes views which are then schema bound into the database schema, which makes it really obvious when changing tables that you are going to influence other objects.
I'm rambling. I need to sleep more. 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 2:29 pm
I might have missed something, but it occurs to me that the article still doesn't solve the problem of adding a column to a table by the software.
You will need to know exactly what the column name is and when it is added. You usually don't, as the software was written by someone else and the source is not available.
How does the function know when to include the new column. If you list all the fields the new one won't be included, if you list it and it's still not present you'll get an error.
We might be able to use a DDL trigger in response to changing database schema (adding new column to a table). The trigger could alter the function in question. I am not sure if it forces the system tables to update. haven't had time to check it.
Any ideas?
September 14, 2009 at 2:41 pm
adjas (9/14/2009)
Any ideas?
Reading the thread? 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 2:53 pm
Sorry guys, my browser didn't display all the page numbers for some reason and i assumed there is only one page.
My apologies
September 14, 2009 at 3:41 pm
george.hames (9/14/2009)
If you can't think of a case where it is sensible to use SELECT *, then I suspect that your experience is rather narrow.
After EXISTS. That would be it. Please provide another example?
george.hames (9/14/2009)
...there are times when you want to select ALL columns regardless of what they are. This would be particularly useful in a table function where you would not want to make any assumptions about what columns will be needed by applications referencing the function.
:blink: A multi-statement table function requires a definition, so you can't be talking about that. An in-line function behaves identically in a query plan regardless of whether you specify SELECT *, or a full column list. If you use star syntax, you cannot schema bind the function (error 1054). Unless you enjoy sudden changes in behaviour as discussed in the article, I don't see any advantage in using star syntax here. Or at all, really.
george.hames (9/14/2009)
Documented or not, the behavior being discussed is not acceptable. As I said earlier, asterisk expansion in any given context must be either static or dynamic. It can't be half and half. Acceptable behaviors would be:
You forgot item 4) Learning to love column lists and schema binding
george.hames (9/14/2009)
Any of these behaviors would be acceptable if documented. Returning bad data without raising an exception is not acceptable, ever.
Item 4 again 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 4:21 pm
I'm sorry if my example was too abstract, but it really doesn't matter. The point is that if the syntax is allowed it should work in a predictable manner, or it should fail at run-time. Unpredicable results are not acceptable in a grown-up database. The existence of work-arounds does not mean that there is not a problem, and documenting the unpredicability doesn't make it acceptable.
gth
September 14, 2009 at 4:43 pm
george.hames (9/14/2009)
I'm sorry if my example was too abstract, but it really doesn't matter. The point is that if the syntax is allowed it should work in a predictable manner, or it should fail at run-time. Unpredicable results are not acceptable in a grown-up database. The existence of work-arounds does not mean that there is not a problem, and documenting the unpredicability doesn't make it acceptable.
George, I do see your point, but remember that non-schema-bound views have always worked this way - it's not a special new thing. Metadata changes can affect non-schema-bound objects in unpredictable ways. It's difficult to see how SQL Server could sensibly be modified to change it either.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 4:58 pm
Paul White (9/14/2009)
George, I do see your point, but remember that non-schema-bound views have always worked this way - it's not a special new thing. Metadata changes can affect non-schema-bound objects in unpredictable ways. It's difficult to see how SQL Server could sensibly be modified to change it either.
I think the key word here is 'sensibly' - sure you can use the ddl trigger style 'roughly sort it out' method - but honestly - even though I wrote it, I wouldn't use it... I just honestly can't agree that having *s bubble up is a generically sensible idea. And having thought a lot about it over the course of this thread, I have come to agree more with paul...
There are 1000s of really stupid things you can do in any programming language... that's what best practice is all about I guess. I think the only important thing is not to ram it down people's throats...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 5:11 pm
Matt Whitfield (9/14/2009)
There are 1000s of really stupid things you can do in any programming language... that's what best practice is all about I guess. I think the only important thing is not to ram it down people's throats...
Absolutely.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 15, 2009 at 2:35 pm
I agree that Ian's trick is totally cool.
Actually, I'd like to comment on the original topic, using * in functions.
Because of "pre-compilation", the function is remembering old info rather
than using the current schema.
The same effect may be found with views, also.
All the same weird phenomena occur.
To update a view with the new info, use
exec sp_refreshview 'YourView'
I wonder if the same medicine would fix your functions?
Or if there is a corresponding proc for them.
Viewing 15 posts - 136 through 150 (of 151 total)
You must be logged in to reply to this topic. Login to reply