SQL Server Q&A: Does SQL Server have a boolean data type? Is that a bit?

Image by Ken Treloar

Simple answer: No

SQL Server has a concept of boolean data type but it doesn't have an actual boolean data type. I can't declare a column with a boolean data type, and I can't use it that way. However, the concept of boolean data is present.

For example, if I write the following:

SELECT *
FROM Sometable
WHERE Somecolumn = Someothercolumn

the outcome of that comparison is TRUE, FALSE, or (don't forget) NULL. And remember that NULL isn't a value; it's the lack of a value. WHERE clauses are satisfied when the value of the predicate is TRUE.

So, there is a boolean concept in the language.

However, note that I cannot write this:

SELECT Somecolumn = Someothercolumn AS TheSameValue
FROM Sometable

I can't just return a TRUE or FALSE value.

If I write it without the alias, it will work:

SELECT Somecolumn = Someothercolumn AS TheSameValue
FROM Sometable

but in that case, I'm returning the value of Someothercolumn aliased (named as) Somecolumn. I'm not returning the comparison.

But isn't that just the same as a bit?

No. I can't use a bit value like a boolean. For example, I can't just write this:

IF ISNUMERIC('somestring')
BEGIN

END

Instead, I have to write this:

IF ISNUMERIC('somestring') <> 0
BEGIN

END

Note that I could have written "= 1" but I prefer with bit values to always compare them to zero across all languages, given some languages use -1 for TRUE and others use 1 for TRUE.

 

4 thoughts on “SQL Server Q&A: Does SQL Server have a boolean data type? Is that a bit?”

    1. No, on the contrary. The point is that if there really was a boolean data type, an expression like (Somecolumn = Someothercolumn) would return TRUE, FALSE, or NULL. It could then be aliased as something else and returned as data. But we can't return boolean values in SQL Server.

      1. Both queries are exactly the same. You forgot to remove the 'AS TheSameValue' in the second one as Jamie already mentioned. Only then the '=' will function as an alias.

        1. No you are missing the point. It’s not meant to be valid T-SQL. If we really had a boolean data type, the values of an expression like “a = b” would be true or false and the outcome of that expression could be aliased. We don’t have such a concept. There is no way to return a logical value. The best we have is a bit, which is a poor substitute.

Leave a Reply

Your email address will not be published. Required fields are marked *