SQL Server bit data type is 1 bit numeric datatype. It is also used as Boolean data type in SQL Server. You can store only 0, 1 or NULL in a bit data type. When used as Boolean data type, 0 is treated as false and 1 as true.
Table of Contents
Storage optimization of Bit column
The bit data type needs only 1 bit of storage. But a byte contains 8 bits.
The SQL Server optimizes the storage of bit columns by merging other bit columns into a single byte. If there are 8 or fewer bit columns in a table, the SQL server combines them into 1 byte. If there are from 9 up to 16-bit columns, it combines them into 2 bytes.
Boolean data type
A boolean is a data type that can store either a True or False value. There is no separate Boolean data type in SQL Server. Hence the bit data types are used instead. The value 1 is true & 0 as false
Examples of Bit Column
Creating a Table with Bit or Boolean Column
The following Query shows how to create Table with BIT & Boolean Columns
1 2 3 4 5 6 7 | CREATE TABLE testBit ( col1 BIT, col2 BIT, col3 BIT, ); |
Inserting Values into a bit/Boolean column
1 2 3 4 5 6 7 8 9 10 11 12 | insert into testBit (col1, col2,col3) Values (1,0,null) select * from testBit ------- ------- ------- 1 0 NULL |
Converting into Bit
The converting string values TRUE and FALSE results in 1 for TRUE & 0 for FALSE.
Converting any other strings results in an error.
Converting to bit promotes any nonzero value to 1.
1 2 3 4 5 6 7 8 9 10 11 12 | insert into testBit (col1, col2,col3) values('TRUE','FALSE',100) select * from testBit ***Result ------- ------- ------- 1 0 1 |
Convert bit column to integer
Although bit data type is a number data type, you can add them. To do that, use the cast function to convert it to integer before adding them
1 2 3 4 | select cast(col1 as int) + cast(col1 as int) + cast(col2 as int) from testBit |
Reference
Read More
