Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?
If so, what is the alternative in MS SQL Server?
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.
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 0
declare @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 = -42
print 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/
There is boolean data type in SQL Server. Its values can be TRUE
, FALSE
or UNKNOWN
. However, the boolean data type is only the result of a boolean expression containing some combination of comparison operators (e.g. =
, <>
, <
, >=
) or logical operators (e.g. AND
, OR
, IN
, EXISTS
). Boolean expressions are only allowed in a handful of places including the WHERE
clause, HAVING
clause, the WHEN
clause of a CASE
expression or the predicate of an IF
or WHILE
flow control statement.
For all other usages, including the data type of a column in a table, boolean is not allowed. For those other usages, the BIT
data type is preferred. It behaves like a narrowed-down INTEGER
which allows only the values 0
, 1
and NULL
, unless further restricted with a NOT NULL
column constraint or a CHECK
constraint.
To use a BIT
column in a boolean expression it needs to be compared using a comparison operator such as =
, <>
or IS NULL
. e.g.
SELECT
a.answer_body
FROM answers AS a
WHERE a.is_accepted = 0;
From a formatting perspective, a bit
value is typically displayed as 0
or 1
in client software. When a more user-friendly format is required, and it can't be handled at an application tier in front of the database, it can be converted "just-in-time" using a CASE
expression e.g.
SELECT
a.answer_body,
CASE a.is_accepted WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS is_accepted
FROM answers AS a;
Storing boolean values as a character data type like char(1)
or varchar(5)
is also possible, but that is much less clear, has more storage/network overhead, and requires CHECK
constraints on each column to restrict illegal values.
For reference, the schema of answers
table would be similar to:
CREATE TABLE answers (
...,
answer_body nvarchar(MAX) NOT NULL,
is_accepted bit NOT NULL DEFAULT (0)
);
boolean
data type. – a_horse_with_no_name