Listing 1: Forcing a Merge Join Gives Worse Performance
USE Northwind
 
-- Copy the two tables.
SELECT *
INTO  o1
FROM Orders

SELECT  *
INTO od1
FROM [Order Details]

-- Create the indexes.
CREATE CLUSTERED INDEX orders_index ON o1(orderID)
CREATE CLUSTERED INDEX OD_index ON od1(orderID)

SET STATISTICS IO ON
[Start callout A]
SELECT *
FROM  o1  JOIN od1
ON o1.orderID = od1.orderID

-- Now force a merge join.
SELECT *
FROM  o1 INNER MERGE JOIN od1
ON o1.orderID = od1.orderID
[End callout A]



1