SQL Server BIT

Summary: in this tutorial, you will learn how to use the SQL Server BIT data type to store bit data in the database.

Overview of BIT data type

SQL Server BIT data type is an integer data type that can take a value of 0, 1, or NULL.

The following illustrates the syntax of the BIT data type:

BIT
Code language: SQL (Structured Query Language) (sql)

SQL Server optimizes storage of BIT columns. If a table has 8 or fewer bit columns, SQL Server stores them as 1 byte. If a table has 9 up to 16 bit columns, SQL Server stores them as 2 bytes, and so on.

SQL Server converts a string value TRUE to 1 and FALSE to 0. It also converts any nonzero value to 1.

SQL Server BIT examples

The following statement creates a new table with one BIT column:

CREATE TABLE test.sql_server_bit (
    bit_col BIT
);
Code language: SQL (Structured Query Language) (sql)

To insert a bit 1 into the bit column, you use the following statement:

INSERT INTO test.sql_server_bit (bit_col)
OUTPUT inserted.bit_col
VALUES(1);
Code language: SQL (Structured Query Language) (sql)

The output is:

bit_col
-------
1

(1 row affected)

To insert a bit 0 into the bit column, you use the following statement:

INSERT INTO test.sql_server_bit (bit_col)
OUTPUT inserted.bit_col
VALUES(0);
Code language: SQL (Structured Query Language) (sql)

Here is the output:

bit_col
-------
0

(1 row affected)

If you insert a string value of True into the bit column, SQL server converts it to bit 1:

INSERT INTO test.sql_server_bit (bit_col)
OUTPUT inserted.bit_col
VALUES
    ('True');
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

bit_col
-------
1

(1 row affected)

Similarly, SQL Server converts a string value of false to bit 0:

INSERT INTO test.sql_server_bit (bit_col)
OUTPUT inserted.bit_col
VALUES
    ('False');
Code language: SQL (Structured Query Language) (sql)

The following is the output:

bit_col
-------
0

(1 row affected)

SQL Server converts any nonzero value to bit 1. For example:

INSERT INTO test.sql_server_bit (bit_col)
OUTPUT inserted.bit_col
VALUES
    (0.5); 
Code language: SQL (Structured Query Language) (sql)

The following is the output:

bit_col
-------
1

(1 row affected)

In this tutorial, you have learned how to use the SQL Server BIT data type to store bit data in a table.