How to reduce index scan by avoid scaning null values

Question

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.

Answer 1

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.