SQL Boolean

In SQL Server, boolean values can be represented using the bit datatype. Bit values differ from boolean values in that a bit can actually be one of three values 1, 0, or NULL; while booleans can only either be true or false.

When assigning bits, it is best to use 1 or zero to indicate it’s on or off status. If using SQL Server 2005 or greater, you can additionally assign a bit by setting it to a string value of ‘true’ or ‘false’. This is not recommended however for two reasons. First, SQL Server has to implicitly convert the bit value prior to the assignment (or comparison). The other reason is that it is not ANSI SQL.

[cc lang=”sql”]
— create the bit
DECLARE @testbit bit

SET @testbit = 1

IF @testbit = 1
BEGIN
SELECT ‘true’
END
[/cc]
In the above example, the string ‘true’ is displayed.

Now let’s assign the bit a value of true and treat it like a boolean.
[cc lang=”sql”]
DECLARE @testbit bit

SET @testbit = ‘true’

IF @testbit = ‘true’
BEGIN
SELECT ‘true’
END
[/cc]
The test code works (on SQL 2005+) and we see the same result as above, true is displayed, however an implicit conversion has taken place. While this may not be a lot of overhead, it is not ideal. It is also not recommended because there is no guarantee it will be supported.

Let’s take a look at two different execution plans. One for comparing the bit using a 1 or 0, and the other comparing the bit as if it were a boolean.

[cc lang=”sql”]
use msdb
go
SELECT *
FROM restorehistory
WHERE [replace] = 0
[/cc]

From the execution plan statement above, we see that the predicate shows a direct comparison with the bit value or: [replace]=[@1] (replace is the actual column name in this example).

Now we will compare the bit column using a boolean like comparison of ‘false’.

[cc lang=”sql”]
use msdb
go

SELECT *
FROM restorehistory
WHERE [replace] = ‘false’
[/cc]

So we see that this execution plan does an implicit conversion by looking at the “Predicate” section. While this takes very little resources it is not recommended.

If readability is an issue, a better implementation would be the following:

[cc lang=”sql”]
declare @true bit
declare @false bit
SET @true = 1
SET @false = 0

SELECT *
FROM restorehistory
WHERE [replace] = @true
[/cc]