February 18, 2008 at 7:59 pm
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.:blink::ermm::sick:
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 🙂
February 18, 2008 at 8:10 pm
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.
February 21, 2008 at 1:15 am
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 😀
April 28, 2008 at 12:32 pm
Hi Brandie
Little doubt, as Functions execute once per row, so How the UDF runs only once in From Clause.
May 7, 2008 at 8:44 am
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.
May 7, 2008 at 9:18 am
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
May 7, 2008 at 9:31 am
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?
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply