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?