3

I'm designing a database schema at the moment, but have a performance query.

Our table needs to have (at least) two flags, or boolean values. We can call these IsComplete and IsExportable. The obvious way to put these into a table is as two separate bit columns, but it occurred to me that we could put it into a byte column and use bitwise operators to query against it.

Which method would yield the best performance? Both flags will be queried often, and so filtering on them should be as fast as possible. Would an index on the byte column improve its performance?

One reason I like the byte column is because if another flag is required in the future, it can easily be made so. Whereas with separate bit columns, an additional column will need to be added.

For clarification, the database will be stored in SQL Azure. I suppose this might make some difference!

2

The answer to any performance question is "it depends." Discrete columns can be fast and byte column flags can be fast too. In absolute terms you can probably save a bitwise operation by having discrete columns here and there so discrete columns are theoretically faster.

A theoretical bump shouldn't be the main reason to choose a strategy. To paraphrase Jeff Atwood "storage is cheap and BDA's are expensive." I would avoid the flags compressed into a byte column for the simple reason that it makes your life more complicated by making your data more cryptic. Discrete columns will be straightforward to query, filter properly, debug, and pass on to future teammates.

For completeness there are other options than bit flags, or discrete columns. One other option is the EAV (entity-attribute-value) model, even though it doesn't feel like this is your best option based on your description.

Entity–attribute–value model (EAV) is a data model to describe entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.