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?
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:
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
because
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