Auxiliary Tables, Bit by Bit

Using Auxiliary Tables
To set up an auxiliary table solution, first break a value into its respective bits. The code in Listing 3 creates an auxiliary table called Bits and populates it with 32 binary values, each of which has a different bit turned on. Then, join the Bitvalues table to the Bits auxiliary table, as in the following query:

SELECT *
FROM Bitvalues JOIN Bits
ON data_bits & bin_val = bin_val

Note the special JOIN condition. The query performs a bitwise AND between each value in the Bitvalues table's data_bits column and each value in the Bits auxiliary table's bin_val column. Each bit that is turned on in a data_bits value gets a matching bin_val value in which the same bit is turned on. So, a value of 7 in the data_bits column gets three matching rows in the Bits auxiliary table with the bin_val values 0x00000001, 0x00000002, and 0x00000004. Table 2 shows the results.

The second and final step in solving the problem is summing the distinct values for each group. This part is fairly easy after you break up the values in the data_bits column. Just perform a GROUP BY query in which, for each group, you use the SUM(DISTINCT) function to calculate the distinct sum of all the values that were broken up, as the following query shows:

SELECT
  group_col,
  SUM(DISTINCT(CAST(bin_val AS int))) AS or_data_col
FROM Bitvalues JOIN Bits
  ON data_bits & bin_val = bin_val
GROUP BY group_col

Note that the Bits auxiliary table stores values in binary. To sum those values, you must convert them to integers.

Which Way Is Better?
You can calculate an aggregate bitwise OR with or without an auxiliary table, but which approach is better? I compared the performance of the long SUM(DISTINCT) query, the long MAX query, and the short SUM(DISTINCT) query that uses an auxiliary table. The long SUM(DISTINCT) query performed considerably worse than the other two. The short SUM(DISTINCT) query performed slightly worse than the long MAX query, but it requires much less code.