Is there a Boolean data type in Microsoft SQL Server like there is in MySQL? [duplicate]

Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

If not, what is the alternative in MS SQL Server?

9 Answers

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 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

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) ); 

SQL Server uses the Bit datatype

Use the Bit datatype. It has values 1 and 0 when dealing with it in native T-SQL

You can use Bit DataType in SQL Server to store boolean data.

Use the BIT datatype to represent boolean data. A BIT field's value is either 1,0 or NULL.

create table <tablename> ( <columnName> bit ) 

Unless you want a threeway boolean you should add NOT NULL DEFAULT 0 like so:

create table <tablename> ( <columnName> bit not null default 0 ) 

I use TINYINT(1)datatype in order to store boolean values in SQL Server though BIT is very effective