Reading time: 6 minutes
When creating a database table or object in SQL Server, there are definitely cases where you will need to store a Boolean value (also known as true or false). But is there such a thing as a SQL Server Boolean?
In SQL Server, there is absolutely one data type that we can use to represent a Boolean value. It would be convenient if that type of data was simply called bool or boolean, but it is not. The type of data we can use is called BIT.
In this short tutorial, we will learn all about the handy BIT data type that we can use to store a SQL Server Boolean value.
The BIT data type
in the following FREE GUIDE:
FREE 1-page simple SQL cheat sheet on top 10
data types you need to know.
This guide 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 developer. It will definitely be a good resource for you to reference as you continue to query and develop SQL Server databases. Be sure to download the guide today!
In this tutorial, we will cover the following topics:
- What is
- Example of using the BIT data type as a Boolean value
- BIT column in a CASE expression
- Tips, tricks, and links.
the BIT data type?
. Using a
Let’s get into it.
1. What is the BIT data type?
The BIT data type
is an integer data type that stores only a value of
1 or 0 (or NULL).
If you are familiar with programming, you will 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 (also known as 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.
This 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 column
name suggests, we will use this column to tell us if the Product in question is in stock or if it is completely out of stock. 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 do not 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 that the value of the InStock column is 1, Of course:
Since we know that 1 means true and 0 means false, we can confidently say that the “Large Bench” product is, in fact, in stock.
Let’s add an out-of-stock item (InStock set to 0):
INSERT INTO Product Values (ProductName, Price, InStock) (‘Coffee Table’, 225.00, 0)
Now, if we check the data, we can say that the “Coffee Table” is sold out:
Friends, that’s basically all there is to it!
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
Using a CASE statement, we can basically return a more obvious value to the user than just 1 or 0. Our end users may not really know what 1 or 0 means.
You might think that the number in this column refers to how many of the products are in stock. They looked at the data and thought, “Wow, we have extremely low inventory. If we have a product, we only have ONE of them!”
So let’s see if we can write a better query to make it a little more obvious that our column is basically a true/false column that refers 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 obviously tells us if an item is in stock or not:
Of course, we can easily change the labels in this column. We can change the name of the column to ‘Item status‘ or ‘Stored?’, 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, tricks and links.
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 friends, this will happen for any number that is NOT ZERO, even if the number is negative or decimal. Crazy!
The only way to have a value of 0 is to insert a value of 0 So if
literally anything other than 0 will eventually insert 1 into the column, it shouldn’t come as a surprise to learn that the only way you can put a value of 0 in the column is if you insert a value of 0. 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 desired, you can sketch the literal words ‘True‘ or ‘False‘ to insert a value of 1 or 0 respectively in 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 are the data:
My mind is BLOWN
Here is the official documentation about SQL Server BIT data type: BIT (Transact-SQL)
Be sure to check it out.
Don’t forget to download your FREE guide:
FREE 1-page simple SQL cheat sheet on top 10 data types you need to know!
This guide discusses the most common types of data you’ll encounter in your career as a database developer or administrator. Make sure you get the guide to learn them all!
Additionally, the BIT data type is one of several integer data types available in SQL Server. Take a look at the following tutorial to learn about the other
integer data types: SQL Integer Data Types: Everything You Need to Know
Thank you so much
Be sure to subscribe to my newsletter to receive special offers and notifications whenever a new tutorial is released
Thanks for reading! If you have any questions, or if you’re struggling with a different topic related to SQL Server, I’ll be happy to discuss it. Leave a comment or visit my contact page and send me an email!