SQL Server Bit Data Type
By: Sergey Gigoyan | Updated: 2020-06-29 | Comments (1) | Related: More > Data Types
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.
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.
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 @[email protected] SELECT @isUsed AS BitType, @isUsedStr AS String --FALSE is converted to 0 SET @isUsedStr='FALSE' SET @[email protected] SELECT @isUsed AS BitType, @isUsedStr AS String --Assigning any other string value to a bit variable causes an error SET @isUsedStr='YES' SET @[email protected] 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:
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 Patient[email protected] ) 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:
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.
To find more information about the discussed topic, please follow the links below:
About the author
View all my tips
Article Last Updated: 2020-06-29