On 14 Oct 2006 05:27:33 -0700, shark wrote:

>hi,
>
>it might be a very stupid question but i want to know whether an
>execution plan is created in user defined functions in sql server 2000
>like stored procedures.

Hugo Kornelis wrote:
> On 14 Oct 2006 05:27:33 -0700, shark wrote:
>
> >hi,
> >
> >it might be a very stupid question but i want to know whether an
> >execution plan is created in user defined functions in sql server 2000
> >like stored procedures.
>
> Hi shark,
>
> Not a stupid question at all, and not a simple answer either. That's
> becuase it depends on the type of function.
>
> * Scalar function.
> Has an execution plan. Note that the function is executed once for each
> row in the calling query's result set, so the execution plan will be
> executed many times.
>
> * Multistatement table-valued function.
> Has an execution plan. The function is called once per query in which it
> is used and the results are materialized in a temporary table that is
> then used in the calling query.
>
> * Inline table-valued function.
> Has NO execution plan. If a query refers to an inline TVF, the name of
> that function is replaced with the definition before the calling query
> is optimized (just as with a view). This allows the optimizer to pull
> tricks that would otherwise be impossible. But the result is, of course,
> that an execution plan is built for the calling query, not for the
> function itself.
>
> --
> Hugo Kornelis, SQL Server MVP

On 15 Oct 2006 11:37:32 -0700, shark wrote:

>Thanks Hugo . but then what id functiond do have an execution plan wht
>are the advantages of using them over stored procedures.