Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 290

WHERE Coalesce(CarrierTrackingNumber, N'9429-430D-89') = N'9429-430D-89'

SELECT SalesOrderDetailId
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber = N'9429-430D-89'
or CarrierTrackingNumber is null

Table 9-21 compares the results of both queries.

Table 9-21: High Volume Comparison of Is Null and Coalese Statements

Method Index? % Cost CPU Reads Duration

Coalesce() N 50 90 1783 1420ms

Is Null N 50 50 1783 1205ms

Coalesce() Y 99 40 526 653ms

Is Null Y 1 10 6 373ms

In the SalesOrderDetail table there are over 120K rows. Without an index, both methods pull massive
pages (all of them) to perform clustered index scanning. Once the index is added to the CarrierTrack-
ingNumber column, you can see the number of reads dropping dramatically. Along with the reducing
number of rows to consider, we can now see reductions in the total time required to evaluate each row
for inclusion in the results.

The point here is that you have to look at more than just whether a table or index scanning operation is
occurring in the execution plan. Sometimes a scan is providing a great benefit in time and costs.

Eliminating Duplicates
DISTINCT statements are used to eliminate duplicates in data. Preferably, a non-clustered or clustered
index will be scanned to retrieve the data that optionally will pass through an aggregation or sort opera-
tor. Even in the worst case scenario where the column under the DISTINCT is a non-indexed column, the
clustered index will be scanned. This is a common task, but one that can be easily misused. A common
misuse is to apply a DISTINCT indiscriminately against an entire set of returned columns. Fortunately,
the optimizer looks into the select statement and doesn't perform any aggregation if at least one column
in the SELECT is unique.

One finer point about the DISTINCT statement is instructive for digging into and understanding query
planning. The point is that Non-Clustered indexes are far more favorable from an I/O perspective when
performing DISTINCT Operations. Keep this in mind and limit the columns under consideration to
those covered by a non-clustered index when tuning DISTINCT statements. Take this simple example
of selecting DISTINCT SalesOrderIds from the Sales.SalesOrderDetail table.

SELECT DISTINCT SalesOrderID
FROM Sales.SalesOrderDetail