Bit & Boolean Data Type In SQL Server - TekTutorialsHub

Home » Add Boolean Type Column In Sql Server » Bit & Boolean Data Type In SQL Server - TekTutorialsHub

Maybe your like

DateTime2 Vs DateTimeBinary & VarBinary

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
  • Boolean data type
  • Examples of Bit Column
    • Creating a Table with Bit or Boolean Column
    • Inserting Values into a bit/Boolean column
    • Converting into Bit
    • Convert bit column to integer
  • Reference

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, );
bit and boolean data type in SQL Server

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

DateTime2 Vs DateTimeBinary & VarBinary

Tag » Add Boolean Type Column In Sql Server