asked
Composite Primary Key
Does order matter in where clause, if the left most column is not specified in the where clause(in that case its already doing an index scan) does SQL server automatically select most selective predicate first?
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
No the order of columns coded in your where clause have no bearing on the execution plan that the engine generates. If you are only using a portion of an index it will always be a scan. It can't do a seek because you are only specifying a portion of the index.
if only the left most column is specified will it be still using the index (index seek)
If Primary key is on (col1,col2,col3) Col 1 asc, Col 2 asc, Col 3 asc, Query with where or join on part of the pk say col2 or col3 results in index scan Where as query with where or join on left most col of the pk say col1 results in index seek In both case part of the pk is used.
20 People are following this question.
