MSSQL server and index join

Question

Hi,

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?

Answer 1

I'm having trouble finding such terminology in SQL server.

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

Are you just looking for a nested loop that uses indexes, resulting in an index seek?

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

Answer 3

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)

Answer 4

I have not seen it explained clearer as in this Powerpoint presentation:

www.jbjgroup.com/files/joinpres.ppt

Answer 5

http://www.youtube.com/watch?v=SmDZaH855qE