How to force MS SQL Server to perform index join?

Question

I am working on an assignment where I have to compare different join methods in SQL Server, namely hash join, merge join, and index join.

I'm having difficulty getting SQL Server to merge indexes. Can someone show me how I can get it to use index join (using a join hint or the like) or just simply provide a simple query with a join on which the SQL server uses the index method?

Answer 1

You only have Loop, Hash and Merge (BOL) . No index is combined.

More than you ever needed to know, Craig Friedman's JOINs series (he is one of those who developed the relationship engine for SQL Server)

Answer 2

I am having trouble finding such terminology on an SQL server.

http://en.wikipedia.org/wiki/Join_(SQL)#Join_algorithms

Are you just looking for a nested loop that uses indexes, which leads to an index search?

Answer 3

, , .

SELECT blah FROM table WITH (INDEX (index_name))

(?) join

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

:

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);