How Many Different Values Can be Stored in Bit Data Type?

SQL Server optimizes storage of BIT columns. If a table has 8 or fewer bit columns, SQL Server stores them as 1 byte. If a table has 9 up to 16 bit columns, SQL Server stores them as 2 bytes, and so on.

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.

SQL Server converts a string value TRUE to 1 and FALSE to 0. It also converts any nonzero value to 1.

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. 

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 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.

REF:

https://www.mssqltips.com/sqlservertip/6447/sql-server-bit-data-type/

https://codingsight.com/sql-server-bit-data-type-an-ultimate-guide/

Watch :
https://www.youtube.com/watch?v=6vPguzvECeo&list=PLWf6TEjiiuID99o2WFCoaX4tXuIwQwiuM&index=21