In the last year I’ve published articles on indexes to include Indexes: When Column Selectivity Is Not Always A Requirement – SQLServerCentral and Query Optimizer Suggests Wrong Index and Query Plan — Why? – SQLServerCentral. This article is a continuation of just how the optimizer interacts with the index wizard.
We’ve all heard and read that you should always define an index (for a specific query) using first the columns used in an equality expression in the WHERE clause, but today let’s look at an example and see just what happens.
Consider the following query. This is from