Store Multiple Bit Values in a Single Table Column

Store multiple bit values in a single table column

You could store this as a bitfield and then use boolean logic operators to retrieve the values

for example:

CREATE TABLE [dbo].[testBF](
    [field1] [varchar](max) NOT NULL,
    [field2] [varchar](max) NOT NULL,
    [bitfield] [int] NOT NULL CONSTRAINT [DF_testBF_bitfield]  DEFAULT ((0))
) ON [PRIMARY]

Then for selects:

SELECT field1, field2,
       CASE WHEN (bitfield & 1) = 1 THEN 'monday ' ELSE '' END + 
       CASE WHEN (bitfield & 2) = 2 THEN 'tuesday ' ELSE '' END +
       CASE WHEN (bitfield & 4) = 4 THEN 'wednesday ' ELSE '' END +
       CASE WHEN (bitfield & 8) = 8 THEN 'thursday ' ELSE '' END +
       CASE WHEN (bitfield & 16) = 16 THEN 'friday' ELSE '' END as [days of week]
FROM testBF

To find all days that contain tuesday flag (tuesday is the 2nd bit or 2^1 or 2)

SELECT * 
FROM aTable
WHERE (bitfield & 2) = 2

or

SELECT * 
FROM aTable
WHERE (bitfield & 2) != 0

Note, the template in the second case will work for any bit -- that is for friday (the 5th bit or 2^4 or 16) would be

SELECT * 
FROM aTable
WHERE (bitfield & 16) != 0

Finally the general case... pass in a number (1 for monday) you get

SELECT * 
FROM aTable
WHERE (bitfield & POWER(2,@inNumOfWeekday-1)) != 0

---

This seems like a lot of work to me, when you could just save it as 5 (or 7 bit fields) but that is how you could do it.

For more examples look at the gist I wrote for another question:

https://gist.github.com/1846338

and the answer:

https://stackoverflow.com/a/9302106/215752

Convert multiple bit columns into one field

Use a CASE WHEN <expr> or CASE <expr> WHEN <value> expression, then concatenate in an outer-query with CONCAT_WS.

• A CASE expression evaluates to NULL when an ELSE case is omitted, so these expressions are equivalent:

  CASE x WHEN 1 THEN 'foo' END
  CASE x WHEN 1 THEN 'foo' ELSE NULL END
  CASE WHEN x = 1 THEN 'foo' END
  CASE WHEN x = 1 THEN 'foo' ELSE NULL END
  

• The CONCAT_WS function is variadic and concatenates non-NULL strings with the separator string (the first argument).

  CONCAT_WS( ', ', 'a', 'b', NULL, NULL, 'c' ) == 'a, b, c'
  

Here's how I'd do it:

SELECT
    StaffId,
    CONCAT_WS( ', ', Type1Text, Type2Text, Type3Text, Type4Text ) AS TypeNames
FROM
    (
        SELECT
            StaffId,
            CASE type1 WHEN 1 THEN 'Type1Name' END AS Type1Text,
            CASE type2 WHEN 1 THEN 'Type2Name' END AS Type2Text,
            CASE type2 WHEN 1 THEN 'Type3Name' END AS Type3Text,
            CASE type3 WHEN 1 THEN 'Type4Name' END AS Type4Text
        FROM
            ...
    ) AS iq

The outer query can be elided if you don't mind making it slightly harder to read:

SELECT
    StaffId,
    CONCAT_WS( ', ',
        CASE type1 WHEN 1 THEN 'Type1Name' END AS Type1Text,
        CASE type2 WHEN 1 THEN 'Type2Name' END AS Type2Text,
        CASE type2 WHEN 1 THEN 'Type3Name' END AS Type3Text,
        CASE type3 WHEN 1 THEN 'Type4Name' END AS Type4Text
    ) AS TypeNames
FROM
    ...

Storing many bits -- Should I use multiple columns or a single bitfield column?

I think it would be easier to allow for future expansion if you have columns for each value. If you add another option in the future (which is likely for most applications like this), then it may affect all your other code since you would need to reparse your int column to account for the new bits.

How to store multiple values in a row, column or table? What is most efficient?

Since it appears a single user may have multiple weights at different times, you have a one to n relationships. You should create a second table (for instance, Measure) which refers to your User table.

This table could contain columns such as ID, UserId, MeasureDate, and weight.

You could also include height measure in this table if your users are not yet fully grown, and therefore susceptible to have varying height at different points in time. Otherwise, height could be stored inside user table.

On a side note, i would advise you to check database normalization for relational databases.

MySQL multiple bit columns or 1 enum column

If, as it seems, the flags represent states (that is, only one flags may be true at a given point in time), then I would recommend a single column, as integer datatype. Instead of using ENUM, you can use a referrential table to store all possible flags and their names, an reference it from the original table, using the integer column.

On the other hand, if several flags may be on (say, both is_error and is_test), then a single column is not sufficient. You can either create several columns (if the list of flags never changes), or use a bridge table to store each status on a separate row.

SQL Server packing multiple bit fields in a table

TL;DR; SQL Server will pack bit fields no matter the order of their definition.

If we were to run the following

create table test_dispersed (
    field1 int,
    field2 bit,
    field3 varchar(100),
    field4 bit,
    field5 varchar(100),
    field6 bit
)

create table test_contiguous (
    field1 int,
    field3 varchar(100),
    field5 varchar(100),
    field2 bit,
    field4 bit,
    field6 bit
)

insert into test_dispersed (field1, field2, field3, field4, field5, field6) values (123,0,'A string',1,'Another string',0);
insert into test_contiguous (field1, field2, field3, field4, field5, field6) values (123,0,'A string',1,'Another string',0);


SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDbName'), OBJECT_ID(N'[dbo].[test_dispersed]'), NULL, NULL , 'DETAILED')
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDbName'), OBJECT_ID(N'[dbo].[test_contiguous]'), NULL, NULL , 'DETAILED')

You will see for both tables the [max_record_size_in_bytes, min_record_size_in_bytes, avg_record_size_in_bytes] are all the same and equal across tables at 54 bytes.

How can I store more than one value in a SQL table's column?

You just need to find a standard way to store the information as a longer string.

So, for example, if you know '__' isn't going to show up in your data then you can store checkboxvalue__otherdata, but has risk.

As alex mentioned, if you use json, http://json.org, then you will put information into this object and then save it as a string, but to be safe you should base64 encode it.

Or you could store it in an xml file.

If you were just saving 2 bytes, for example, then you could push them into an int, and then decode it later.

Basically, just determine some way that works for you to store the type of data you want to store.

If you have a signed into that is always less than 2G and greater than 0 then you could put the checkbox (0 or 1) into the first bit of an unsigned int.

You didn't specify what the data you want to store is, so it is hard to give more detail.

Index on multiple bit fields in SQL Server

Whilst there are probably ways to solve your indexing problem against your existing table schema, I would reduce this to a normalisation problem:

e.g I would highly recommend creating a series of new tables:

1. Lookup table for the names of this bit flags. e.g. CREATE TABLE Flags (id int IDENTITY(1,1), Name varchar(256)) (you don't have to make id an identity-seed column if you want to manually control the id's - e.g. 2,4,8,16,32,64,128 as binary flags.)
2. Create a new link-table that contains the id's of the original data table and the new link table e.g. CREATE TABLE DataFlags_Link (id int IDENTITY(1,1), MyFlagId int, DataId int)

You could then create an index on the DataFlags_Link table and write queries like:

SELECT Data.*
FROM Data
INNER JOIN DataFlags_Link ON Data.id = DataFlags_Link.DataId
WHERE DataFlags_Link.MyFlagId IN (4,7,2,8)

As for performance, that's where good DBA maintenance comes in. You'll want to set the INDEX fill-factor and padding on your tables appropriately and run regular index defragmentation or rebuild your indexes on a schedule.

Performance and maintenance go hand-in-hand with databases. You can't have one without the other.

---

Related Topics