SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User Defined Function Execution


User Defined Function Execution

Author
Message
Mike C
Mike C
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6713 Visits: 1172
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.BlinkErmmSick


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. Wink 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 Smile
Mike C
Mike C
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6713 Visits: 1172
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.
Hans Lindgren
Hans Lindgren
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2936 Visits: 366
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 :-D



SG_Explorer
SG_Explorer
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 122
Hi Brandie

Little doubt, as Functions execute once per row, so How the UDF runs only once in From Clause.
janine.rawnsley
janine.rawnsley
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 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.
StarNamer
StarNamer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2816 Visits: 1992
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
Matt Miller (4)
Matt Miller (4)
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30699 Visits: 19011
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search