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 «««1234

User Defined Function Execution Expand / Collapse
Author
Message
Posted Monday, February 18, 2008 7:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Matt Miller (2/18/2008)

Brandie (who put the question in to Steve) mentioned she got it out of one of the MS Press training books...God only know where they might have picked it up.


After going through SQL 2005 Books Online with a fine-toothed comb, I'm a little less quick to take anything that MS puts in writing at face value without thoroughly testing it myself. ;) I reported a couple dozen documentation bugs to MS last year, but stopped when I realized they weren't actually... ummm, what's the word?... oh yeah, "paying" me to debug their code and docs :)
Post #457166
Posted Monday, February 18, 2008 8:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Brandie Tarvin (2/14/2008)

My apologies for the poorly worded question, but I'm still glad I posted it. I'm learning all sorts of things from this discussion. The primary thing being even Microsoft Press authors don't apparently get this stuff right. @=)


It's not your fault. The authors of the book in question decided to take a shortcut and came up with an oversimplified answer for an overly simple question. It seems like the question was written for SQL 2000 (or at least based on SQL 2000 functionality) since SQL 2005 shook up the landscape quite a bit with a lot more options in regards to user-defined functions. Of course determinism in UDFs was well-documented even in the days of SQL 2000, which is why it's even stranger the authors would completely overlook it.
Post #457168
Posted Thursday, February 21, 2008 1:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 5, 2013 7:04 AM
Points: 1,197, Visits: 294
Greg Young (2/13/2008)
Doesn't anybody proof these questions? This is yet another example of where you have to guess what limitations the author had in mind. I tend to write UDFs that use column inputs (don't know why, just seems to work that way) so they always have to be evaluated on every row. If you don't specify the assumptions then no answer is truly correct and we're just throwing darts.


Totally agree! I think at least a couple of people have to proof read every question. These kind of questions takes the fun out of it :/
If anyone is listening, I would volunteer if there was such a 'proof reading' group and I hope a lot of other people would do the same.

Regards,
Hanslindgren

EDIT: Of course I do not say that the questions will be perfect after 'proofing' them, just that it has a probability of becoming slightly better



Post #458386
Posted Monday, April 28, 2008 12:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 7:24 AM
Points: 283, Visits: 115
Hi Brandie

Little doubt, as Functions execute once per row, so How the UDF runs only once in From Clause.
Post #491650
Posted Wednesday, May 7, 2008 8:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 23, 2010 9:52 AM
Points: 62, Visits: 117
Hmm I know exactly how UDF's work given that I write/use them but this question must have been worded really badly (for me).

"SELECT UDF FROM Table" - to me this is saying the UDF is in the FROM clause, thus returns an answer once per row. I answered as such and was told I am wrong. Hmm.
Post #496401
Posted Wednesday, May 7, 2008 9:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,274, Visits: 1,983
janine.rawnsley (5/7/2008)
Hmm I know exactly how UDF's work given that I write/use them but this question must have been worded really badly (for me).

"SELECT UDF FROM Table" - to me this is saying the UDF is in the FROM clause, thus returns an answer once per row. I answered as such and was told I am wrong. Hmm.
No. Your example is the case where the UDF is not in the FROM clause,

SELECT columns FROM table_valued_UDF .... UDF is in the FROM clause
SELECT columns FROM table JOIN table_valued_UDF .... UDF is in the FROM clause
SELECT columns, scalar_UDF FROM tables ... UDF is not in the FROM clause

In the first 2 cases the UDF should be called only once; for the 3rd it's called once per row.

Of course...

SELECT columns FROM tables CROSS APPLY UDF(columns)

... doesn't fit either case, but probably gets called once per row.


Derek
Post #496435
Posted Wednesday, May 7, 2008 9:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:56 PM
Points: 7,075, Visits: 15,325
Derek Dongray (5/7/2008)
janine.rawnsley (5/7/2008)
Hmm I know exactly how UDF's work given that I write/use them but this question must have been worded really badly (for me).

"SELECT UDF FROM Table" - to me this is saying the UDF is in the FROM clause, thus returns an answer once per row. I answered as such and was told I am wrong. Hmm.
No. Your example is the case where the UDF is not in the FROM clause,

SELECT columns FROM table_valued_UDF .... UDF is in the FROM clause
SELECT columns FROM table JOIN table_valued_UDF .... UDF is in the FROM clause
SELECT columns, scalar_UDF FROM tables ... UDF is not in the FROM clause

In the first 2 cases the UDF should be called only once; for the 3rd it's called once per row.

Of course...

SELECT columns FROM tables CROSS APPLY UDF(columns)

... doesn't fit either case, but probably gets called once per row.


#3 would be called once per row, unless the function is marked as deterministic. If the function is marked as deterministic (using the AS SCHEMABINDING), then the optimizer evals it once, and replaces the function call with a constant..

And yes - CROSS APPLY would fire the function once per row.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #496451
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse