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

WHERE color = N'Black'
or color is null

Usually the action of wrapping the function around the column would produce a performance issue
and the conventional wisdom would be to replace the function with searchable arguments and apply a
non-clustered index. The Production.Product table is perfect for our test since it doesn't have an index on
the color column. No index means that a clustered index scan (really a table scan) must be performed to
read every row in the product table. Table 9-19 displays the results from the first run.

Table 9-19: Duration Comparison of Is Null and Coalesce Statements

Method % Cost CPU Reads Duration

Coalesce() 50 0 15 97ms

Is Null 50 0 15 173ms

What this tells us is that both methods require the same number of page reads. There is no performance
punishment for using a function around the column. All else being equal, the results also show that
the function evaluates faster than the two separate test conditions. Because of the clustered index scan
and fact that this step takes up 100 percent of the cost of each individual plan, a lock-step action would
be to apply a non-clustered index. However, just adding a non-clustered index doesn't always solve
the problem. This is a 504 row table with 248 null values. This means there is a 49 percent selectivity
calculation for a NULL value, which is just under the selectivity criteria for the optimizer to abandon the
index if provided. Add one anyway. The results should look similar to those in Table 9-20.

Table 9-20: Comparison of Is Null and Coalese Statements

Method % Cost CPU Reads Duration

Coalesce() 51 10 4 126ms

Is Null 49 0 4 270ms

The results are better from an I/O perspective, but worse from a speed perspective. The reads have
dropped from 15 to 4 pages. This decreased the overall costs seen in the increase of separation in subtree
costs from 0.0033172 for the IsNull() method vs. 0.00457714 for the Coalesce() method. The issue
remains that with the addition of the index, the Coalesce method is still faster and is even using a full
Index Scan operation. The reason is because of the size of the Production.Product table. With only 504
rows, it is more efficient to employ a sequential disk read and incur the actual table scan cost than to
perform an index seek. To see what happens when the table size starts to come into play, run these
queries once without the index and then again after adding a non-clustered index on the
CarrierTrackingNumber.

SELECT SalesOrderDetailId
FROM Sales.SalesOrderDetail