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.