Microsoft SQL - NULLIF and Index Scans or Table Scans - Performance issues

Issue:  Poor performance on SQL queries utilizing NULLIF in the WHERE clause

Applies To: Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012

Using NULLIF in the WHERE clause of a SQL statement will cause a table scan or index scan on an otherwise properly indexed table.  This results in very poor query performance as the number of rows grows.

EXAMPLE:

Here we want to check our products table for products that either have a sale price (if one exists) or a price greater than 50.00

SELECT ProductID, ProductName
FROM Product
WHERE ISNULL(NULLIF(SalePrice, 0.00), Price) > 50.00

Unfortunately this scenario will cause an index scan even if you have a covering index for the query.

REWRITTEN:

We can rewrite the query to omit NULLIF() and yield a HUGE performance improvement since an index seek will now be used instead.

SELECT ProductID, ProductName
FROM Product
WHERE 50.00 < CASE WHEN SalePrice = 0.00 THEN Price ELSE SalePrice END

RESULT:

Put into practice, on a complex query parsing a table with approximately 50,000 records, a performance improvement of 1,500% was achieved by removing NULLIF() from the where clause. 

Article ID: 34, Created On: 12/13/2012, Modified: 12/13/2012