How do I force MS SQL Server to perform an index join?

Question

I'm working on an assignment where I'm supposed to compare different join methods in SQL Server, namely hash-join, merge-join and index-join.

I'm having difficulties getting SQL Server to perform an index-join. Can anyone show me how I can force it to use an index-join (using a join hint or similar), or just simply provide a simple query with a join on which SQL server uses the index-join method?

Solution 1

You have Loop, hash and merge joins (BOL) only. No index joins.

For more than you ever needed to know, Craig Friedman's series on JOINs (he's one of the team that designed the relation engine for SQL Server)

Solution 2

You can have an Index hint on straight select, but I'm not sure that the same syntax is available for a join.

SELECT blah FROM table WITH (INDEX (index_name))

you could use this in a non-ansi (?) join

SELECT blah FROM TABLE1, TABLE2
WHERE TABLE2.ForiegnKeyID = TABLE1.ID
WITH (INDEX (index_name))

Join with a index hint:

SELECT
    ticket.ticket_id
FROM
    purchased_tickets
JOIN   ticket WITH (INDEX ( ticket_ix3))
    ON ticket.original_ticket_id = purchased_tickets.ticket_id
       AND ticket.paid_for = 1
       AND ticket.punched = 0
WHERE  purchased_tickets.seller_id = @current_user
OPTION (KEEPFIXED PLAN);