SQL Server BIT Data Type

The BIT data type is used to store boolean values like 0, 1, or NULL. The SQL server doesn’t have the data Boolean instead it has the data type BIT which has which stores the boolean value. The BIT data type is advantageous in terms of space optimization since each BIT data type takes on only 1 bit of storage, suppose their n columns in the table with the BIT data the ceil value of n/8 bytes will be used i.e if there 0-8 columns with bit data type then it will be stored as 1 byte.

SQL server even converts the following assignment into 0 or 1 values:

• When the column of bit data type assigned with ‘TRUE‘ or ‘FALSE‘ is converted to 1 or 0 respectively, other than there strings it will throw an error for other strings.
• When the column of bit data type assigned with any nonzero values will be converted to 1 respectively.

Syntax:

CREATE TABLE  table_name
(
column_name BIT
);

Explanation: Using the BIT data type after our desired column name make the column of BIT data type.

How to Use the BIT

Step 1: Create database Geeksforgeeks by using the following SQL query

CREATE DATABASE Geeksforgeeks

Step 2: Use the GFG Database.

USE Geeksforgeeks

Step 3: Create a Table with the Name studentsFeesInfo which Contains the studentIds, Names and their fee status.

CREATE TABLE studentsFeesInfo(
studentId INT PRIMARY KEY,
studentName VARCHAR(100),
isFeePaid BIT
);

Explanation: The table with a column name isFeePaid with BIT data type is created.

Step 4: Insert the Sample Data into the Table.

Inserting Some data into StudentFeesInfo Table

Explanation: Here we can see that for all the students the isFeePaid column of BIT data type is either 0 or 1.

Step 5: Check the table

SELECT *  FROM  studentsFeesInfo

studentsFeesInfo Table

How to Set the Student with Id of 19104069 as ‘TRUE’.

Suppose we have to update the fee status from 0 to 1 of a particular student whose studentId is 19104069 from studentsFeesInfo table.

Query

UPDATE studentsFeesInfo
SET isFeePaid = 'TRUE'
WHERE studentId = 19104069
SELECT * FROM studentsFeesInfo

The Result Looks Like:

After Update studentID

Explanation : In the above query we have updated the record of student with studentId 19104069 with isFeePaid =1 by updating its value to TRUE since SQL server has converted it to BIT datatype it has become 1 in the table.

How to Assign the Nonzero Values to the BIT Data Type Converts it to 1.

Let’s UPDATE the isFeePaid values for two students in the studentsFeesInfo table. Also, Set the isFeePaid to 60 for the student with ID 19104061 and set it to 0 for the student with ID 19104060

Query

UPDATE studentsFeesInfo
SET isFeePaid = 60 
WHERE studentId = 19104061
UPDATE studentsFeesInfo
SET isFeePaid = 0
WHERE studentId = 19104060

The result Looks Like :

Updating non zero and zero values to isFeePaid column

Explanation : In the above query we have updated the isFeedPaid = 60 for studentId = 19104061 and isFeePaid = 0 for studentId = 19104060 ,assigning non zero values also gets converted to 1 as SQL server converts them to BIT data type so Student1 got the value isFeePaid to 0 as 0 is assigned and the Student2 got updated with the non zero values so 1 is assigned.

Conclusion

The BIT datatype is used to represent boolean values in the database. It is very versatile datatype and used for boolean flags, boolean conditions. This BIT data type can be used only if the column has only values of 0 , 1 and NULL it becomes advantageous in such cases rather than using the VARCHAR or INT data type which can occupy extra space depending on the usecase.