Store multiple bits in one table column.
I need to store some kind of day scheduling in a database where I can schedule an entry (like her task) for a day or several days of the week. I need it to be stored in a single column so that it can be easily saved and retrieved. Then every day I select records where the schedule matches the current day of the week, for example: get me all records with a schedule that matches the day of week 7.
What I thought was a series of 7 bits, each representing a day of the week, where 0 means not a schedule, but 1 means scheduled. For example: "1111111" for a daily task and "1,000,000" for a task that only runs on the first day of the week.
To be clear, the recording can be scheduled on any combination of days that I am not aware of, so the only entry to search would be the business day to search.
I have two questions:
-
What's the best way to store this type of data in SQL Server 2008? This makes it easy to query based on the current day of the week.
-
What are the best alternatives to the above approach. Please, I need an efficient and simple solution.
source to share
You can store this as a bitfield and then use boolean logical operators to get the values
eg:
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 containing tuesday flags (tuesday is 2nd bit or 2 ^ 1 or 2)
SELECT *
FROM aTable
WHERE (bitfield & 2) = 2
or
SELECT *
FROM aTable
WHERE (bitfield & 2) != 0
Note: the pattern in the second case will work for any bit - that is, on Friday (5th bit or 2 ^ 4 or 16) will be
SELECT *
FROM aTable
WHERE (bitfield & 16) != 0
Finally, the general case ... pass the number (1 on Monday), you get
SELECT *
FROM aTable
WHERE (bitfield & POWER(2,@inNumOfWeekday-1)) != 0
It seems like a lot of work to me where you can just store it as 5 (or 7-bit fields), but this is how you can do it.
For more examples, look at the gist I wrote for another question:
https://gist.github.com/1846338
and the answer is:
source to share
You are talking about a "bit mask". They are handy devices as you can use binary math to test them, but they are a bit customizable. To expand on this, a little more it will look something like this.
-- You will want to work with constants
DECLARE @Mon INT, @Tue INT, @Wed INT, @Thu INT, @Fri INT, @Sat INT, @Sun INT
SET @Mon = POWER(2,0) -- 1
SET @Tue = POWER(2,1) -- 2
SET @Wed = POWER(2,2) -- 4
SET @Thu = POWER(2,3) -- 8
SET @Fri = POWER(2,4) -- 16
SET @Sat = POWER(2,5) -- 32
SET @Sun = POWER(2,6) -- 64
-- Set Monday and Wednesday
UPDATE T SET Schedule = @Mon | @Wed
-- Find all tasks scheduled on Tuesday
SELECT * FROM T WHERE Schedule & @Tue = @Tue
-- Find all tasks scheduled on Tuesday and Saturday
SELECT * FROM T WHERE Schedule & @Tue | @Sat = @Tue | @Sat
source to share
I'm not sure what you mean by a simple query. It's easy once you wrap it in a function. An option for a simple ad hoc query might be to have easy-to-read string fields and use some string processing. Example:
UPDATE T SET Schedule = 'M|W|H'
SELECT * FROM T WHERE CHARINDEX(Schedule, 'M') > -1
source to share
From the title, I gave a situation with the presence of bit fields in the table and the need to convert to one integer field.
Following the example of the days of the week, let's say there are 7 separate bit fields that you want to convert to an integer field (bit mask) named Schedule:
UPDATE T
SET Schedule = Mon * POWER(2,0) +
Tue * POWER(2,1) +
Wed * POWER(2,2) +
Thu * POWER(2,3) +
Fri * POWER(2,4) +
Sat * POWER(2,5) +
Sun * POWER(2,6)
source to share