SQL Server BIT Data Type – An Ultimate Guide

Total: 1 Average: 5

Introduction to Bit Data type

The data type represents an attribute that stores integer, character, data time, binary data. Suppose you have a web application for recording the users’ feedback for various questions. It contains multiple dichotomous questions and a few textbox-based questions, such as remarks. On the backend, these responses get stored in an SQL table.

CodingSight - SQL Server BIT Data Type - An Ultimate Guide

To design the table, we define columns and their data types. SQL Server supports various data types such as integer, char, varchar(), float, DateTime, numeric, etc. To learn more about the data types, refer to the official documentation. The use cases depend on the values we wish to store in them.

Suppose we have specific columns in that table we’ve created. Those columns accept Boolean values 1,0 or TRUE\False. SQL. Therefore, SQL Server provides a particular Boolean data type – Bit.

The SQL Server Bit is an integer data type that accepts 0,1 and NULL values. It is available in all SQL Server versions, including Azure SQL Server, Azure Managed Instances and SQL on Azure VM.

Bit data type storage

In terms of storage, the Bit data type is optimized by the SQL Server. If you have eight or fewer Bit columns in the table, SQL Server stores them as 1 byte. Similarly, for 9 to 16-bit columns, it consumes 2 bytes. Additionally, SQL Server converts String values TRUE and FALSE into their corresponding values 1 and 0.

Syntax

The syntax of the BIT data type is straightforward:

 Bit

Practical use of Bit data type

The following T-SQL script creates a product table and inserts two products data values into it:

  • If the product is available, the column [Available] bit is set to 1.
  • If the product is not available, the column [Available] bit is set to 0.
CREATE TABLE Products (
    [ProductName] varchar(20),
    [Available] BIT
);
Go
INSERT INTO Products (productname,available) values('A',1)
INSERT INTO Products (productname,available) values('B',0)
GO
SELECT * FROM Products
The output of the T-SQL script that creates a product table and inserts two products data values into it

Let’s truncate the [Products] table and insert its values using the TRUE and False strings.

TRUNCATE TABLE Products
INSERT INTO Products (productname,available) values('A','TRUE')
INSERT INTO Products (productname,available) values('B','False')
GO
SELECT * FROM Products

As shown below, SQL Server converts the string TRUE to 1 and False to 0.

SQL Server converts the string TRUE to 1 and False to 0

However, if you try to insert any other values, such as Yes or No, into the Bit data type, you get the “Conversion failed” error message.

INSERT INTO Products (productname,available) values('A','Yes')
If you try to insert any other values, such as Yes or No, into the Bit data type, you get the "Conversion failed" error message

When you insert any nonzero value into the Bit data type column, SQL Server converts that value to one. For example, in the following script, we insert value 100 into the [available] column. No error messages happen while that.

If you select records, you validate that the inserted value is 1.

INSERT INTO Products (productname,available) values('A',100)
SELECT * FROM Products;
If you select records, you validate that the inserted value is 1

Similarly, SQL Server converts the negative value to value 1 in the Bit column. As shown below, we insert value -100 into the [available] column. We receive value one while retrieving it:

INSERT INTO Products (productname,available) values('A',-100)
SELECT * FROM Products;
SQL Server converts the negative value to value 1 in the Bit column. As shown below, we insert value -100 into the [available] column. We receive value one while retrieving it

Previously, we used the Bit data type for checking the product availability. We usually need to display text at the front-end instead of bits 1 and 0. Therefore, we can use the CASE statement in SQL Server.

In the following T-SQL code, the CASE statement returns:

  • Value 1: Product is available
  • Value 0: Out of Stock
SELECT [ProductName], CASE [Available]
         WHEN 1 then  'Product is available.'
        WHEN 0 then 'Out of Stock'
        ELSE 'NA'
    END AS [Availability]
from products
The T-SQL code the CASE statement returns - Value 1: Product is available, Value 0: Out of Stock

As we could see earlier, SQL Server optimizes the storage for the Bit data type. The following [TestTable] has eight columns with the Bit data type; therefore, it uses 1 byte for storage.

CREATE TABLE TestTable (
    [Column1] Bit,
    [Column2] Bit,
    [Column3] Bit,
    [Column4] Bit,
    [Column5] Bit,
    [Column6] Bit,
    [Column7] Bit,
    [Column8] Bit,
);

Alternatively, if you use tinyint or Char(1) data type, it would consume 1 byte for each column. You should use the Bit data type, whereas you require Boolean value.

You can also use the Bit data type in stored procedures or functions that return Boolean values. For example, we have the fn_customer() function that checks the customer id, and if it exists, it returns value one else it returns 0.

CREATE FUNCTION fn_customer
(
   @CustomerID INT
)
RETURNS bit
AS
BEGIN
IF EXISTS (
SELECT [CustomerID] FROM [SalesLT].[Customer] 
WHERE [CustomerID]=@CustomerID 
)
      RETURN 1
    RETURN 0
 END

This function requires customer ID as an input parameter. If we execute it with customer ID 10, you get the following message:

IF (dbo.fn_customer(10)=1)
   PRINT 'Customer record is available'
ELSE 
   PRINT 'Customer record is not available'
This function requires customer ID as an input parameter. If we execute it with customer ID 10, you get the following message

However, if the customer ID does not exist in the [SalesLT].[Customer] table, the function returns value 0. The IF block condition is not true. Therefore, you get the message specified in the ELSE clause:

However, if the customer ID does not exist in the [SalesLT].[Customer] table, the function returns value 0. The IF block condition is not true. Therefore, you get the message specified in the ELSE clause

Conclusion

The Bit data type is useful for columns accepting values 0,1 or NULL. SQL Server optimizes the Bit data type storage; therefore, it makes the code compact and efficient. Similarly, you can use it to return Boolean values from a stored procedure or function.

Read also

Handling the NULL Values Effectively with the SQL COALESCE Function for Beginners

Latest posts by Rajendra Gupta (see all)

Rajendra Gupta

Rajendra Gupta is a Database Administrator with over 13 years of experience working with SQL Server, Azure, AWS, Power BI and related technologies. He loves writing and an author of 500+ technical articles. He is also a writer of the book " DP-300 Administering Relational Databases on Microsoft Azure."