Microsoft KB Archive/197247

= INF: "OR" Clause Prohibits the Use of Merge or Hash Algorithms =

Article ID: 197247

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q197247



SUMMARY
Hash and merge algorithms can be used in a join operation between two or more tables only if there is at least one qualified equality comparison between the join attributes. An OR clause disqualifies all equality comparisons between joined tables.



MORE INFORMATION
A query like the one below will always use nested loops to join tables:

select * FROM discounts a INNER JOIN stores b     ON a.stor_id = b.stor_id or b.stor_id like '78%'

If you attempt to force this query to use hash or merge you will get a Query Processor error. This is because the OR condition disqualifies a.stor_id = b.stor_id as a join predicate.

SQL Server Books Online notes that merge and hash joins can be used only if there is at least one equality (WHERE) clause in the join predicate.

A requirement of the hash and merge algorithm, however, is that if a row fails the equality portion of the predicate, it is considered a non-match. The non-matched rows are consequently unavailable for any further processing. But if there is an OR clause in the expression, a row may fail the equality portion of the predicate but still be a match from the other side of the OR clause (for example, "b.stor_id like '78%'").

Additional query words: prodsql algorithm 8622 query plan

Keywords: kbinfo KB197247

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.