Only three values
The datatype
bit can only store three values:
0,
1 and
null.
If a variable whose data type is
bit is assigned value that is different from
0 and is not
null, the variable's value will be set to
1. This is demonstrated in the following simple
sql batch:
declare @B bit;
set @B = 0 ; print(isNull(str(@B), 'null')); -- 0
set @B = 0.4 ; print(isNull(str(@B), 'null')); -- 1
set @B = 0.6 ; print(isNull(str(@B), 'null')); -- 1
set @B = 1 ; print(isNull(str(@B), 'null')); -- 1
set @B = -1 ; print(isNull(str(@B), 'null')); -- 1
set @B = 2 ; print(isNull(str(@B), 'null')); -- 1
set @B = -2 ; print(isNull(str(@B), 'null')); -- 1
set @B = 123.456; print(isNull(str(@B), 'null')); -- 1
set @B = null ; print(isNull(str(@B), 'null')); -- null
Although the name of this datatype is bit, it needs at least two bits to be able to store these three values.
No boolean data type
SQL Server does not have a real
boolean datatype (which would be named
bool or
boolean). In order to store boolean like values in SQL Server, usually, developers resort to the
bit datatype because of the three values it can store.
Because SQL server does not have a »real« boolean datatype, it also does not have the constant values true and false.
bit data type used in an if statement
It should be noted that SQL server does not allow an expression that evaluates to a
bit data type in an
if statement: it causes the error
Msg 4145 …An expression of non-boolean type specified in a context where a condition is expected….
Thus, the expression needs to explicitly be compared to 1 or 0 or is null, as shown in the following example:
declare
@do_it bit;
set @do_it = 0;
if @do_it = 1 print('Going to do it' )
else if @do_it = 0 print('Not going to do it' )
else print('I am not sure if I am going to do it')