Introduction to Bit Datatype in SQL
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 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 Datatype Storage
In terms of storage, the Bit datatype 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.
The syntax of the BIT data type is straightforward:
How to Insert Value in BIT Column in SQL Server?
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 datatype 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:
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.