I am a big proponent of bitmasks but I have found that other SQL developers have a hard time coming to grips with the concept. (I am an engineer by trade, maybe that helps?)
When implementing bitmaps (or flags is another term) I suggest providing helper functions or stored procs to i) make the SQL more readable and ii) minimize errors in using bitmaps associated with using the wrong bitwise operator or bitmask.
I have written stored procs that will update (set/reset) individual bits based on the flagname (stored in a reference table). I also allow the user to pass the table and the keys/values to the dataset to be updated and dynamically generate the where clause.
Regarding performance, I was under the impression that using bitwise operators in a where clause was very efficient, but I may be completely wrong.
Is [ColumnName] & FlagNumberMask
quicker than
field1 = 1 and field2 = 1 and field3 = 1 and field4 = 0....
Would every bit field have to be indexed???