Introduction

All developers are very much concerned about performance. If someone say that this increase performance, all the developer are running behind it. It is not a bad practice at all. Rather as per my point of view we must span all our effort related improve the performance of query.

“One common question that we find that, if we change the ordering of table join in case of inner join will effect or increase performance”

To understand it lets take a simple example of Inner join. There is two tables named Table-A and Table-B. We can us the Inner Join on both the table.

Like this

FROM [Table-A] AS a INNER JOIN [Table-B] AS b ON a.IDNO = b.IDNO

OR

FROM [Table-B] AS a INNER JOIN [Table-A] AS b ON a.IDNO = b.IDNO

Which one is best for performance?

To answer this question we all know that whenever a SQL Query is executed the MS SQL server create several query plans with different join Order and choose the best one.

That means the Join order that we are writing in the query may not be executed by execution plan. May be different join order is used by the execution plan. In the above case the execution plan decide which Join order he will chose depends on best possible costing of execution.

Here [Table-A] JOIN [Table-B] or [Table-B] JOIN [Table-A], MS SQL Server knows it well that both are same.