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.


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.


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


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