why "is null" or "is not null" use index ?

• smfatqq

  Old Hand

  Points: 371

  More actions

  April 12, 2010 at 9:33 am

  #138242

  My ENV : SQL Server 2008 on Windows Server 2003 64bit

  -- create the the test table

  create table test04

  (

  f01 int ,

  f02 int ,

  f03 int ,

  f04 int

  );

  -- load null value in the table

  declare @i int

  set @i=0

  while @i<10000

  begin

  set @i = @i+1 ;

  insert into test04 values ( null,null,null,null );

  end

  -- create a nonclustered index on a field

  create index i_t04f01 on test04(f01);

  -- the test SQL

  select COUNT(*) from test04 where f01 is null;

  select COUNT(*) from test04 where f01 is not null;

  select * from test04 where f01 is not null ;

  the first and second SQL use "Index Seek"

  the third SQL use "index seek" "RID Lookup"

  Oracle Index never indexes the NULL value , But , SQL Server Index CONTAINS and INDEXES the NULL value ?

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 12, 2010 at 9:54 am

  #1150141

  Yes, it does, because it makes that kind of query faster.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• smfatqq

  Old Hand

  Points: 371

  More actions

  April 12, 2010 at 10:13 am

  #1150156

  Thanks for replies

  by the way , is there some document like MSDN illustrate this point ?

  In Our Site , Many Many Developer and DBA think that The Index of SQL Server never contains and indexes the NULL value .

  I must to Overturn this view

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 12, 2010 at 10:35 am

  #1150172

  Microsoft has an article on estimating index disk space at http://msdn.microsoft.com/en-us/library/ms190620.aspx. It includes data on the effect of nullable columns, which at least implies that indexes can contain null values.

  If you need something more specific than that, you might just want to Google/Bing the subject and see what you can find. Otherwise, your own test seems like pretty good proof on the subject.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• smfatqq

  Old Hand

  Points: 371

  More actions

  April 12, 2010 at 10:43 am

  #1150183

  Thank you very mush ,

  may be my keywords has some problem , I will try to use bing to search again .