Bitwise and logic in sql server

Question

I have a table that stores flag values in an integer column.(I am not the designer)

I have a query where in one of The where conditions is

flags & 256==0

The above condition is true for all unsigned numbers where the 8th bit is not 1

The question is, in sql server ,while converting the int to binary,its taking too much time and the performance of query is degraded with lot of other joins.

Is there any way to replace the above condition with a simple consition ?

Answer 1

From what you say, I gather that you want to express flags & 256 with sargable operators.

This will be easier if your flag is one of the biggest used bits (i.e. few bits above the 8th are used). For this, we need to remember that a number written on b bits has a value between 0 and 2^b-1.

Meaning of the 8th bit on flag's value

• If the 8th bit is the biggest used, your flags, represented as numbers, have values between 0 and 511. It is then trivial to see that flags & 256 > 0 is equivalent to flags >= 256, since any binary number with its 8th bit not set is a number on 7 bits, and thus has values between 0 and 255.

• Ideally, to express this without a bitmask we would like to do (flags % 512) >= 256, however I suspect that won't be sargable either. Thus we will need to enumerate the intervals where the 8th bit is set, for each possible combination of flags above the 8th. Easy if only 1 or 2 are used, but harder otherwise.

Example of intervals with 10 bits

Suppose you use 10 bits for flags, then your bit can be prefixed by 00, 01, 10, or 11. Because those are the bits 10 and 9, the corresponding numbers (assuming these prefixes are suffixed by 8 zeroes) are 0, 512, 1024, and 1536.

Thus all the interval of values for 'flags' with the bit 8 not set are :

• [0,255]
• [512,767]
• [1024,1279]
• [1536,1791]

Thus flag & 256 == 0 is equivalent to flags BETWEEN 0 AND 255 OR flags BETWEEN 256 AND 511 OR flags BETWEEN 512 AND 767 OR flags BETWEEN 768 AND 1023

Generalizing the intervals

Supposing you have F flags possible (aka number of bits used) and you are interested in the flag C (thus that bit's position).

The intervals with the flag set to 1 are, for all i < 2^F-C, [(2i+1)2^C, (2i+2)2^C-1]

The intervals with the flag set to 0 are, for all i < 2^F-C, [(2i)2^C, (2i+1)2^C-1]

As you can see, you have 2^F-C intervals to consider. So if it is at all possible to bribe the guy designing you SQL database, make him put that flag as the biggest possible : the F=C and you only have one flag to take care about.

Here's a quick & dirty bash script that will write the ugly intervals clause for you :

#!/usr/bin/env bash

bits=10 # F, total number of possible flags
check=8 # C, position of the flag

grain=$(( 2 ** check ))
interval_offsets="$(seq 0 $grain $(( 2 ** bits -1 )) )"


str=
for i in $interval_offsets 
do str="$str OR flags BETWEEN $(( i )) AND $(( i + grain - 1))"
done

echo "intervals for bit $check NOT set : ${str# OR}"


str=
for i in $interval_offsets 
do str="$str OR flags BETWEEN $(( i + grain )) AND $(( i + 2 * grain - 1))"
done

echo "intervals for bit $check set : ${str# OR}"