SQL Server Bit Data Type

By: Sergey Gigoyan | Comments (1) | Related: > Data Types

Problem

In database development, there are some special cases when it is necessary to work with the Boolean data type. This is a data type that allows only two possible values "True" or "False". Definitely, if a variable or column can only have two possible values, it will be easier and logical to set its type as Boolean. Thus, does SQL Server have Boolean or a similar data type? In SQL Server, there is no data type called Boolean. However, there is a data type called bit that can be used to store Boolean values. In this article, we will introduce and explore SQL Server bit data type.

Solution

SQL Server bit data type is an integer data type that can take only one of these values: 0, 1, NULL. With regard to the storage, if there are less than 9 columns of the bit data in the table, they are stored as 1 byte. If there are 9 to 16 such columns, they consume 2 bytes and so on. Thus, SQL Server optimizes the storage of columns of the bit data type. Additionally, string values TRUE and FALSE can be converted to 1 and 0 corresponding to bit values.

Features

Now, let's explore the features and specifications of the bit data type. First, as it is mentioned above, 'TRUE' and 'FALSE' strings are converted to 1 and 0. This behavior is illustrated in the following example:

DECLARE @isUsed BIT
DECLARE @isUsedStr NCHAR(5)
 
SELECT @isUsed AS BitType, @isUsedStr AS String
 
--TRUE is converted to 1
SET @isUsedStr='TRUE'
 
SET @isUsed=@isUsedStr
 
SELECT @isUsed AS BitType, @isUsedStr AS String
 
--FALSE is converted to 0
SET @isUsedStr='FALSE'
 
SET @isUsed=@isUsedStr
 
SELECT @isUsed AS BitType, @isUsedStr AS String
 
--Assigning any other string value to a bit variable causes an error
SET @isUsedStr='YES'
 
SET @isUsed=@isUsedStr
 
SELECT @isUsed AS BitType, @isUsedStr AS String

In the code above, we declared to variables – one of the bit and other of the string data type. Then, we assign the string to a variable of the bit data type. As we can see, the values 'TRUE' and 'FALSE' are successfully converted to the corresponding values (1 and 0) of the bit data type:

Assigning any other string to a bit variable, however, causes an error. In our example, we assigned 'YES' to a bit variable and received an error:

SQL Server bit data type conversion error

Secondly, it is important to mention that assigning any non zero value to a bit data type converts it to 1. In the next example, we assign positive and negative integers to a variable of the bit datatype:

DECLARE @isUsed BIT
 
SELECT @isUsed AS BitType
 
--Assigning any nonzero value converts it to 1
SET @isUsed=9
 
SELECT @isUsed AS BitType
 
--Assigning any nonzero value converts it to 1
SET @isUsed=-100
 
SELECT @isUsed AS BitType

The result shows that in both cases, the value of the bit variable is 1:

SQL Server Bit Data Type Advantages

As it is mentioned above, if we have less than 9 columns of the bit data type in our table, they are stored as one byte. Therefore, the question might arise why use the bit instead of char(1) or tinynint if we have only one column in the table accepting only Boolean values? Well, let's create a test environment and see the following example:

USE master
GO
 
CREATE DATABASE TestDB
GO
 
USE TestDB
GO
 
CREATE TABLE [dbo].[PatientTestResults]
(
   [PatientID] [int] NOT NULL PRIMARY KEY,
   [Gender] [char](1) NULL,
   [Test1Result] [tinyint] NULL,
 
)
GO

We have created the TestDB database and sample table storing patients' medical test results. It is assumed, that gender can be only 'Male', 'Female', 'Unknown', 'Undefined' or 'other'. For the first two cases, we will use 'M' and 'F' correspondingly and for the last three cases, we will use 'NULL'. To make our example easier, it is also assumed that test results can be either positive or negative (NULL will be considered as unknown). The data types (char(1) and tinyint) chosen in the table definition, however, are not enough to implement this logic. We need to ensure that the mentioned columns cannot accept other values. Therefore, we need to create check constraints:

USE [TestDB]
GO 
 
--Adding check constraints
ALTER TABLE [dbo].[PatientTestResults]  WITH CHECK ADD  CONSTRAINT [CK_PatientTestResults_Gender] CHECK  (([Gender]='M' OR [Gender]='F'))
GO
 
ALTER TABLE [dbo].[PatientTestResults] CHECK CONSTRAINT [CK_PatientTestResults_Gender]
GO
 
ALTER TABLE [dbo].[PatientTestResults]  WITH CHECK ADD  CONSTRAINT [CK_PatientTestResults_Test1Results] CHECK  (([Test1Result]=(1) OR [Test1Result]=(0)))
GO

In contrast, if we use the bit datatype for the Gender and Test1Result columns, we do not need to create check constraints:

USE TestDB
GO
 
DROP TABLE [dbo].[PatientTestResults]
GO
 
CREATE TABLE [dbo].[PatientTestResults]
(
   [PatientID] [int] NOT NULL PRIMARY KEY,
   [Gender] [bit] NULL,
   [Test1Result] [bit] NULL
)
GO

For the Gender column, we can consider 1 as a male, for example, 0 as a female and NULL as unknown, undefined or other. With regard to the Test1Result, we can consider 1 as a positive and 0 as a negative.

In terms of storage optimization, the main advantage is that if we have several Boolean columns, using bit type we can significantly reduce the space used. As a simple example, we can use a table that stores the patients' several medical test results (which are either positive or negative):

USE TestDB
GO
 
DROP TABLE [dbo].[PatientTestResults]
GO
 
CREATE TABLE [dbo].[PatientTestResults]
(
   [PatientID] [int] NOT NULL PRIMARY KEY,
   [Gender] [bit] NULL,
   [Test1Result] [bit] NULL,
   [Test2Result] [bit] NULL,
   [Test3Result] [bit] NULL,
   [Test4Result] [bit] NULL,
   [Test5Result] [bit] NULL,
   [Test6Result] [bit] NULL,
   [Test7esult] [bit] NULL
)
GO

Here we have 8 columns of the bit data type and, therefore, 1 byte is used to store the values. If we used the tinyint or char(1) instead, 1 byte would be used for each of them.

We can also derive benefits from using the bit data type in functions or stored procedures that accept or return Boolean values. Let's assume we need a function that will check whether a patient is tested or not. Obviously, the function will return TRUE or FALSE. Thus, we can use the bit data type as a return value type:

USE TestDB
GO
 
CREATE FUNCTION isPatientTested
(
   @PatientID INT
)
RETURNS bit
AS
BEGIN
   
   IF EXISTS (SELECT PatientID FROM PatientTestResults WHERE PatientID=@PatientID )
      RETURN 1
 
   RETURN 0
 
END
GO
Now, we can easily call this boolean function:
USE TestDB
GO
 
IF (dbo.isPatientTested(1)=1)
   PRINT 'The patient is tested'
ELSE 
   PRINT 'The patient is not tested'

We can see that the patient is not tested as there isn't a record for the patient with PatientID=1:

SQL Server bit data type testing of patient data

Conclusion

In conclusion, the bit data type can be quite useful if columns or variables accept only two values (plus NULL). In these cases, using the bit type instead of the string or tinyint data types can make the code more logical and compact. Additionally, as SQL Server optimizes the storage of bit columns, the usage of this type can economically save storage, particularly if we have more than 8 columns of bit data type in the table.

Next Steps

To find more information about the discussed topic, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Friday, January 7, 2022 - 4:57:55 PM - JOHN BEVERAGE	Back To Top (89636)
Has anyone experienced any issues while synching up some older 2008 tables into SQL 2019 if they have data types = bit, varbinary, binary, text, ntext or image or other comparable data types while using the Red Gate Toolset for SQL Data Compare or SQL Object Compare. Seems to me that we had this type of issue last year but now I cannot duplicate that problem.