Microsoft KB Archive/274075

= INF: Hash Hint Is Ignored When a UNION Operator Follows an IN Operator =

Article ID: 274075

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q274075



SUMMARY
The hash union hint in the option clause is ignored when the UNION operator follows a WHERE clause with an IN operator.



MORE INFORMATION
The execution plan for the following query shows concatenation instead of hash. The hash union hint in the option clause is ignored. Because the UNION is part of an IN predicate, duplicates are irrelevant. To ignore duplicates, the UNION operator is converted into a UNION ALL operator. The UNION ALL is implemented only as CONCAT. The hash operation is only needed if duplicates must be removed. The UNION operator specifies that multiple result sets are to be combined and returned as a single result set. The UNION ALL operator includes all rows in the results, including duplicates. If ALL is not specified, the duplicate rows are removed. Because duplicates are allowed, there is no need to use a hash operation. Select au_id from authors where au_id IN (

( SELECT titleauthor.au_id      FROM  titleauthor, sales        WHERE (titleauthor.title_id = sales.title_id)         AND (sales.title_id ='BU1032')    )

UNION ( SELECT C.au_id       FROM  authors  C       WHERE (authors.zip = '84152')         AND (authors.au_id = C.au_id)    ) ) option(hash union)

Keywords: kbinfo KB274075

-

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

© Microsoft Corporation. All rights reserved.