The SQL Boolean data type is not included in SQL Server. Other databases like Oracle and MySQL include the Boolean data type that accepts the values of TRUE, and FALSE.
SQL Server uses the bit data type that stores 0, 1, and NULL values that can be used instead of the TRUE, FALSE, and NULL values. In this tutorial, we will teach several examples to select and insert values.
What is the bit data type?
It is a data type to store 3 values. 1, 0, and NULL values. This data type is more efficient than the SQL Boolean data type used by other DBMSs because it only uses 1 bit to store the data. Let’s take a look at an example of the usage.
SQL Boolean examples in SQL Server
The following example will create a variable named myBoolean with the bit data type. We will then set the variable to true and finally show the results.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean='true' select @myBoolean result |
Note that when we select the value it shows the value 1 which means that is true.
In the same way, you can set the Boolean variable to false.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean='false' select @myBoolean result |
Alternatively, and as a best practice, you can set numbers to the variable. The following example will set the myBoolean variable to 1 instead of a true value. This is a best practice.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean=1 select @myBoolean result |
For a false value, you will set the value to 0.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean=0 select @myBoolean result |
To resume, you can set the bit values with 0, 1 or use ‘TRUE’ and ‘FALSE instead’.
Finally, you can set a bit variable with NULL values if necessary. The following example will set the myBoolean bit variable to null.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean=NULL select @myBoolean result |
How to replace 0, 1 values with true or false values
As you saw before, the bit shows 0 and 1 instead of TRUE and FALSE. How can I replace the 0 and 1 values with TRUE and FALSE?
The following example illustrates how to do that. We will use the case statement to replace the values. The following example shows how to do it.
|
1 2 3 4 5 6 7 8 9 10 11 |
declare @myBoolean bit set @myBoolean='TRUE' select case WHEN @myBoolean=1 then 'TRUE' WHEN @myBoolean=0 then 'FALSE' ELSE NULL end as result |
The code does the following. If the value is 1, then it will return TRUE. If the value of the variable is 0, it will return false. Otherwise, it will return a Null value.
How to create a table with a bit data type column
The following example will create a table named myBooleanTable with 2 columns. An ID column with the integer data type and the ispair column with a bit data type.
|
1 2 3 4 5 6 7 |
create table myBooleanTable ( id int, ispair bit ) |
How to insert data with SQL Boolean data type
To insert data with a bit data type, you can only use the 0,1 and NULL values. For this example, we are inserting data in the myBooleanTable previously created.
|
1 2 3 4 5 6 |
insert into myBooleanTable values (1,0), (2,1), (5,NULL) |
Working with SQL queries
The following example will use the famous Adventureworks database. If you do not have the Adventureworks database installed, please follow our article related.
For this example, we will use the HumanResource.Employee table which contains some bit data type columns. For this example, we use the SalariedFlag bit column.
The following example will show the BusinessEntityID, NationalIDNumber, and SalariedFlag columns where the SalariedFlag is 1.
|
1 2 3 4 5 6 7 |
SELECT [BusinessEntityID] ,[NationalIDNumber] ,[SalariedFlag] FROM [HumanResources].[Employee] WHERE SalariedFlag =1 |
The salary displayed is the following.
To get the same information for employees with a Salariedflag equal to false, you can change the salariedFlag value to false (0).
|
1 2 3 4 5 6 7 |
SELECT [BusinessEntityID] ,[NationalIDNumber] ,[SalariedFlag] FROM [HumanResources].[Employee] WHERE SalariedFlag =0 |
Alternatively, you can use the TRUE and FALSE values instead of the 0 and 1 values, but this practice is not recommended.
|
1 2 3 4 5 6 7 |
SELECT [BusinessEntityID] ,[NationalIDNumber] ,[SalariedFlag] FROM [HumanResources].[Employee] WHERE SalariedFlag ='true' |
Finally, we have the NULL value in the where clause. The Null value is slightly different than the 0, and 1 values because you have to use the IS operator instead of the equal.
The following example illustrates that.
|
1 2 3 4 5 6 7 |
SELECT [BusinessEntityID] ,[NationalIDNumber] ,[SalariedFlag] FROM [HumanResources].[Employee] WHERE SalariedFlag IS NULL |
CONVERT SQL boolean to other data types
The following example shows what happens if we want to add a bit data type to a numeric value. We will declare the variable myBoolean and set it to true and then add 2.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean='true' select @myBoolean+2 result |
So, in the example, @myBoolean is 1, and 1+2 is equal to 3 which means that bit it is implicitly treated as a numeric value.
If we try to concatenate with the + operator, we will have an error. The following example illustrates this problem.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean='true' select 'The value is'+@myBoolean |
The error message is as follows:
The data types varchar and bit are incompatible in the add operator.
The best solution for this problem is to use the CONCAT operator which will convert the bit variable to a string.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean=1 select CONCAT ('The value is ',@myBoolean) as result |
CONCAT will concatenate the string with the bit variable and show the following result.
Alternatively, but not recommended, we can use the CONVERT function to convert the bit variable into a string. For this scenario, the CONCAT is the best solution. However, for other scenarios, you may not be able to use CAST and may need to use CONVERT instead.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean='true' select 'The value is'+CONVERT(varchar(1),@myBoolean) as result |
The convert value is converting the @myBoolean bit data type into varchar(1) which can be concatenated with the string.
The other option is to use CAST. CAST is similar to CONVERT and we are only including this option because you can find code from someone else and it is better to be familiar with this function as well.
|
1 2 3 4 5 |
declare @myBoolean bit set @myBoolean='true' select 'The value is'+CAST(@myBoolean as varchar(1)) as result |
The syntaxis for CAST is slightly different than cast. You have to specify the expression and then the data type.
Stored procedures with SQL Boolean variables
Stored procedures can also be used in SQL Server stored procedures. The stored procedures are code stored in the database that you can call and reuse multiple times.
In this example, we will show how to create a stored procedure with bit variables and execute it.
We will first create a stored procedure named salariedFlag that shows the login ID of users with the salariedFlag equal to true (1) or false (0). According to the variable @salariedFlag, it shows the values specified in the where clause. The values will be retrieved from the Employee table.
|
1 2 3 4 5 6 7 8 9 10 |
create procedure salariedFlag ( @salariedFlag bit ) as select LoginID from [HumanResources].[Employee] where salariedFlag = @salariedFlag |
To execute the stored procedure, we will invoke it using the Exec clause and specifying a value for the @salariedFlag variable.
|
1 2 3 |
exec salariedFlag 1 |
Exec is executing the salariedFlag and the @salaried is receiving the value of 1 (TRUE).
The result displayed by the stored procedure is the following:
These login IDs are the Login IDs with the SalariedFlag equal to TRUE. If we want to see the Login IDs with the value equal to False (0), you can invoke the stored procedure. We can use the exec command or the execute command. We can also include the name of the parameter (which is a best practice to make it easier to understand and easier to maintain especially when multiple parameters are used in the stored procedure).
|
1 2 3 |
execute salariedFlag @salariedFlag='False' |
As you can see in the example, we are using the execute command as an alternative to the exec command and we are invoking the @salariedFlag whereas we did not include that in our previous example. Finally, we are using the value equal to a False string instead of using the 0 value (not recommended, but it works).
The value displayed by this stored procedure execution is the following.
Summary
SQL Server does not include the SQL Boolean data type like other databases, but you can use the SQL Boolean which is the same. The main difference is that instead of using TRUE and FALSE values, you use 1 and 0. This is more efficient because it requires just a single bit to store values. In this article, we showed how to use this bit variable as a variable in a SQL query. We also show how to convert bit data to integer and string and finally, we teach how to use the query inside a stored procedure. If you have more questions about this SQL Boolean data type do not hesitate to write your comments.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023