How should I index a table to optimize to find null?

Question

I've got a table and the only time I'll be selecting or doing anything against it is to find all the rows where a certain date column is null. From there, I do stuff with it and update that column, and likely I'll never visit that row again, with the exception of auditing purposes.

So, is there a best practice to ensure that the rows where my column SentDate is null are more quickly found?

Answer 1

You can set the Default Value or Binding of the field to something that will never be used and then look for that value. Otherwise you will be doing a table scan.

Answer 2

In SQL Server 2008, you can filter an index, so you could theoretically create your index to include only NULL values, i.e. WHERE SentDate IS NULL.

Answer 3

To optimize a table to find NULLs you use indexes, of course. NULLs are indexed and are seek-able like any other value:

create table foo (a int null, b varchar(100) null);
create clustered index cdxFoo on foo(a);
go

insert into foo (a,b) select Number, 'not null' from master..spt_values

insert into foo (a,b) values (null, 'null')

select * from foo where a is null

The query plan for the select clearly shows that a seek on the key 'NULL' is used to locate the row.