Can using isnull in a where statement cause problems with using indexes?

Question

I have a query like the following:

SELECT t1.v3, t2.v2
FROM t1
INNER JOIN t2
ON t1.v1 = t2.v1
WHERE ISNULL(t1.DeleteFlag,'N') = 'N'

I have an index in place that I think should result in there being an Index Seek for the = 'N' part but instead I am seeing a very expensive Index Scan. Is it possible that the ISNULL is messing up the correct use of indexes? Does it even make sense to have an index on a column that will only have a few possible values (like DeleteFlag will)?

Answer 1

Yes, any function calls in your WHERE clause will likely make the index useless. Try to rewrite it so that the index can be used:

SELECT t1.v3, t2.v2
FROM t1
INNER JOIN t2
ON t1.v1 = t2.v1
WHERE NOT t1.DeleteFlag = 'Y'

The index makes sense if the number of results you expect from the query is much smaller than the total number of rows in the table.