Press "Enter" to skip to content

Fun with Multiple Indexes

Erik Darling makes a fairly rare multi-index sighting:

Notice! Both of our nonclustered indexes get used, and without a lookup are joined together.

Because the predicates are of the inequality variety, the join columns are not ordered after each seek. That makes a hash join the most likely join type.

I’ve always had this belief that there are probably more cases in which multi-index solutions are useful than the SQL Server optimizer gives us. This belief may be irrational.