How to reduce index scan by avoid scaning null values
indexing, non-clustered-index, nullable, sql-server-2008-r2, t-sql
Solution
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.
Problem
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.