5

I am using SQL Server 2008 R2 and I want to add a non-clustered index on a non-unique, nullable field. My index will also have one more column included in order to avoid accessing my clustered index:

CREATE INDEX IX_My_Index 
ON MyTable (myBasicField) 
INCLUDE (myIncludedField);

In the actual data of myBasicField there will be a lot of NULLs and I was wondering if there is a way I can increase performance by not scanning these NULLs, or prevent NULL values to be stored on my index.

Thanks in advance.

Amro
  • 123,847
  • 25
  • 243
  • 454
zafeiris.m
  • 4,339
  • 5
  • 28
  • 41
  • I like the filtered index answer. This is just something else. You could put that data in a separate table with a FK relationship and then you eliminate the null altogether (if it is null then no row in the FK table.). A PK to FK join is pretty fast if both are clustered index. – paparazzo Apr 09 '12 at 16:10
  • I wanted to confirm that Sql Server does include NULLs in indexes (it does) - [ms docs](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc280372(v=sql.100)#filtered-indexes-for-subsets-of-data) (from [this answer](https://stackoverflow.com/a/20687291/4289902)) – Jono Job Aug 20 '19 at 23:54

2 Answers2

8

With SQL Server 2008 and newer, you could use a filtered index. See an intro blog post here - syntax would be:

CREATE INDEX IX_My_Index 
ON MyTable (myBasicField) 
INCLUDE (myIncludedField)
WHERE myBasicField IS NOT NULL;

Any query that contains the same WHERE clause can take advantage of this, and the index will be a lot smaller and thus perform better if you exclude NULL values like this.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • +1 If the user query was where myBasicField IS NULL then you would get a full table scan? – paparazzo Apr 09 '12 at 14:26
  • @Blam: possibly - depends on what other criteria there might be in your query. If `myBasicField` is NULL, then **this** index will definitely not be used. – marc_s Apr 09 '12 at 14:28
  • Thank you guys for your fast responses. That's what I needed – zafeiris.m Apr 09 '12 at 14:44
  • By the way, is there anything similar I can do in SQL Server 2005? We have few clients not upgraded yet, so I will need sth there too. – zafeiris.m Apr 09 '12 at 15:13
  • 1
    @zafeiris.m: no, unfortunately - this is a new feature in SQL Server **2008** – marc_s Apr 09 '12 at 15:14
3

You are looking for a filtered index. See:

http://technet.microsoft.com/en-us/library/cc280372.aspx

Rory Hunter
  • 3,425
  • 1
  • 14
  • 16
  • Thanks for the fast answer [i have too few reputation to vote you up, sorry :)]. I'll check this and let you know – zafeiris.m Apr 09 '12 at 13:50