SQL Server inner join

Question

How to choose main table when joining multiple tables using inner join?

A) Should I choose main table depending on its number of columns/rows (For example large main as main table or to keep larger table as join table)?

B) If I choose the table containing column that I use in where condition as main table , will there be any performance benefit ?

For example lets say there are 2 tables. Table1 & Table2 . Will there be any performance difference between two solutions given below

Solution 1 :

select t1.empid , t1.name , t1.dept , t2.add , t2.city , t2.country
from Table1 t1
inner join Table2 t2 on t2.empid = t1.empid
where t1.year = 2010

Solution 2 :

select t1.empid , t1.name , t1.dept , t2.add , t2.city , t2.country
from Table2 t2
inner join Table1 t1 on t1.empid = t2.empid 
where t1.year = 2010

Answer 1

There is no difference. SQL Server will pick "main" table and join type based on table statistics.

Example: Table1 contains 5 rows (and only one with year 2010). Table2 contains 10000 rows. SQL Server will generate Nested Loops join with Table1 as outer input, Table2 as inner input, to get 1 run over 1000 rows. It will definitely not generate 10000 cycles over 1 row.

You still can get different execution plans for statements above, but only in case if SQL Server will decide that plan should be trivial and will skip the optimization phase (because tables are almost empty, for example).