6

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)?

Eva423
  • 31
  • 10
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • Is there a reason that DeleteFlag's datatype is not bit? – Gabriel McAdams Dec 22 '09 at 22:24
  • To answer your second question, when deciding whether or not to create an index, the number of possible values isn't as important as to how the data is partitioned. Generally, the more selective the column (i.e. the more possible rows per column value), the higher it should be in the index hierarchy. – Aaronaught Dec 22 '09 at 22:28
  • New on the project so i'm not sure, but it is something that I'm keeping in the back of my mind as a potential change in the future. My current priority is fixing serious performance problems. While it would be better to have that column as a bit, if possible, I don't think it would cause any serious problems, would it? – Abe Miessler Dec 22 '09 at 22:29
  • If the column is a `char(1)` (ASCII character) then it will have the same performance characteristics as a `bit` column. But since a "Delete Flag" is truly a boolean, a `bit` is a better choice to ensure that nobody ever puts in a garbage value ('Q') and also to make it easier to map to a boolean type in various entity frameworks and ORM tools. – Aaronaught Dec 22 '09 at 22:36
  • Also, why are there NULL values in your DeleteFlag? Could you make the column NOT NULL? Then you wouldn't need the ISNULL call at all. – Mark Byers Dec 22 '09 at 22:38

3 Answers3

8

Mark Byers' answer doesn't work, and it is due to a very subtle way that SQL Server treats nulls. In the WHERE expression " t1.DeleteFlag = 'Y' " if t1.DeleteFlag is NULL, the expression returns a NULL. So doing NOT (NULL) also returns NULL and this fails the WHERE condition. Try doing this test:

DECLARE @myvar VARCHAR(1)
SET @myvar = NULL

SELECT 'OK' WHERE ISNULL(@myvar, 'N') = 'N' -- Baseline statement. Returns OK
SELECT 'OK' WHERE NOT (@myvar = 'Y')        -- Equivalent to answer above. Fails
SELECT 'OK' WHERE @myvar = 'N' OR @myvar IS NULL -- This is another way to do it. Also returns OK

The second select statement doesn't return any rows so is therefore not equivalent to the baseline statement and therefore does not work. The third statement is another way to write this query which, one, works, and two, still ensures that an index on the field can be used.

So, here is a correct answer to the question:

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

An alternative to this, which would probably yield nominally better performance results, would be to define the DeleteFlag field as "NOT NULL" and give it a DEFAULT of '' (an empty string). Then, the query can simply be written without worries of NULLs:

SELECT t1.v3, t2.v2
FROM t1
INNER JOIN t2
ON t1.v1 = t2.v1
WHERE t1.DeleteFlag = 'N'
Jonathan Okie
  • 101
  • 1
  • 2
  • There is nothing subtle in this treatment of the `NULL` value. It is standard three-valued logic with the truth values of `TRUE`, `FALSE`, and `NOIDEA`. The negation of `NOIDEA` is still `NOIDEA`. The comparison of `NOIDEA` with any other value is, again, `NOIDEA`. – Anton Shepelev Dec 28 '20 at 15:40
8

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
8

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 function 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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569