SQLTeam.com | Weblogs | Forums

Querying against integers using the AND (&) math operator


I have a device which uses bit flags, stored within a byte value. For example, each byte contains 8 bits, and if bit 1 is not 0, it indicated a tamper switch was activated. I have to store this information into a database. For optimization, I'd rather just store the one byte (containing all 8 flags), instead of creating a field for each entire bit flag independently. The problem is, running a query against all values that have bit 1 active. This is easily done using a simple math func/operator of AND. The problem is, can you use something like this within a SQL query?

I hope this is making sense...

Please let me know if I need to explain further.


Nevermind... it does seem to work (was finally able to test it, so my apologies about jumping the gun and creating the post before I was able to).

SELECT * FROM device_events WHERE flags & 8 > 0

I do have a question though, in how using a mathematical query statement like this affects performance. Would somebody familiar with SQL do this, or advise that I split the flags up into their own fields and run a simple:

SELECT * FROM device_events WHERE tampered = 1



I would suggest that you do

flags & 8 = 8

either works fine for checking a single bit, but if you want to check for multiple bits you need an equality test

flags & 10 = 10


flags & 10 > 0

with be true if either, or both, the "2" and the "8" bits are set.

I have seen it suggested that

flags >= 10
AND flags & 10 = 10

performs well (if [flags] is indexed) because the first statement allows a range test on the index, and then the bitwise AND test is applied to those rows (only)

But I don't know how well

flags2 = 1
AND flags8 = 1

would perform by comparison. Sometimes it is easier to assemble a single INT value of all the bits required, rather than compare individual, separate, bit-columns