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

This question already has answers here: How to create a yes/no boolean field in SQL server? (13 answers) Closed 2 years ago.

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

开发者_StackOverflow中文版If not, what is the alternative in MS SQL Server?

---

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

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

---

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

---

SQL Server uses the Bit datatype

---

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

---

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