Database Reference
In-Depth Information
There are also some limitations on how the Query Optimizer works with multi-statement functions. First, it does
not factor function execution overhead into the plan. As you already saw in Figure
10-4
, there is an additional
Filter
operator in the execution plan, although SQL Server expects that this operator to have a very low cost, which is not
even close to the real overhead it introduces. Moreover, SQL Server does not factor the cost of the operators from
within the function into the execution plan cost of the calling query.
To illustrate this behavior, let's create a function that returns the number of orders for a specific client based on
the
ClientId
provided as the parameter. This function is shown in Listing 10-9.
Listing 10-9.
Multi-statement function costs and estimates: Function creation
create function dbo.ClientOrderCount(@ClientId int)
returns int
with schemabinding
as
begin
return
(
select count(*)
from dbo.Orders
where ClientId=@ClientId
)
end
Now, let's look at the estimated execution plan for the function call, as shown in Listing 10-10 and in Figure
10-6
.
Listing 10-10.
Multi-statement functions cost and estimates: Function call
select dbo.ClientOrderCount(1)
Figure 10-6.
Estimated execution plan for the multi-statement function
