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.

 

6 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.

          1. No, you're missing their point.

            You've put:

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

            SELECT Somecolumn = Someothercolumn AS TheSameValue
            FROM Sometable

            they are saying you meant to remove AS TheSameValue from that query. otherwise, you don't make any sense as it's the same query as the one above it. you haven't actually removed the alias

          2. Hi Derek,

            We're not talking about how the current syntax works. At present, if you write SELECT Somecolumn = Someothercolumn FROM Sometable, you are just assigning the alias Somecolumn to the column called Someothercolumn. But if we had a real boolean type, the expression Somecolumn = Someothercolumn would itself return true or false (or null). And the result of that expression could then be aliased. Same thing if I wanted to write SELECT (4 = Column2) AS Colum2Is4 FROM Sometable. The point is that we don't have an actual boolean type. We only have a bit, and that's not the same thing.

            For the same reason, if we had a real boolean type, we could write:

            IF FunctionThatReturnedBoolean()
            BEGIN

            END

            but we can't do that either. We can only have a function that returns a bit. So we have to write:

            IF FunctionThatReturnedBoolean() <> 0
            BEGIN

            END

Leave a Reply

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