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 «««1213141516»»

A Function Gotcha with the Use of an Asterisk Expand / Collapse
Author
Message
Posted Monday, September 14, 2009 12:55 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, Visits: 719
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
Why I wrote a sql query analyzer clone
Post #787694
Posted Monday, September 14, 2009 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 9:18 PM
Points: 42, Visits: 104
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?

Post #787749
Posted Monday, September 14, 2009 2:41 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, Visits: 719
adjas (9/14/2009)
Any ideas?

Reading the thread?


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #787759
Posted Monday, September 14, 2009 2:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 9:18 PM
Points: 42, Visits: 104
Sorry guys, my browser didn't display all the page numbers for some reason and i assumed there is only one page.

My apologies
Post #787762
Posted Monday, September 14, 2009 3:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
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.

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #787789
Posted Monday, September 14, 2009 4:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 11:01 AM
Points: 6, Visits: 33
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
Post #787809
Posted Monday, September 14, 2009 4:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #787818
Posted Monday, September 14, 2009 4:58 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, Visits: 719
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
Why I wrote a sql query analyzer clone
Post #787820
Posted Monday, September 14, 2009 5:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #787825
Posted Tuesday, September 15, 2009 2:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 16, 2013 4:30 PM
Points: 5, Visits: 46
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.

[I wrote this after I read the first page of comments, then I realized their were many more comments.]

Post #788545
« Prev Topic | Next Topic »

Add to briefcase «««1213141516»»

Permissions Expand / Collapse