1

I know that SQL Server does not have boolean data type and your best option is to use BIT data type.

My question is to what boolean expressions evaluate. For example if I have the statement

expr1 AND expr2

and if expr1 is true and expr2 is false, do they internally evaluate to BITs with values 1 and 0? And then the AND operator checks for BITs?

I think this is not the case because then the following should work:

select 1 where 1 and 0

So does sql server internally have a boolean data type?

The following work

select 1 where 1 = 0
select 1 where 1 = 0 and 0 = 0

but this

select 1 where 1 and 0

reports

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

which means that internally sql server handles the expressions as booleans, but why don't have access to that data type.

alfoks
  • 4,324
  • 4
  • 29
  • 44

2 Answers2

3

SQL Server does have a Boolean data type. You can open the Logical Operators (Transact-SQL) manual page and find the following statement:

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

It's just that you can't use this type in the same way you can use other Transact-SQL data types. For instance, you can't declare boolean variables or arguments, add boolean columns to tables, cast to/from a boolean. But you can have boolean expressions and use them in contexts where they are required (WHERE, ON, check constraints…). You can also apply boolean operators to those expressions: AND, NOT et al. (Operators like <, =, LIKE and other can also be considered boolean, in the sense that they return boolean results, but their operands are actually never booleans.)

So, to summarise, there is a boolean type in SQL Server but its use is limited, as described above. Why? My answer may be a silly one, sorry, but that's one that I'm satisfied with: this is the way they chose it to be.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
0

Logical operators results in true or false. So in your example you get a result if expr1 and expr2 is true. To check if its true or not is the operation you do for each expression.

So you compare e.g. a column against something like a bit value column_bit = 0 and this results in true or false. If your expr1 is true and expr2 is false your result is false for the logical AND operator.

Edit:

Your select 1 where 1 and 0 can't work because you don't use a compare operator. 1=1 AND 0=0 works e.g. but its nonsense :)

YvesR
  • 5,922
  • 6
  • 43
  • 70
  • I understand what you say. My question was how does sql server handles these boolean values true, false, unknown. – alfoks Jun 21 '12 at 09:42
  • I know it can't work and why! It seems I can't explain correctly what I'm asking! – alfoks Jun 21 '12 at 09:47
  • Well logical operators handles true/false for each operation but there is no "i am true/false placeholder" for a operation, so you need always to compare against something. – YvesR Jun 21 '12 at 09:49