bit (Transact-SQL)
An integer data type that can take a value of 1, 0, or NULL.
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
Storing bits in a byte, and the 'bit' type
The reason that T-SQL has a type called a 'bit', is because it conceptually maps directly to the bits used in computer processors. Computer bits are binary, they are either 1 or 0, (true/false, open/closed, on/off), so the bit is Boolean. Other SQL servers have a boolean type, which is also stored as a bit.
There are 8 bits in a byte, so you can think of it as an array like {0,1,1,0,1,1,0,0}. Computers process data in bytes. Rather than using up a whole byte to store a binary value of 0 or 1 (which wastes 7 bits), it is smarter to use a single byte and store 8 binary values in it.
This technique of using a single byte to store boolean values as bits is used by programmers as an optimization.
-------------------------------
This page says that
(1) bit fields can be 1, 0 or NULL (three values) and
(2) eight such fields will be stored in one byte.
That's got to be wrong. Or am I missing something?
(1) bit fields can be 1, 0 or NULL (three values) and
(2) eight such fields will be stored in one byte.
That's got to be wrong. Or am I missing something?
- 5/5/2010
- KevinBaynes
storage for 8 columns of type bit??
I'm guessing they are using two bitmaps. First one says if value is NULL or not and second one says, if value is not NULL, is it 0 or 1.
- 1/19/2010
- Marko Parkkola
storage for 8 columns of type bit??
This page says that
(1) bit fields can be 1, 0 or NULL (three values) and
(2) eight such fields will be stored in one byte.
That's got to be wrong. Or am I missing something?
(1) bit fields can be 1, 0 or NULL (three values) and
(2) eight such fields will be stored in one byte.
That's got to be wrong. Or am I missing something?
- 11/21/2009
- Pleasant Holiday
is / is not
Often C/C# programmers (including me) fall into the trap and believe that IS NOT operator can be used to query if a bit column is true (1) or false (0):
is MyTable.ABitColumn
is not MyTable.ABitColumn
MyTable.ABitColumn is true
MyTable.ABitColumn is false
but we have to to that following:
MyTable.ABitColumn = 1 -- is true
MyTable.ABitColumn = 0 -- is false
So, is not operator can only be used to ask if a column is null or not and there exists no true / false constant in SQL?
- 11/10/2009
- hfrmobile
- 11/15/2009
- Thomas Lee
