SQLTeam.com | Weblogs | Forums

Inner join query plan confusion


#1

hi team,

i have two tables as ,

table1 : id int primary key-clustered index , tb1 int nonclustered index , name varchar(100)
table2 : id int primary key-clustered index , tb2 int nonclustered index , name varchar(100)

table1 have only one row
table2 have 300000 rows

my query is

select a.id
from table1 a inner join table2 b
on a.id = b.id

i am getting a clstered index seek on table2 column id and a index scan(non clustered ) on table1 column id.

But i am not able to understand why this is doing "index scan(non clustered ) on table1 column id" why not clustered index seek both the times.

Please clear my doubt.


#2

I think probably because of "table1 have only one row"


#3

A seek requires a specific value. Thus you can't possibly seek to the first table, because there's no known value to look for.

If you want to see a seek to the first table also, specify the id value in the query:

select a.id
from table1 a inner join table2 b
on a.id = b.id
where a.id = <id_value_in_table1>