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

Question

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?

Mottle

Actually, MySQL does not have a boolean data type. – Contrariety

Answer 1

You could use the BIT datatype to represent boolean data. A BIT field's value is either 1, 0, or null.

Manatee

Also, It is standard practice for 0 to be construed as FALSE, 1 to be construed as TRUE and Nulls, when allowed, would be reflected as NULL. – Flack

Please note that BIT is not equivalent to a boolean. If a scalar function returns a bit, you still need to test if it is 0 or 1. For example, dbo.IsReturnsBit(value) = 1 – Audiogenic

The only downside of this is that it's not particularly user friendly. For instance, I'm displaying a datagrid of a sql table, and I'd like the users to be able to see/edit true or false, not 1 or 0. – Dooryard

@crclayton You can have an associative calculated column that returns true for 1 and 0 for false. Other options are using transforms for reports. This, of course, assumes your typical users who will see this are not programmers that are used to the 0/1 system. – Viviparous

Answer 2

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.

Basildon

Answer 3

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

http://www.firstsql.com/idefend3.htm

https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/

Pother

are true and false not non-zero values? – Novelia

@Novelia a zero is treated as false, and non-zero values are treated as true. But a true evaluates to 1, and false evaluates to 0. For example, declare @b bit = -9; if @b = 1 print 'pass' --notice how bit was set to -9, but is implicitly converted to a 1 (which = "true") and passes the if test. – Pother

It's worth noting that you'll encounter additional overhead with this method, because SQL will be converting your 'true' or 'false' to 1 or 0, respectively. – Mariomariology

@NathanielBendinsky yes, there would be overhead if any casts are involved (not just in this situation though...) . I just wanted to point out that it's possible to just use True / False directly. But yes, it's more efficient to just use 1's and 0's. – Pother

Answer 4

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

Regent

Bit really isn't a Boolean equivalent because you can't assign a Boolean expression to a bit column. Real support would handle direct assign of logical operators to bit field, EG x = y AND z. I like your answer the best because you explicitly point this out. – Genevieve

Answer 5

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

Elielia

Answer 6

SQL Server uses the Bit datatype

Angadreme

Answer 7

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

Toweling

Answer 8

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
)

Brushwork

Answer 9

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

Ron

"BIT is very effective" -> So why use TINYINT(1) then? – Gorse

TINYINT stores only 1 Byte you can check more here learn.microsoft.com/en-us/sql/t-sql/data-types/… – Ron

But Bit is even better as SQL can pack up to 8 of them in one byte. – Gorse

I've found TINYINT(1) works better on MySQL as there BIT is a binary bit value for 1 or more bits, not an ordinal number, which is why they have BOOL as an alias for TINYINT(1). For MS-SQL/TSQL BIT is generally fine. I'd be very surprised if they bothered bit packing multiple bits in a row. – Unicorn

TINYINT allows the use of '+' for OR and '*' for AND in SQL Server, I suppose... could be useful sometimes. – Ashlaring