SQL Server inner join -

how choose main table when joining multiple tables using inner join?

a) should choose main table depending on number of columns/rows (for example large main main table or keep larger table join table)?

b) if choose table containing column use in condition main table , there performance benefit ?

for example lets there 2 tables. table1 & table2 . there performance difference between 2 solutions given below

solution 1 :

select t1.empid , t1.name , t1.dept , t2.add , t2.city , t2.country table1 t1 inner join table2 t2 on t2.empid = t1.empid t1.year = 2010 

solution 2 :

select t1.empid , t1.name , t1.dept , t2.add , t2.city , t2.country table2 t2 inner join table1 t1 on t1.empid = t2.empid  t1.year = 2010 

there no difference. sql server pick "main" table , join type based on table statistics.

example: table1 contains 5 rows (and 1 year 2010). table2 contains 10000 rows. sql server generate nested loops join table1 outer input, table2 inner input, 1 run on 1000 rows. not generate 10000 cycles on 1 row.

you still can different execution plans statements above, in case if sql server decide plan should trivial , skip optimization phase (because tables empty, example).