question

ks2007 avatar image
ks2007 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?
index
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
seanlange avatar image
seanlange answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ks2007 avatar image
ks2007 answered
if only the left most column is specified will it be still using the index (index seek)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
What do you mean by the "left most column"?
0 Likes 0 ·
ks2007 avatar image ks2007 commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.