Tuning with Execution Plans - Microsoft SQL Server 2012 Performance Tuning - page 118

Getting ready

We are going to create two tables to see the different effects of physical join operator in the
execution plan. Execute the following query to create those tables. We are going to make
some schema-level changes in the tables. As it is not a good idea to change the schema of
the original AdventureWorks2012 database, we will create two sample tables from the
table of AdventureWorks2012 :

USE AdventureWorks2012
GO

if object_id('SalesOrdHeaderDemo') is not null
begin
drop table SalesOrdHeaderDemo
end
GO

if object_id('SalesOrdDetailDemo') is not null
begin
drop table SalesOrdDetailDemo
end
GO

Select * Into SalesOrdHeaderDemo
from Sales.SalesOrderHeader
GO

Select * Into SalesOrdDetailDemo
from Sales.SalesOrderDetail
GO

How to do it...

Perform the following given steps to understand the Hash, Merge, and Nested Loop
Join strategies:

1. Execute the following query with the execution plan (press Ctrl + M to enable

Execution plan):
SELECT
sh.*
FROM
SalesOrdHeaderDemo AS sh
JOIN
SalesOrdDetailDemo AS sd
ON
sh.SalesOrderID=sd.SalesOrderID
GO