Database Reference
In-Depth Information
As you see, by reusing the same stored procedure, we introduced a suboptimal execution plan with an
unnecessary join and clustered index scan versus a filtered non-clustered index scan for one of our use-cases. We
could also have very similar problems with user-defined functions.
There are three types of user-defined functions available in SQL Server:
scalar
,
multi-statement table-valued,
and
inline table-valued
. However, I would rather use a different classification based on their execution behavior and
impact; that is,
multi-statement
and
inline
functions.
Multi-Statement Functions
The code in a
multi-statement function
starts with a
BEGIN
and ends with an
END
keyword. It does not matter how
many statements they have, that is, functions with the single
RETURN
statement are considered a multi-statement as
long as the
BEGIN
and
END
keywords are present.
There are two different types of the multi-statement functions. The first is the
scalar
function, which returns a
single scalar value. The second type is the
table-valued
function, which builds and returns a table resultset that can be
used anywhere in the statement.
Unfortunately, multi-statement function calls are expensive and introduce significant CPU overhead. Let's
populate the
Orders
table that we defined above with 100,000 rows and create a scalar function that truncates the time
part of
OrderDate
column. The function code is shown in Listing 10-4.
Listing 10-4.
Multi-statement functions overhead: Scalar function creation
create function dbo.udfDateOnly(@Value datetime)
returns datetime
with schemabinding
as
begin
return (convert(datetime,convert(varchar(10),@Value,121)))
end
This function accepts the
datetime
parameter and converts it to a
varchar
in a way that truncates the time part
of the value. As a final step, it converts that
varchar
back to
datetime
,
and it returns that value to the caller. This
implementation is terribly inefficient. It introduces the overhead of the function call and type conversions. Although
we often see it in various production systems.
Now let's run the statement shown in Listing 10-5. This query counts the number of orders with
OrderDate
as of
March 1, 2013.
Listing 10-5.
Multi-statement functions overhead: Select that uses scalar function
set statistics time on
select count(*)
from dbo.Orders
where dbo.udfDateOnly(OrderDate) = '2013-03-01'
The execution time on my computer is:
SQL Server Execution Times:
CPU time=468 ms, elapsed time=509 ms
