• 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???