SQLServerCentral / Discuss Content Posted by Brandie Tarvin / Article Discussions / Article Discussions by Author / User Defined Function Execution / Latest Posts

RE: User Defined Function Execution

Matt Miller (#4) — Wed, 07 May 2008 09:31:44 GMT

[quote][b]Derek Dongray (5/7/2008)[/b][hr][quote][b]janine.rawnsley (5/7/2008)[/b][hr]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.[/quote]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 clauseSELECT columns FROM table JOIN table_valued_UDF .... UDF is in the FROM clauseSELECT columns, scalar_UDF FROM tables ... UDF is [i]not[/i] in the FROM clauseIn 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.[/quote]#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.

RE: User Defined Function Execution

Derek Dongray — Wed, 07 May 2008 09:18:46 GMT

[quote][b]janine.rawnsley (5/7/2008)[/b][hr]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.[/quote]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 clauseSELECT columns FROM table JOIN table_valued_UDF .... UDF is in the FROM clauseSELECT columns, scalar_UDF FROM tables ... UDF is [i]not[/i] in the FROM clauseIn 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.

RE: User Defined Function Execution

janine.rawnsley — Wed, 07 May 2008 08:44:35 GMT

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.

RE: User Defined Function Execution

SG_Explorer — Mon, 28 Apr 2008 12:32:29 GMT

Hi BrandieLittle doubt, as Functions execute once per row, so How the UDF runs only once in From Clause.

RE: User Defined Function Execution

Hans Lindgren — Thu, 21 Feb 2008 01:15:00 GMT

[quote][b]Greg Young (2/13/2008)[/b][hr]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.[/quote]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,HanslindgrenEDIT: 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

RE: User Defined Function Execution

Mike C — Mon, 18 Feb 2008 20:10:49 GMT

[quote][b]Brandie Tarvin (2/14/2008)[/b][hr]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. @=)[/quote]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.

RE: User Defined Function Execution

Mike C — Mon, 18 Feb 2008 19:59:28 GMT

[quote][b]Matt Miller (2/18/2008)[/b][hr]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:[/quote]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 :)

RE: User Defined Function Execution

Matt Miller (#4) — Mon, 18 Feb 2008 19:28:51 GMT

[quote][b]Mike C (2/18/2008)[/b][hr]BTW, my friend that I blogged about (who's currently interviewing for jobs) was asked this very question a couple of days ago. I can only assume the interviewer got it from here :([/quote]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:

RE: User Defined Function Execution

Mike C — Mon, 18 Feb 2008 14:46:46 GMT

[quote][b]Matt Miller (2/18/2008)[/b][hr]From what it looks like (based on the logical reads you see in Profiler), if you go to the trouble of marking it as deterministic (by using the WITH SCHEMABINDING), it does seem to treat it like a constant (meaning - evaluate once and pollinate once per row). But now you get to enter the world of confusing behavior: if you DON'T mark it using WITH SCHEMABINDING, the execution optimizer seems to not know that it is deterministic, and will re-evaluate it once per row. However - it DOES know that it's deterministic if you decide to call that from within another function (in 2000, since that was where it cared about deterministic components in functions).[/quote]That's exactly my point - the question is too simplistic and not specific enough. It's asking for a simple answer to a question that's far more complex than the question would have us believe. Type of UDF and determinism factor into the question significantly. While it's probably not all that common to invoke a UDF using the Function(Constant) form, it's not uncommon to see a UDF being invoked using the Function(@Variable) form. The question seems to imply that Function(Column_Name) is the only form available and that the UDF in question is nondeterministic, which are both bad assumptions IMHO.BTW, my friend that I blogged about (who's currently interviewing for jobs) was asked this very question a couple of days ago. I can only assume the interviewer got it from here :(

RE: User Defined Function Execution

Matt Miller (#4) — Mon, 18 Feb 2008 14:35:58 GMT

[quote][b]Mike C (2/18/2008)[/b][hr]What if your UDF is a scalar detereministic UDF with a constant value passed in? How many times is this UDF executed:[code]CREATE FUNCTION dbo.AbsoluteValue (@i INT)RETURNS INTWITH SCHEMABINDINGASBEGIN RETURN ABS(@i);END;GOSELECT dbo.AbsoluteValue(-10)FROM sys.columns;GO[/code]If SQL Server is executing a deterministic UDF with a constant parameter hundreds or thousands of times, it is seriously wasting resources.[/quote]From what it looks like (based on the logical reads you see in Profiler), if you go to the trouble of marking it as deterministic (by using the WITH SCHEMABINDING), it does seem to treat it like a constant (meaning - evaluate once and pollinate once per row). But now you get to enter the world of confusing behavior: if you DON'T mark it using WITH SCHEMABINDING, the execution optimizer seems to not know that it is deterministic, and will re-evaluate it once per row. However - it DOES know that it's deterministic if you decide to call that from within another function (in 2000, since that was where it cared about deterministic components in functions).

RE: User Defined Function Execution

Mike C — Mon, 18 Feb 2008 14:18:19 GMT

What if your UDF is a scalar detereministic UDF with a constant value passed in? How many times is this UDF executed:[code]CREATE FUNCTION dbo.AbsoluteValue (@i INT)RETURNS INTWITH SCHEMABINDINGASBEGIN RETURN ABS(@i);END;GOSELECT dbo.AbsoluteValue(-10)FROM sys.columns;GO[/code]If SQL Server is executing a deterministic UDF with a constant parameter hundreds or thousands of times, it is seriously wasting resources.

RE: User Defined Function Execution

Matt Miller (#4) — Thu, 14 Feb 2008 11:49:51 GMT

[quote]Regarding my previous example, did you run profiler with the SP:StmtStarting event selected? That is the only event necessary to see the behavior. When I run the select statement with the "non-deterministic" version (I put that in quotes because it really is determinstic but the lack of SCHEMABINDING leads SQL Server to identify it as non-deterministic) I see an SP:StmtStarting event for every row with TextData "RETURN @intA". When using the determinstic version (WITH SCHEMABINDING) the SP:StmtStarting with TextData "RETURN @intA" is only fired once (it helps to clear the profiler output in between).[/quote]I did. It was a good point. Of course - I needed to run it on something bigger to see it (the differences were so small I was chalking that up to sampling errors).

RE: User Defined Function Execution

Matt Marston — Thu, 14 Feb 2008 11:44:53 GMT

[quote][b]Matt Miller (2/14/2008)[/b][hr]Except that rule doesn't work in this case:[code]select getdate() from sys.all_columns[/code]We all know that getdate() is non-deterministic. But still - you will find that you get a repeating SINGLE VALUE down the line (run that with distinct if you want to see what I mean. Perhaps the rule needs to be restricted to user-defined only.[/quote]Yes, the "rule" is regarding user-defined functions. And it is really more of a generalization than a rule. It is basically trying to predict optimizations that SQL Server will do. GetDate() is a built-in system function that behaves differently than user defined functions. The topic of converstion here is UDFs. Try that with an equivalent UDF.[code]CREATE FUNCTION dbo.fnGetDate()RETURNS datetimeASBEGIN RETURN GetDate();ENDGOselect dbo.fnGetDate() as dteinto #tempfrom sys.all_columns-- notice distinct values, demonstrating that the function is called multiple timesselect distinct dte from #temp[/code]Regarding my previous example, did you run profiler with the SP:StmtStarting event selected? That is the only event necessary to see the behavior. When I run the select statement with the "non-deterministic" version (I put that in quotes because it really is determinstic but the lack of SCHEMABINDING leads SQL Server to identify it as non-deterministic) I see an SP:StmtStarting event for every row with TextData "RETURN @intA". When using the determinstic version (WITH SCHEMABINDING) the SP:StmtStarting with TextData "RETURN @intA" is only fired once (it helps to clear the profiler output in between).

RE: User Defined Function Execution

Matt Miller (#4) — Thu, 14 Feb 2008 11:33:12 GMT

[quote][b]Matt Marston (2/14/2008)[/b][hr]However, I do stand corrected, that in practice, it appears that SQL Server (at least when I test it on SQL Server 2005) also does further optimizatition depending on which clause the function is used in. In the WHERE clause (as shown in my previous example) it only depends on condition 1 (do the parameter values change?) and not on condition 2 (is it deterministic?). But the following example shows that elsewhere (at least in the SELECT clause) these 2 conditions must hold true. I tested on SQL Server 2005 and verified using SQL Profiler watching the SP:StmtStarting event.[/quote]Except that rule doesn't work in this case:[code]select getdate() from sys.all_columns[/code]We all know that getdate() is non-deterministic. But still - you will find that you get a repeating SINGLE VALUE down the line (run that with distinct if you want to see what I mean. Perhaps the rule needs to be restricted to user-defined only.

RE: User Defined Function Execution

Matt Marston — Thu, 14 Feb 2008 10:01:25 GMT

[quote][b]Matt Miller (2/14/2008)[/b][hr][quote][b]Matt Marston (2/13/2008)[/b][hr] [code]-- This will only call the function onceSELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd(object_id('dbo.fnDateAdd'))-- This will call the function for every row in sysobjects.SELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd([id])[/code][/quote]True - but that's because one of them is taking in a scalar value as input (as in - object_id('dbo.fnDateAdd') is going to return the same value no matter how many times you run it), and one of them is taking in a parameter that changes for each row. It really has nothing to do with the determinism of fnDateAdd.[/quote]If something is going to return the same value no matter how many times you run it, then it is, by definition deterministic. So it [i]does[/i] depend on the determinism of the function. As I stated in my original post, the number of times a function gets evaluated depends on 1) whether it gets called with the same values or with changing values and 2) whether it deterministic.However, I do stand corrected, that in practice, it appears that SQL Server (at least when I test it on SQL Server 2005) also does further optimizatition depending on which clause the function is used in. In the WHERE clause (as shown in my previous example) it only depends on condition 1 (do the parameter values change?) and not on condition 2 (is it deterministic?). But the following example shows that elsewhere (at least in the SELECT clause) these 2 conditions must hold true. I tested on SQL Server 2005 and verified using SQL Profiler watching the SP:StmtStarting event.[code]CREATE FUNCTION dbo.fnDateAdd(@intA int)RETURNS int--WITH SCHEMABINDINGAS BEGIN RETURN @intAENDGO-- This should return 0SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic') -- This will call the function multiple even though the parameter-- values do not change since the function is not deterministicSELECT *, dbo.fnDateAdd(object_id('dbo.fnDateAdd')) FROM sysobjectsGOALTER FUNCTION dbo.fnDateAdd(@intA int)RETURNS intWITH SCHEMABINDINGAS BEGIN RETURN @intAENDGO-- This should return 1SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic') -- This will call the function once since the parameter-- values do not change and the function is deterministicSELECT *, dbo.fnDateAdd(object_id('dbo.fnDateAdd')) FROM sysobjects[/code]

RE: User Defined Function Execution

Matt Miller (#4) — Thu, 14 Feb 2008 09:01:56 GMT

[quote][b]Matt Marston (2/13/2008)[/b][hr] [code]-- This will only call the function onceSELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd(object_id('dbo.fnDateAdd'))-- This will call the function for every row in sysobjects.SELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd([id])[/code][/quote]True - but that's because one of them is taking in a scalar value as input (as in - object_id('dbo.fnDateAdd') is going to return the same value no matter how many times you run it), and one of them is taking in a parameter that changes for each row. It really has nothing to do with the determinism of fnDateAdd.In general - if the optimizer detects that the inputs to a scalar function are static, then the function is evaluated once, and used as a scalar value. Look at rand() or getdate() when applied to a set. The only thing I can think of that doesn't follow that pattern is NEWID() (and I suppose its companion NEWSEQUENTIALID()).

RE: User Defined Function Execution

Derek Dongray — Thu, 14 Feb 2008 08:54:32 GMT

I always reckon it's a good QotD if I learn something either from it or from the resulting discussion. In this case, I need to go off and look at functions defined WITH SCHEMABINDING.Hence it was a good question to raise even if the answer was not so clear ;).Thanks for putting it up.Derek.

RE: User Defined Function Execution

Carla Wilson-484785 — Thu, 14 Feb 2008 08:38:23 GMT

Matt Marston,Thanks for the excellent examples on how the usage of the function can make a big difference! Definitely something I will keep in mind when building a query.

RE: User Defined Function Execution

Brandie Tarvin — Thu, 14 Feb 2008 05:43:06 GMT

Wow, this is quite a thread.Check the references section on the question and you'll see where I got the idea for it. I suppose I should have checked up on my source, but the information in the training kit actually made sense to me, so I thought it'd make a neat Question of the Day.I didn't take into account CROSS APPLY which I've never used.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. @=)

RE: User Defined Function Execution

Christian Buettner-167247 — Wed, 13 Feb 2008 14:21:35 GMT

Ah, good to know! Thanks for the heads up!

RE: User Defined Function Execution

Matt Miller (#4) — Wed, 13 Feb 2008 13:53:33 GMT

[quote][b]Christian Buettner (2/13/2008)[/b][hr]Hm, I thought I had tried the SCHEMABINDING before already but that really works now.I have also tried to use a non-deterministic function within a function - that actually is allowed:[/quote]..In 2005 and later. Try that in 2000 and you won't get past the getdate(). 2005 mollified the "must be deterministic" rule.sorry - should have specified that.

RE: User Defined Function Execution

Christian Buettner-167247 — Wed, 13 Feb 2008 13:49:37 GMT

Hm, I thought I had tried the SCHEMABINDING before already but that really works now.I have also tried to use a non-deterministic function within a function - that actually is allowed:[code]DROP FUNCTION dbo.fnDateAddGOCREATE FUNCTION dbo.fnDateAdd(@intA int)RETURNS datetimeWITH SCHEMABINDING AS BEGIN RETURN GETDATE()ENDGODROP FUNCTION dbo.TestGOCREATE FUNCTION dbo.Test()RETURNS datetimeASBEGIN RETURN dbo.fnDateAdd(1)ENDGOSELECT dbo.Test()[/code]Since were not updating any data (no side-effects) this seems to be fine.Thanks for your hints, I was really lost:)

RE: User Defined Function Execution

Matt Marston — Wed, 13 Feb 2008 13:21:12 GMT

Chris, it appears that SQL Server requires the WITH SCHEMABINDING function option in order for a function to be identified as deterministic.Here is a completely meaningless sample to show how SQL server handles deterministic functions.[code]ALTER FUNCTION dbo.fnDateAdd(@intA int)RETURNS intWITH SCHEMABINDINGAS BEGIN RETURN @intAENDGO-- This should return 1SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic') -- This will only call the function onceSELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd(object_id('dbo.fnDateAdd'))-- This will call the function for every row in sysobjects.SELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd([id])[/code]

RE: User Defined Function Execution

Matt Miller (#4) — Wed, 13 Feb 2008 13:18:01 GMT

[quote][b]Christian Buettner (2/13/2008)[/b][hr]Hm, I am having a blackout:Why is this function not deterministic?!?[code]CREATE FUNCTION dbo.fnDateAdd()RETURNS intAS BEGIN RETURN 0ENDGO[/code]Do I need to have any special session settings enabled?SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic') returns 0.I am stuck... :(Btw: I prefer "wrong" questions with discussions that start your brain on a topic over questions where I just get my points.[/quote]It IS deterministic, and SQL Server knows it - but somehow that setting never gets set:).Prove it to yourself.[code]create function dbo.fndateadd2()returns intas beginreturn dbo.fndateadd()endgo[/code]It won't let you use anything non-deterministic when creating a function, ergo QED.Long way to get to "that ObjectProperty be broke" part....:)

RE: User Defined Function Execution

Christian Buettner-167247 — Wed, 13 Feb 2008 13:09:23 GMT

Like this?[code]CREATE FUNCTION dbo.fnDateAdd(@intA int)RETURNS intAS BEGIN RETURN @intAENDGO[/code]Still no luck... :(

RE: User Defined Function Execution

Bob Griffin — Wed, 13 Feb 2008 13:05:58 GMT

I think you have to have input parameters. If the function is deterministic it will return a consistent result given the same input parameter(s).......give that a whirl...:)

RE: User Defined Function Execution

Christian Buettner-167247 — Wed, 13 Feb 2008 12:53:50 GMT

Hm, I am having a blackout:Why is this function not deterministic?!?[code]CREATE FUNCTION dbo.fnDateAdd()RETURNS intAS BEGIN RETURN 0ENDGO[/code]Do I need to have any special session settings enabled?SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic') returns 0.I am stuck... :(Btw: I prefer "wrong" questions with discussions that start your brain on a topic over questions where I just get my points.

RE: User Defined Function Execution

Greg Young — Wed, 13 Feb 2008 12:28:04 GMT

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.

RE: User Defined Function Execution

Matt Marston — Wed, 13 Feb 2008 11:07:06 GMT

I got the answer "correct" by choosing what I thought was the best choice out of the available options, but really, as Yelena pointed out, the answer is "it depends". The question didn't even address whether the function was scalar-valued or table-valued? CLR or not? One of the biggest things that it depends on is whether the function is deterministic (see [url=http://technet.microsoft.com/en-us/library/ms187440.aspx#ctl00_rs1_mainContentContainer_ctl13]User-defined Function Design Guidelines[/url]) and whether the values passed into the function are expressions involving only variables and literals or if it includes columns from the query.If the system can determine both 1) that the function always returns the same value for the same input (it is deterministic) and 2) that the same values are passed into the function for every row (for a given execution of the query) then the function will be called once.For example, assume that you have a user-defined function fnDateAdd that is equivalent to the built-in DATEADD function. Assume that a table with column OrderDate is specified in the FROM clause. Then consider the following to WHERE clauses:[code]WHERE fnDateAdd('day', 7, OrderDate) > '2008-02-13'[/code][code]WHERE OrderDate > fnDateAdd('day', -7, '2008-02-13')[/code]In the first case the values passed into the function vary for each row so the function has to be called for each row. In the second case the values passed into the function are the same for every row so it can be called once.

RE: User Defined Function Execution

Yelena Varshal — Wed, 13 Feb 2008 09:10:38 GMT

BOL says in the article "Creating User-defined Functions (Database Engine) ":"The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer."So the real answer maybe not "one per row outside of FROM" but "whatever, more the one"

RE: User Defined Function Execution

Derek Dongray — Wed, 13 Feb 2008 07:47:47 GMT

Hi ChristianI agree that APPLY must be counted as part of the FROM clause, since it's the only place it can be used.So I think the correct answer should have been:[quote]It executes only once if the UDF is in the FROM clause [b]and not in an APPLY sub-clause[/b] and executes [b]at least[/b] once per [b]output[/b] row if it is located in any other clause of the query.[/quote]Obviously, it could execute many more times than the number of output rows, especially if a WHERE clause eliminates [i]all[/i] the output. :)I suspect the author simply hadn't considered the APPLY sub-clause. Also the phrase "once per row" is very vague since it doesn't specify where the row is from.Derek.

RE: User Defined Function Execution

Christian Buettner-167247 — Wed, 13 Feb 2008 05:24:45 GMT

Hello Derek,[quote]I think in this context the CROSS APPLY clause isn't counted as part of the FROM![/quote]Of course it is part of the FROM clause. APPLY is an operator that is used in the FROM clause. See BOL.I totally agree that if you SELECT only from a TVF and don't join it to anything else, then it would be executed only once. But I cannot imagine that this (SELECT from TVF only) was the intention of the author.Thanks for your comments though! Especially the hint with the optimizer.

RE: User Defined Function Execution

Derek Dongray — Wed, 13 Feb 2008 05:13:06 GMT

[quote][b]Christian Buettner (2/13/2008)[/b][hr]Um, question:Shouldn't it be executing once per row in the FROM clause too?Example:SELECT * FROM dbo.MyTable T CROSS APPLY dbo.MyFunction(T.fielda)Grazias![/quote]I think in this context the CROSS APPLY clause isn't counted as part of the FROM!In [url]http://msdn2.microsoft.com/en-us/library/ms175156.aspx[/url] (quoted by Christian above) Microsoft says that the UDF in the APPLY will execute once per row of the 'left input'.Elsewhere, [url]http://msdn2.microsoft.com/en-us/library/aa175085(SQL.80).aspx[/url], it is indicated that the exact number of executions may be difficult to determine.[quote]The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.[/quote]About the only certain thing is that[code]SELECT ... FROM udf[/code]will execute the UDF once, and a UDF anywhere else will [i]probably[/i] be executed at least once for each row returned, but may be executed many more times than that!Derek.

RE: User Defined Function Execution

Christian Buettner-167247 — Wed, 13 Feb 2008 04:23:59 GMT

Hi Brandie,thanks for your response.I still am not sure whether your assumption is correct.When reading BOL I get the assumption that the TVF is evaluated for each and every row:[quote]The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input. [/quote][url=http://technet.microsoft.com/en-us/library/ms175156.aspx]http://technet.microsoft.com/en-us/library/ms175156.aspx[/url]Thanks!

RE: User Defined Function Execution

Brandie Tarvin — Wed, 13 Feb 2008 04:04:27 GMT

I used to think that too, but actually no. The FROM clause is the one place where the UDF doesn't execute once per row. Remember that a UDF in the FROM clause, even using CROSS APPLY, is a table-defined function. Therefore, it is more set-based in nature than other functions and will not evaluate per each individual record, whereas a SUM() function or an AVG() function will evaluate every time a record is passed into the database engine.Does that help?

RE: User Defined Function Execution

Christian Buettner-167247 — Wed, 13 Feb 2008 00:04:17 GMT

Um, question:Shouldn't it be executing once per row in the FROM clause too?Example:SELECT * FROM dbo.MyTable T CROSS APPLY dbo.MyFunction(T.fielda)Grazias!

User Defined Function Execution

Brandie Tarvin — Tue, 12 Feb 2008 20:44:36 GMT

Comments posted to this topic are about the item [B]User Defined Function Execution[/B]