如何强制MS SQL Server执行索引连接?

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.

我正在做一个作业，我要比较SQL Server中不同的连接方法，即hash-join、merge-join和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?

我很难让SQL服务器执行索引连接。有人能告诉我如何强制它使用一个索引连接(使用一个连接提示或类似的)，或者仅仅提供一个带有连接的简单查询，SQL服务器在该连接上使用索引连接方法吗?

3 个解决方案

#1

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

您只有循环、散列和合并联接(BOL)。没有索引连接。

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)

对于您所需要了解的更多信息，Craig Friedman关于join的系列文章(他是为SQL Server设计关系引擎的团队之一)

#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

您可以在非ansi(?)连接中使用这个

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

#3

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

我很难在SQL server中找到这样的术语。

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

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?

您是否正在寻找一个使用索引的嵌套循环，从而导致索引查找?

#1