Database Reference
In-Depth Information
The date part comparison can be done without applying the function on the
DATETIME
column.
SELECT soh.SalesOrderID,
soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate >= '2008-04-01'
AND soh.OrderDate < '2008-05-01';
This allows the optimizer to properly reference the index
IndexTest
that was created on the
DATETIME
column,
as shown in Figure
18-11
.
Figure 18-11.
Execution plan showing the benefit of not using the
CONVERT
function on a
WHERE
clause column
This is the output of
SET STATISTICS IO
and
TIME
:
Table 'Worktable'. Scan count 0, logical reads 0
Table 'SalesOrderDetail'. Scan count 1, logical reads 276
Table 'SalesOrderHeader'. Scan count 1, logical reads 8
CPU time = 0 ms, elapsed time = 132 ms
Therefore, to allow the optimizer to consider an index on a column referred to in the
WHERE
clause, always
avoid using a function on the indexed column. This increases the effectiveness of indexes, which can improve query
performance. In this instance, though, it's worth noting that the performance was minor since there's still a scan of the
SalesOrderDetail
table.
Be sure to drop the index created earlier.
DROP INDEX Sales.SalesOrderHeader.IndexTest;
Avoiding Optimizer Hints
SQL Server's cost-based optimizer dynamically determines the processing strategy for a query based on the current
table/index structure and statistics. This dynamic behavior can be overridden using optimizer hints, taking some of
the decisions away from the optimizer by instructing it to use a certain processing strategy. This makes the optimizer
behavior static and doesn't allow it to dynamically update the processing strategy as the table/index structures or
statistics change.
