|
|
Posted by Hugo Kornelis on 10/14/06 21:11
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
Navigation:
[Reply to this message]
|