Is there a Boolean data type in Microsoft SQL Server like there is in MySQL? [duplicate]
You could use the BIT
datatype to represent boolean data. A BIT
field's value is either 1, 0, or null.
You may want to use the BIT
data type, probably setting is as NOT NULL
:
Quoting the MSDN article:
bit (Transact-SQL)
An integer data type that can take a value of 1, 0, or NULL.
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
You are looking for a bit
. It stores 1 or 0 (or NULL
).
Alternatively, you could use the strings 'true'
and 'false'
in place of 1 or 0, like so-
declare @b1 bit = 'false'print @b1 --prints 0declare @b2 bit = 'true'print @b2 --prints 1
Also, any non 0 value (either positive or negative) evaluates to (or converts to in some cases) a 1.
declare @i int = -42print cast(@i as bit) --will print 1, because @i is not 0
Note that SQL Server uses three valued logic (true
, false
, and NULL
), since NULL
is a possible value of the bit
data type. Here are the relevant truth tables -
More information on three valued logic-
Example of three valued logic in SQL Server
http://www.firstsql.com/idefend3.htm
https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/