SQLTeam.com | Weblogs | Forums

Indexing on Columns That Allow Nulls


#1

This is MSSQL 2014.
In older versions, if a column was nullable and was included in an index, SQL Server might ignore that index.
I have several indexes where almost all the columns can contain null values.

The indexes appear to be healthy (dbcc showcontig shows high density and low fragmentation).
My question is in 2014, will SQL Server use an index if it's on columns that allow nulls? Or does it usually ignore it and do a table scan?
And how can I know that the index will be used? The table has millions of rows.
Thanks, John


#2

I'm not sure I understand your point that "In older versions, if a column was nullable and was included in an index, SQL Server might ignore that index.", to my way of thinking the only issue is whether the index is selective for the column.

Seems to me that the only time NULLs matter in an index is if the index is not highly selective for that column. If you have an index on a column that stores either 1 or 2, and 99% of the values are "1" then the index will be very useful for any query that is looking for "2", but useless to find "1". (Assuming that the Statistics for that index are up-to-date)

If your queries (for columns that are indexed) never try to match NULL values - i.e. you only look for non-NULL values - it might be worth adding a filter to the index to explicitly exclude NULL values. That will save index space, and inserts/updates

For a multi-column index you may want to change the order of columns so that the most selective column is first ... notwithstanding that an index on A, B, C columns can be used for a query on A, and a query on A & B, as well as A, B & C - but not for a query just on C.

That's two questions (I think !)

You can look at the Query Plan for a specific SQL Statement and see what index(es) the planner will use for that specific query.

You can look at the DMVs to see if that index has been used frequently/not at all (in the time interval that the DMV is holding data for - which may be dependent on when the server was last restarted, and how much memory is available), which can help figure out that an index is never used - but: beware of an index that is critical for a monster report that is only run, say, quarterly.

And a third one!! is that you can ask SQL to recommend any [new] indexes that would help queries - best not to just blindly create them though, use it as a guide as to "Why does SQL want THAT index" along with "Can I adjust an existing index to do dual-duty"

Might help if you post some code (CREATE TABLE, CREATE INDEX and a sample SQL Query) for specific examples that aren't working the way you expect/want and I'm sure folk here will be happy to make suggestions.


#3

Yes, SQL will use an index with NULLable columns. NULL is effectively just another "value" in an index. The index will be searched normally, just like any other index would be.


#4

Thanks Kristen and Scott. That info clears up some misconceptions I had.


#5

One other point. All NULLs in an index are considered equal. Therefore, if you define a UNIQUE index, only 1 value for that column can be NULL.