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

Listing 10-15. Inline table-valued functions: Select statement without inline table-valued function

select c.ClientName, o.OrderId, o.OrderDate, o.OrderNumber, o.Amount
from dbo.Clients c join dbo.Orders o on
c.ClientId=o.Clientid
where c.ClientId=1

Figure 10-10. Execution plan of the query without the inline table-valued function

While inline table-valued functions can help us encapsulate and reuse code without unnecessary side effects,
they cannot include more than one statement. Fortunately, in some cases, we can refactor the code and convert
multi-statement functions into inline table-valued functions.
As a general rule, scalar functions can be replaced with inline table-valued functions that return a one-row table
with the single column. As an example, take a look at the implementation of dbo.udfDateOnly function. You can
convert it to inline table-valued function, as shown in Table 10-1 .

Table 10-1. Converting multi-statement scalar to inline table-valued function

Multi-statement scalar function Inline table-valued function

create function dbo.udfDateOnly(@Value datetime)
returns datetime
with schemabinding
as
begin
return
convert(datetime,
convert(varchar(10),@Value,121)
)
end

create function dbo.udfDateOnlyInline(@Value
datetime)
returns table
as
return
(
select
convert(datetime,
convert(varchar(10),@Value,121)
) as [OrderDate]
)

select count(*)
from dbo.Orders
where dbo.udfDateOnly(OrderDate) = '2013-03-01'

select count(*)
from
dbo.Orders o cross apply
dbo.udfDateOnlyInline(o.OrderDate) udf
where udf.OrderDate='2013-03-01'

If you run the SELECT with an inline table-valued function, the execution plan shown in Figure 10-11 would still
use an Index Scan operator instead of an Index Seek . Even with an inline table-valued function, you cannot make our
predicate SARGable due to the convert system function calls.