Non clustered index not used for nullable column
This is because your nonclustered index covers predicates for &quot;EntityId&quot; and &quot;AggregateEntityId&quot; or only &quot;EntityId&quot; 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.