Posted by Ami Levin on Tuesday, 11 December 2012
T-SQL Tuesday is now entering its fourth year! Sebastian Meine, who's hosting this month's T-SQL Tuesday, chose joins as this month's subject, to correspond with his "A join a day" blog series.
Although the “Join-a-day” series focuses mostly on the logical aspects of joins, under the covers there is another aspect which is the physical operators that SQL Server chooses to implement your ‘logical’ joins. As part of the optimization process, the SQL Server Optimizer uses statistical information to try and evaluate which physical operator will be the optimal one to use in regards to your join and the rest of the query plan. If you are unfamiliar with the physical join operators, I recommend you read our free E-book which explains in detail how each operator works and what are the main considerations of the SQL Server optimizer when choosing one operator over another. For you convenience, here is the summary table that sums the main characteristics of each physical join operator:
