开发者

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

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.

开发者_JAVA百科

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


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.


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.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜