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 index 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)?
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.
1) Does use of ISNULL turn a Seek into a Scan? Yes. Applying a function to a column in general makes the expression non SARG-able (not searchable). In order for an index to be considered for a Seek operation the engine needs to know what value to seek for, as a raw binary value. As soon as you apply a funtion to the column you are asking to search for the result of the function, so it has to evaluate the function on every row to see if the result happens to satisfy the condition.
2) Does it make sense to have an index on a column with very low selectivity (2-3 values)? Yes, but never as a standalone index expression. The index tipping point will make a standalone index on a low selectivity column just a waste of space. But very low selectivity columns like bits and flags are very usefull as leftmost keys in an index, when composed with more keys. In your case, given that is deleted flag, it would make sense to be the first key of the clustered index since is expected that every query will specify the 'IsDeleted' condition.
I would also add that you should probably not have NULLs on a 'deleted' flag.