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

bit and boolean data type in SQL Server

Inserting Values into a bit/Boolean column

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.

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

Reference

bit (Transact-SQL)

Read More

Leave a Comment

Your email address will not be published.

Scroll to Top