1

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?

1

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.

2
  • 1
    Thank you! I apparently need to read up on sql server indexing.
    – ZNS
    Dec 3 '20 at 9:15
  • No problem! It wasn't long ago that I had the same tribulations with indexing before I understood how it works with the B-Tree.
    – J.D.
    Dec 3 '20 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.