Inserting Multiple Values in a single Binary Field of SQL Server

Question

I've come to an issue where i need to insert bit values(true/false) in my database for each hour for student attendance. For each hour there should be one value(true/false).

The straight forward option is create 24 columns and input value for each of them, every time a student is present. But definately this is also the worst one.

Someone suggest me to use binary field for it, one field could contain all these values.

Actually mu gui has 24 checkboxes and whichever checkbox is checked, its value should be stored as 1 so when next time i open the screem those checkboxes should remain checked.

I am using sql server 2008 and .net framework 4.0, if it helps.

Thanks

I've not try anything as i don't have any idea, how this binary datatype works, kindly guide me in the rght direction. — manav inder
– manav inder, Commented Oct 31, 2011 at 5:30
then checkout these msdn.microsoft.com/en-us/library/ms188362.aspx and blogs.msdn.com/b/sqlprogrammability/archive/2008/10/31/… and msdn.microsoft.com/en-us/library/ms176122.aspx and then come back with specific questions... — Yahia
– Yahia, Commented Oct 31, 2011 at 5:34

Marc Gravell · Accepted Answer · 2011-10-31 06:36:24Z

1

There is no need to mess with [var]binary(n). Since you only need 24 bits, store a regular, non-nullable int. Then just do bit-math on your code, i.e. shift (<< / >>) operators and bitwise combination (&, | and maybe some ~). Nothing else is needed. So if we decide that the LSB is 00:00, working right-to-left, then someone present at 03:00, and 14:00-16:00 (inclusive) would have a value of

(1 << 3) | (1 << 14) | (1 << 15) | (1 << 16) ==> 114696

If you aren't comfortable with bit-math, then either:

get comfortable with bit-math
don't try storing it as binary

You also state:

But definately this is also the worst one.

In what way? It describes your scenario, and SQL-server will condense multiple bit fields for you automatically. That might actually not be a bad option.

answered Oct 31, 2011 at 6:36

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Booth · Accepted Answer · 2011-10-31 06:45:56Z

0

You can use the BitArray class for manipulating the individual bits of an int (and other types). This may make it easier for you to go with the binary option, however I don't think its necessarily a bad option to store all the values in the database.. Why do you think this is such a bad option?

answered Oct 31, 2011 at 6:45

Martin Booth

8,59533 silver badges31 bronze badges

Collectives™ on Stack Overflow

Inserting Multiple Values in a single Binary Field of SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related