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
 
Search WWH ::




Custom Search