Overusing ISNULL

ISNULL() is a function, and therefore has an overhead. Here is a pattern I have seen recently …

WHERE ISNULL(CustomerID, 0) = 1

… which harms performance in three ways.

1 Changing NULL to a value that will never equal ‘1’ (EG: ‘0’), is the same as leaving it as NULL (but with the additional overhead of using a function).

2 Indexes will have to be scanned (not seeked), as nesting CustomerID inside a function like this makes it non SARGable.

3 Every single CustomerID has to go through this comparison test – not just the NULL’s. Which may have a profound impact on performance, if -say- a million CustomerID’s have to funnel through!

To fix, you can just remove the ISNULL function, or maybe investigate the option of changing the column definition within the table to ‘not null’.