How to order the where clause and join columns in a covering index?

Question

Simple query example:

Select t1.A, t1.B, t1.C, t1.CustomerID
from table1 t1
join table2 t2 on t1.CustomerID = t2.Id
where t1.A = 'x'
and t1.B = 'xx'
and t1.C = 'xxx'

I created a non clustered with A, B, C, CustomerID for table1

I turned on statistic io. I recreated the same index and moved the order of the columns around and ran the queries at least twice for each order and checked the logical read count. After several trials, I noticed that one order: A, B, CustomerID, C produced the least number of logical reads, instead of something like A, B, C, CustomerID. I know this is a simple query and I haven't indicated the schema.

In general, it seems the order matters but I thought if I placed the where clause columns together, it doesn't matter in what order as SQL Server will figure it out. In the case above, the join column came between the where clause columsn and had an effect. I am not sure why? Does column selectivity matter in deciding where to place the column in the order? What are other considerations? Right now I am just doing it by trial and error.

Answer 1

If all your filters are equality matches (such as =, rather than >, LIKE, !=, etc), then it's going to useful to list them first. Then the data that matches those three conditions will be easily locatable, and in CustomerID order, which gives good options for being used in a join. Remember that your matching on CustomerID needs to join on several things.

Oh, and if your CustomerID is non-nullable and has a FK, then the Query Optimizer might not even be looking at t2.