Non clustered index not used for nullable column

Posted on

Question :

I’m having a strange (to me) issue with a non clustered index on SQL Server 11.0. I have a quite large table with about 4 million rows. I’ve created a non clustered index which looks like this

Sql Server index

Neither is colum is primary key

I then run the follow queries with different results. One uses the non clustered index but the other does not and I can’t figure out why. The only difference I can see is that AggregateEntityId is nullable.

Sql Queries

The second query takes about 15 seconds the first takes about 50 milliseconds. Why doesn’t the second query use the non clustered index?

Answer :

This is because your nonclustered index covers predicates for “EntityId” and “AggregateEntityId” or only “EntityId” based on the order that the columns are listed on the index.

This is because the B-Tree that stores the nonclustered index data first sorts on EntityId then by AggregateEntityId.

Since your second query doesn’t use EntityId in the WHERE clause, the nonclustered index is invalid in this case because it’s unable to efficiently locate AggregateEntityId without being able to first filter on EntityId.

You would need another nonclustered index that starts with AggregateEntityId (or only contains AggregateEntityId) under the Indexed Columns.

Leave a Reply

Your email address will not be published. Required fields are marked *