Need a SQL Server Boolean value? Use the BIT data type!

When creating a table or database object in SQL Server, there are definitely instances where you will need to store a Boolean value (aka true or false). But is there such a thing as a SQL Server Boolean?

In SQL Server, there is absolutely a data type we can use to represent a Boolean value. It would be convenient if that data type were simply called bool or boolean, but it isn’t. The data type we can use is called BIT.

In this very short tutorial, we’re going to learn all about the handy BIT data type we can use to store a SQL Server Boolean value.

The BIT data type is referenced in the following FREE guide:

FREE EBook on SQL Server Data Types!

This FREE Ebook gives you a simple summary of the most common data types, including the BIT data type, that you will use in your career as a database professional. It will definitely be a good resource for you to reference as you continue to query and developer SQL Server databases.  Make sure you download the guide today!

In this tutorial, we will cover the following topics:

1. What is the BIT data type?
2. An example of using the BIT data type as a Boolean value.
3. Using a BIT column in a CASE expression
4. Tips and tricks

Let’s get into it.

1. What is the BIT data type?

The BIT data type is an integer data type that only stores a value of either 1 or 0 (or NULL).

If you are familiar with programming, you’ll already understand how we can represent True or False values using the numbers 1 or 0 respectively. Since BIT can only store 1 or 0, it is the perfect data type to use if we want to store a true or false value (aka a Boolean value) in SQL Server.

2. An example of using the BIT data type as a Boolean value.

The BIT data type is very easy to understand. Let’s create a simple Products table with an InStock column to demonstrate the use of BIT.

Here is the Products table, with an InStock column that is a BIT data type:

CREATE TABLE Products
(
ProdID INT IDENTITY,
ProductName VARCHAR(20),
Price DECIMAL(5,2),
InStock BIT
)

As the name of the column suggests, we’ll use this column to tell us if the Product in question is in stock or if it is completely sold out.

Let’s add a new Product that is currently in stock:

INSERT INTO Products (ProductName, Price, InStock) 
values ('Large Bench', 198.00, 1)

(In our INSERT statement, we don’t need to specify a value for the ProdID column because it uses the handy IDENTITY property)

Now if we check the data, we can see the value for the InStock column is 1, of course:

Since we know 1 means true and 0 means false, we can say with confidence that the “Large Bench” product is, in fact, in stock.

Let’s add an out-of-stock item (InStock set to 0):

INSERT INTO Products (ProductName, Price, InStock) 
values ('Coffee Table', 225.00, 0)

Now if we check the data, we can say the “Coffee Table” is out of stock:

Folks, that’s basically all there is to it!

3. Using a BIT column in a CASE expression

One of the most common ways to make the BIT data type more readable in a query is to use it in a handy CASE Statement.

Using a CASE statement, we can basically return a more obvious value to the user than just 1 or 0. Our end users might not really know what 1 or 0 means.

They might think the number in this column refers to how many of the product is in stock. They would look at the data and think, “Wow, we’re extremely low on inventory. If we have a product at all, we only have ONE of it!”

So, let’s see if we can write a better query to make it more obvious that our column is basically a true/false column referring to the status of each product in our inventory.

Here’s a good query using the CASE statement:

SELECT ProdID, ProductName, Price,
CASE InStock
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS 'Item In Stock?'
FROM Products

This query uses a simple CASE statement. There is also a different way to write it using a searched CASE statement.

Take a look at the full tutorial to learn more:

SQL Server CASE Statement: A How-To Guide

If we run this query, we see a new column that very obviously tells us if an item is in stock or if it isn’t:

Of course, we can very easily change labels in this column. We can change the name of the column to ‘Item Status’ or ‘Stocked?’, for example. Or we could change the replacement values to ‘True‘ and ‘False‘, or ‘In stock‘ and ‘Out of stock‘, for example. Whatever you want!

The point is that the purpose of the new column is very obvious to the end user now: To tell us if an item is in stock or not!

4. Tips and tricks

Here is a list of tips and tricks you should know when working with the BIT data type:

Tip # 1: If you try to insert any number other than 0, the value 1 will be inserted instead

Let’s add another row to our Products table, specifying an InStock value of something other than 0:

INSERT INTO Products (ProductName, Price, InStock) 
values ('Cutting Board', 85.00, 9383)

Now let’s check the data:

When we insert something other than 0, 1 is inserted. And folks, this will happen for any number that is NOT ZERO, even if the number is negative or a decimal. Crazy!

Tip # 2: The only way to have a value of 0 is to insert a value of 0

So if literally anything other than 0 will ultimately insert 1 into the column, it shouldn’t be a surprise to learn that the only way a value of 0 can be put into the column is if a value of 0 is inserted!

The only way to get 0 is to put 0. Very trivial 🙂

Tip # 3: You can use the string values ‘True’ or ‘False’ and the values 1 or 0 will be inserted, respectively

SQL Server is very smart. If you want, you can outline the literal words ‘True‘ or ‘False‘ to insert a value of 1 or 0 respectively into your BIT column. Let’s try it:

INSERT INTO Products (ProductName, Price, InStock) 
VALUES
('Spice Rack', 45.00, 'TRUE'),
('Bar Stool', 60.00, 'FALSE')

Here’s the data:

My mind is BLOWN

Next Steps:

Leave a comment if you found this tutorial helpful!

Don’t forget to download your FREE guide:

FREE EBook on SQL Server Data Types!

This FREE Ebook discusses the most common data types you will encounter in your career as a database professional. Make sure you get the guide to learn them all!

Also, the BIT data type is one of several integer data types available to us in SQL Server. Take a look at the following tutorial to learn all about the other integer data types:

SQL Integer Data Types: Everything you need to know

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

Thank you for reading! If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!