SQL Server BIT Datatype – Ultimate Guide

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.

To design the table, we define columns and database field types. SQL Server supports various data types such as integer, char, varchar(), float, DateTime, numeric, etc. The same can be applied to T-SQL data types. 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, in SQL Server, a particular Boolean data type – Bit is provided.

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 in SQL Server 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

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.

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')

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;

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;

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

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'

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 MSSQL Boolean data type-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

Tags: bit data type, sql database, sql server Last modified: September 16, 2022