I have 2 tables with same column e.g. (col1,col2,col3) and primary key on col1.
when i select record like
select col1,clo2,col3 from table1 where col1 > some condition
here col1 is PK and its come in select as well as where condition
select col1,clo2,col3 from table2 where col3 > some condition
here col1 is PK and its come in select condition only
select clo2,col3 from table2 where col3 > some condition
here PK columns is not select
so my question is how index is seeking in all 3 cases.
because i try query execution plan for 3rd case its show index is seeking.
then why it seek index with any PK column in selection or where condition.
You would need an index on col3. In the first instance the PK index is used for col1 but in the other two your where uses col3.
Let us know if you need help with the index
in 3rd case if i dont select or use col1 which is PK here then why index seeking is done? and indexing is present on col1 only
You have index on the Col1 and you are not used in the query.
Table is having a clustered index because index scan will be used In the query plan. Even if you are not used the column.