Is there any advantage of using int vs varbinary for storing bit masks in terms of performance or flexibility.
For my purposes, I will always be doing reads on these bit masks (no writes or updates).
+1
A:
Well, considering an int has less storage space and is generally a little easier to work with I'm not sure why you'd use a varbinary.
Chris Lively
2009-04-30 22:22:09
+7
A:
You should definitely use an INT (if you need 32 flags) or BIGINT (for 64 flags). If you need more flags you could use VARBINARY (but you should probably also ask yourself why you need so many flags in your application).
Besides, if you use an integral type, you can use standard bitwise operators directly without converting a byte array to an integral type.
Ronald Wildenberg
2009-04-30 22:30:50
yeah, or event the smaller types like tiny int for smaller bit masks? I have never used a tiny int as bit mask yet, but should be no issue. int's work great.
Chad Grant
2009-04-30 23:10:35
Smaller types for smaller masks is also a good idea.
Ronald Wildenberg
2009-05-02 08:23:50
+2
A:
It is generally considered preferable to use a bunch of bit columns instead of a bit mask. They will get packed together in the page, so they won't take any more room. Although I too always seem to go with an int or bigint column to avoid all of the column name typing.. but with intellisense I would probably go with the bit columns.
I disagree, bitmasks still have their place and they are a great way to leave your options open without having to refactor your DB and SELECTS/VIEWS/SPROCS just to add a simple bool, they are very flexible ... just most programmers dont understand what they are these days
Chad Grant
2009-04-30 23:12:17
Programmer ignorance sometimes shows we're making good progress abstracting low-level optimizations away inside engines and frameworks. INDIVIDUAL BIT FIELDS: 1. Are handled efficiently by the engine (packed together, manipulated by native code rather than TSQL). 2. Have meaningful names making your queries more understandable (rather than using arbitrary bit index numbers). 3. Can have indexes placed on them, accelerating queries. 4. Can have individually configurable default values. 5. Are easier to refactor to a larger type later if you decide the field needs more than two states. etc.
Triynko
2010-10-27 20:54:43
Exactly. Bitmasks in SQL just kill what SQL can do - you will never be able to query them efficiently. Ergo they should not be used.
TomTom
2010-10-28 14:48:30
+1
A:
I usually agree with @hainstech's answer of using bit fields, because you can explicitly name each bit field to indicate what it should store. However I haven't seen a practical approach to doing bitmask comparisons with bit fields. With SQL Server's bitwise operators (&, |, etc...) it's easy to find out if a range of flags are set. A lot more work to do that with equality operators against a large number of bit fields.
Rick
2009-04-30 22:49:00
Except... hat bitwise operators FORCE A TABLE SCAN which kills performance while indivitual bit fields can efficiently be queried.
TomTom
2010-10-28 14:49:13