1 and 0 not recognized as boolean constants in T-SQL

Question

If I try to run this query in SQL Server 2005:

SELECT 1 WHERE NOT ( 0 )

I get this error:

Msg 4145, Level 15, State 1, Line 1 An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Sometimes when debugging complex WHERE statements I will cut out pieces and test a particular scenario by inserting something like 1=1 for true. I would prefer to just use 1 or TRUE but this doesn't work in the above case. Why is it not able to evaluate NOT( 0 ) as a boolean expression?

Edit: This does work fine, showing that I don't have to add a column:

SELECT 1 WHERE NOT(1<>1)

Answer 1

0 and 1 are integers. They are not themselves boolean expressions and in SQL only expressions can have a value of TRUE, FALSE or UNKNOWN. Saying "NOT 7.5" for example is not itself a boolean expression. In actuality, what you are trying to say "NOT (7.5 <> 0)" Thus, in your example, you would need to convert your value to an expression:

Select 1 Where Not ( 0 <> 0 )

In T-SQL at least, there are no constants that you can use for TRUE and FALSE. I.e., there is no means to query for :

Select 1 Where Not TRUE

Answer 2

0 and 1 are not boolean constants, they're bit constants.