Database Reference
In-Depth Information
Figure 10-11.
Execution plan of the query with inline table-valued function
If you compare the execution plan shown in Figure
10-11
with the plan that uses a multi-statement scalar
function, as shown in Figure
10-5
, you will observe that there is no
Filter
operator. SQL Server checks the predicate as
part of the Index
Scan
operator. This behavior is the same with the query from Listing 10-6.
The execution time on my computer is:
SQL Server Execution Times:
CPU time=78 ms, elapsed time=84 ms.
While it is still far from being optimal due to the scan performed, these numbers are much better than what
we had before.
Of course, it is much trickier when the function consists of the multiple statements. Fortunately, in some cases,
we can be creative and refactor those functions to inline ones. An
IF
statement can often be replaced with a
CASE
operator, and Common Table Expressions can sometimes take care of procedural style code.
As an example let's look at a multi-statement function that accepts geographic location as the input parameter
and returns a table with information about nearby points of interest (POI). This table includes information about the
first POI in alphabetical order by name as well as an optional XML column that contains the list of all POI IDs to which
that location belongs. In the database, each POI is specified by a pair of min and max latitudes and longitudes.
Listing 10-16 shows the implementation of the multi-statement table-valued function.
Listing 10-16.
Converting multi-statement to inline functions: Multi-statement function implementation
create function dbo.GetPOIInfo(@Lat decimal(9,6), @Lon decimal(9,6), @ReturnList bit)
returns @Result table
(
POIID int not null,
POIName nvarchar(64) not null,
IDList xml null
)
as
begin
declare
@POIID int, @POIName nvarchar(64), @IDList xml
select top 1 @POIID=POIID, @POIName=Name
from dbo.POI
where @Lat between MinLat and MaxLat and @Lon between MinLon and MaxLon
order by Name
if @@rowcount>0
begin
if @ReturnList=1
select @IDList =
(
select POIID as [@POIID]
