Multi-bit database field

Question

I'm using a MSSQL database and would like to create a column that only has 4 possible values. Is there any way to define a 2-bit column? I see the bit datatype and then the next smallest is tinyint which is 1 full byte.

If there is no such field, I'd be interesting in finding out why not.

Thanks.

Answer 1

What is your concern with field size? The way to implement a field domain should be using a Foreign Key constraint (MSSQL should have that).

Answer 2

So you're saying that I should just have a foreign key to another table that contains my four values?

I have no serious concern with it. I just don't see the point of giving a full byte to something that only needs 2 bits. I'm more curious as to the reasons why a variable bit-length field doesn't exist.

Answer 3

I am with friol on this.

I would create a lookup table that contains your four possible values, and then link to them via a foreign key.

As for the data type, smallint is as small as you are going to get, SQL Server really isn't designed to hold individual bits of data and manipulate them, but it can be done via Bitwise operators in Binary columns, but even the smallest of those is much larger on disk. The SQL Server Bit column is also unusual as it has 3 potential values (1, 0, and null).

Answer 4

I don't see the reason for such a field.

It definately cannot be space - that's cheap today, and an economy of 6 bits is really negligible unless you have lots of rows. But in that case it will take up much space anyway, and extra 6 bits per row will again hardly make a difference.

Speed is also not a valid reason. Such bit fields would require additional manipulation to store and retrieve, making the whole process a lot slower than simply dealing with a whole byte.

Lastly, if it's about constraints, then you should use foreign keys or user-defined types (though that would require additional coding in .NET). Although I'd simply use a byte and implement the constraint in my application. Not so nice perhaps, but heck of a lot easier.

Update: Although, yes, an "enum" constraint would be useful in MSSQL. But that's again a little different than simply a 2-bit field.

Answer 5

Without rehashing what the other gentlemen have said, If you still want to do this, there is no direct way... But you could use two Bit columns,

and then add a computed column that generates the values (0-3) that correspond to the values of the 2 bit columns....

CREATE TABLE [dbo].[testTable](
    [colA] [bit] NOT NULL,
    [colB] [bit] NOT NULL,
    [CalcCol]  AS (case [colA] when (1) then (2) else (0) end+[colB])
) ON [PRIMARY]

if you need a different set of four values then 0-3 just put them into the calculation formula:

CREATE TABLE [dbo].[testTable](
    [colA] [bit] NOT NULL,
    [colB] [bit] NOT NULL,
    [CalcCol]  As 
         (Case ColA 
             When 0 Then Case ColB WHen 0 Then ValueA Else ValueB End
                    Else Case ColB WHen 0 Then ValueC Else ValueD End
             End)
) ON [PRIMARY]

Only issue is that calculated column is not directly "writable" - you'd have to write to the individual bit fields in separate code... like

Update TestTable Set 
  colA = Case When Value In (ValueA, ValueB) Then 0 Else 1 End, 
  colB = Case When Value In (ValueA, ValueC) Then 0 Else 1 End 
Where ...

Answer 6

Unless your database is being stored on a floppy disk, I wouldn't sweat it. Take the advice of the rest here and move on with building the rest of the database.