HASH UNION

The HASH UNION operator uses Adaptive Server hashing algorithms to simultaneously perform a UNION ALL operation on several data streams and hash-based duplicate elimination.

The HASH UNION operator is a nary operator that displays this message:

HASH UNION OPERATOR has <N> children.

<N> is the number of input streams into the operator.

HASH UNION also displays the name of the worktable it uses, in this format:

HASH UNION OPERATOR Using Worktable  <X>  for internal storage.

This worktable is used by the HASH UNION operator to temporarily store data for the current iteration that cannot be processed in the memory currently available.

This example demonstrates the use of HASH UNION:

select * from sysindexes
union
select * from sysindexes

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.

3 operator(s) under root

    | ROOT:EMIT Operator (VA = 3)
    |
    |    |HASH UNION Operator has 2 children.
    |    | Using Worktable1 for internal storage. 
    |    |
    |    |    |SCAN Operator
    |    |    |  FROM TABLE
    |    |    |  sysindexes
    |    |    |  Table Scan.
    |    |    |  Forward Scan.
    |    |    |  Positioning at start of table.
    |    |    |  Using I/O Size 2 Kbytes for data pages.
    |    |    |  With LRU Buffer Replacement Strategy for data pages.
    |    |
    |    |SCAN Operator (VA = 1)
    |    |    |  FROM TABLE
    |    |    |  sysindexes
    |    |    |  Table Scan.
    |    |    |  Forward Scan.
    |    |    |  Positioning at start of table.
    |    |    |  Using I/O size 2 Kbytes for data pages.
    |    |    |  With LRU Buffer Replacement Strategy for data pages.