Database Reference
In-Depth Information
Figure 10-4.
SQL Trace with SP:Starting event
As you see, SQL Server calls the function 100,000 times—once for every row.
Another important factor is that multi-statement functions make the predicates non-SARGable. Let's add the
index on the
OrderDate
column and check the execution plans of the queries. The index creation statement is shown
in Listing 10-8.
Listing 10-8.
Multi-statement functions overhead: Index creation
create nonclustered index IDX_Orders_OrderDate on dbo.Orders(OrderDate)
As you see in Figure
10-5
, both queries are now using a non-clustered index. However, the first query scans the
entire index and calls the function for every row within it while the second query performs the
Index Seek
operation.
Figure 10-5.
Execution plans of the queries with a non-clustered index on the OrderDate column