Bit & Boolean Data type in SQL Server

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.

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

CREATE TABLE testBit (
    col1 BIT,
    col2 BIT,
    col3 BIT,
);

Inserting Values into a bit/Boolean column

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.

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

select cast(col1 as int) + cast(col1 as int) + cast(col2 as int) 
from testBit

Reference

bit (Transact-SQL)

Read More