CODEWITHSUNDEEP
sql-serverdatabase-design
Tedderz
Tedderz

Reputation: 587

Using a bit mask to store unrelated bit columns

I'm designing a table. There are several bit columns that are unrelated to each other.

I'm considering just merging these columns together into a generic ATTRIBUTES INT bit mask column.

Now, I'm trying to convince myself that this is a bad idea. It seems like an anti-pattern. It might save me some time if I need another bit column later down the line, but are there really any benefits beyond that?

If I have an index on a bit mask, and I'm searching by ATTRIBUTES & 128 = 128, then I suspect that it's not efficient. The index would have everything ordered by the whole value, rather than by the bits. So, I imagine it would actually have to just scan the entire index instead of knowing exactly where to go.

Does that sound right? Is this a horrible idea?

Thanks, Tedderz

Upvotes: 3

Views: 1163

Answers (1)

Sebastian Meine
Sebastian Meine

Reputation: 11813

Using a bitmask column makes

  • the code harder to read and maintain
  • index use impossible

It also violates the normal forms of database design.

You are not buying space either as SQL Server can compress multiple bit columns into a single byte.

However, as bit columns naturally aren't very selective, index use might not be advantageous here either.

Overall the benefits of single bit columns clearly outweigh the benefit of a bitmask column. So I would go with single bit columns in almost all cases.

Upvotes: 4

Related Questions