User-Defined Functions - Pro SQL Server Internals - page 197

As you see, SQL Server displays the execution plans for two queries. There are no indexes on the ClientId
column and the function needs to perform a clustered index scan on Orders table even though the Query Optimizer
does not factor the estimated cost of the function into the outer query cost.
Another limitation is that Query Optimizer always estimates that a multi-statement table-valued function returns
just a single row, regardless of the statistics available. To demonstrate this, let's create a nonclustered index on the
ClientId column, as shown in Listing 10-11.
Listing 10-11. Multi-statement function costs and estimates: IDX_Orders_ClientId index creation
create nonclustered index IDX_Orders_ClientId on dbo.Orders(ClientId)
In this demo, we have 100 clients in the system with 1000 orders per client. As you remember, a statistics
histogram retains 200 steps, so you would have the information for every ClientId . You can see this in Listing 10-12
and Figure 10-7 .
Listing 10-12. Multi-statement function costs and estimates: IDX_Orders_ClientId index statistics
dbcc show_statistics('dbo.Orders','IDX_Orders_ClientId')
Figure 10-7. Index IDX_Orders_ClientId histogram
Now let's create a multi-statement table-valued function that returns the order information for a specific client
and call it in the single client scope. The code for accomplishing this is shown in Listing 10-13.
Listing 10-13. Multi-statement function costs and estimates: Function that returns orders for the clientid provided
create function dbo.udfClientOrders(@ClientId int)
returns @Orders table
(
OrderId int not null,
OrderDate datetime not null,
OrderNumber varchar(32) not null,
Amount smallmoney not null
)
with schemabinding
as
begin
insert into @Orders(OrderId, OrderDate, OrderNumber, Amount)
select OrderId, OrderDate, OrderNumber, Amount
from dbo.Orders
where ClientId=@ClientId
return
end