446

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?

G S
  • 35,511
  • 22
  • 84
  • 118
Ayyappan Anbalagan
  • 11,022
  • 17
  • 64
  • 94

9 Answers9

615

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

John Smith
  • 7,243
  • 6
  • 49
  • 61
kristian
  • 22,731
  • 8
  • 50
  • 78
  • 62
    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. – reido113 Jan 29 '13 at 20:36
  • 24
    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 – Darren Griffith Sep 19 '14 at 20:17
  • 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. – Charles Clayton Jul 17 '15 at 16:04
  • 1
    @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. – CSS Oct 30 '15 at 15:34
106

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.

Community
  • 1
  • 1
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
70

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 -

enter image description here

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/

Community
  • 1
  • 1
John Smith
  • 7,243
  • 6
  • 49
  • 61
  • are `true` and `false` not non-zero values? – Malachi Jan 05 '15 at 16:28
  • @Malachi 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. – John Smith Jan 06 '15 at 16:59
  • 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. – Nathaniel Bendinsky Aug 06 '15 at 18:47
  • @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. – John Smith Aug 06 '15 at 21:31
48

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)
);
John Smith
  • 7,243
  • 6
  • 49
  • 61
Mark Chesney
  • 1,082
  • 12
  • 20
  • 2
    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. – JohnOpincar May 24 '16 at 20:49
9

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

John Smith
  • 7,243
  • 6
  • 49
  • 61
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
8

SQL Server uses the Bit datatype

John Smith
  • 7,243
  • 6
  • 49
  • 61
moribvndvs
  • 42,191
  • 11
  • 135
  • 149
8

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

John Smith
  • 7,243
  • 6
  • 49
  • 61
Rodrick Chapman
  • 5,437
  • 2
  • 31
  • 32
2

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
)
Henrik Høyer
  • 1,225
  • 1
  • 19
  • 27
1

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

Bipul Roy
  • 163
  • 1
  • 14
  • 2
    "BIT is very effective" -> So why use `TINYINT(1)` then? – Caltor Dec 06 '18 at 11:57
  • TINYINT stores only 1 Byte you can check more here https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017 – Bipul Roy Dec 06 '18 at 17:46
  • But Bit is even better as SQL can pack up to 8 of them in one byte. – Caltor Dec 06 '18 at 22:20
  • 1
    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. – AndrewWhalan Mar 18 '19 at 10:35
  • 1
    TINYINT allows the use of '+' for OR and '*' for AND in SQL Server, I suppose... could be useful sometimes. – High Plains Grifter May 02 '19 at 16:02