Geek Answers Handbook

SQL Server does not use an index comparing datetime so it is not null

I have a simple table that is not related to any other. It does not have a PK column, this is a date. I created a non-clustered index for this column. If I make this request:

select * from the table where datecolumn is not null <- does not use the index and is very slow.

But if I delete no, then like this: select * from the table where datecolum is null <- uses the index and goes very quickly.

There are far more non-zeros than null.

Did I forget something? Can I use a filtered index here?

Thanks in advance.

+4
source share
2 answers

This is normal. He will not use the index unless the predicate is selective enough to guarantee it.

It seems that the vast majority of records are not NULL, so instead of finding them through a non-clustered index, you have to do a lot of bookmark searches and random I / O to extract the rest of the columns to return, it’s faster and more efficient to just scan the entire cluster index.

You can use FORCESEEK to force the behavior you say want. You will probably find that time and I / O statistics go through the roof compared to clustered index scans.

 SET STATISTICS IO ON SELECT * FROM YourTable WITH (FORCESEEK) WHERE YourCol IS NOT NULL
+3
source

The key to understanding your problem is probably in this sentence: there are far more non-zeros than nulls .

SQLServer (and any other relational db, for that matter) uses statistics to determine which query plan it will use. Statistics probably tells the database that there are many rows with non-empty dates. Thus, perhaps SQLServer believes that using an index that it does not cost, and FULL SCAN TABLES, is the best plan for this particular query with NOT NULL . > condition.

Two things I want to mention:

  • A query using is not always faster than a query without using .
  • You can put an INDEX HINT in your query, but I usually find the shooting in my foot doing such an optimization .
+1
source

More articles:


All Articles