This is really not a T-SQL anti-pattern as much as it is a database design issue, but we see it so often that it’s worthwhile bringing it up and clarifying things.

For illustrating the scenario, let’s examine the table Person.Contact in the AdventureWorks database. It has 2 columns called FirstName and LastName. Let’s say an application frequently queries this table with these columns in the WHERE clause. The query looks like this:

SELECT ContactID from Person.Contact

WHERE FirstName = ‘Carla’ and LastName = ‘Adams’

 

In order to support the query for seeking to the data, we create this index:

create nonclustered index idx_contact_names on Person.Contact(FirstName, LastName)

 

Now, let’s say there’s another application which fires another query on this table, and that query looks like this:

SELECT ContactID from Person.Contact

WHERE LastName = ‘Alberts’ and FirstName = ‘Amy’

 

Notice the difference between the 2 queries: the predicate ordering in the expression is different. Now, for the problem: some developers will now create another index, with the column order as (LastName, FirstName). That is not required. If you view the execution plan for both the queries, you will notice that the index is being used!

 

If you end up creating 2 indexes for the above scenario, SQL Server will effectively use only one of them for queries such as the above. The other index will only add to the overhead of index maintenance required during DML operations. So it is a redundant index and as such should be dropped.

 

Conclusion

The predicate order is independent of the choice of index to be created / the choice of index being used. SQL Server will use the index which yields a good plan.