Bits vs Char - What is the best way to store 3 mutually exclusive flags?

Question

I have a table that is designed to track various elements. Among other properties, elements can be either A, B, or C, each of which excludes the rest. Is it recommended to store this information as a character or as 3 sets of bits (isA isB, isC, etc.) or some other method? I could understand the use of a character if in the future I need more data types, but it also seems to me that using bit data types will consume less memory. Or am I analyzing this too much and will the difference be so tiny that it doesn't even matter?

Answer 1

Or am I analyzing this too much and will the difference be so tiny that it doesn't even matter?

Yes a little.

But you should understand that there is an important difference between your project proposals: having a char column will make the exception work exceptionally. Areas of IsX (alone) will not. Explanation: With the IsA and IsB , you can probably set both true values ​​to the same record, unless you use another mechanism to prevent this (trigger, check constraint, etc.).

In addition, having a new column whenever a new value is possible is not a good database design.

Answer 2

Just use Char .

Spatially, you will use an additional 625kb per million lines (assuming that 5 bits are saved in each line, which is the best saving scenario).

This is not very.

To put this in perspective, 625 MB per MILLION . When you get into tables of this size, you don’t need any units that do not start with giga , tera or peta .

Internally, SQL Server saves them as bytes independently (up to 8 bit fields).

By the time space matters, any architecture changes (from using bit fields to something more flexible) will be extremely painful.

Answer 3

I would use one char, byte, enumeration, whatever. If the states are mutually exclusive, then this is not the best use for flags.

Answer 4

Think it's really difficult, but kind of crazy, the way to get your script out is to keep them in the zero bit.

"Integer is a data type that can be 1, 0, or NULL."

but I don’t quite understand how they pull it out, because

"The SQL Server database engine optimizes storage of column bits. If a table has columns with 8 or fewer columns, the columns are stored as 1 byte."

Both of http://msdn.microsoft.com/en-us/library/ms177603.aspx

If you need to index three values, I would choose tinyint instead of three bit fields.

Answer 5

I would use a tiny int , basically a single byte from 0 to 255. As your potential values ​​expand, you end up using crazy letters that mean nothing. So, I just start with numbers. Saving three mutually exclusive bits is not worth the hassle; they will accept storage bytes anyway.