[sql-server] How do you create a yes/no boolean field in SQL server?

What is the best practice for creating a yes/no i.e. Boolean field when converting from an access database or in general?

The BIT datatype is generally used to store boolean values (0 for false, 1 for true).

You can use the BIT field.

For adding a BIT column to an existing table, the SQL command would look like:

ALTER TABLE table_name ADD yes_no BIT

If you want to create a new table, you could do: CREATE TABLE table_name (yes_no BIT).

You can use the data type bit

Values inserted which are greater than 0 will be stored as '1'

Values inserted which are less than 0 will be stored as '1'

Values inserted as '0' will be stored as '0'

This holds true for MS SQL Server 2012 Express

bit is the most suitable option. Otherwise I once used int for that purpose. 1 for true & 0 for false.

bit will be the simplest and also takes up the least space. Not very verbose compared to "Y/N" but I am fine with it.

Sample usage while creating a table:

[ColumnName]     BIT   NULL   DEFAULT 0

You can use the bit column type.

In SQL Server Management Studio of Any Version, Use BIT as Data Type

which will provide you with True or False Value options. in case you want to use Only 1 or 0 then you can use this method:

CREATE TABLE SampleBit(
    bar int NOT NULL CONSTRAINT CK_foo_bar CHECK (bar IN (-1, 0, 1))
)

But I will strictly advise BIT as The BEST Option. Hope fully it's help someone.

There are already answers saying use of Bit. I will add more to these answers.

You should use bit for representing Boolean values.

Remarks from MSDN article.

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

Converting to bit promotes any nonzero value to 1.

Reference

Note: It is good practice to keep values as 1 and 0 only with data type NOT NULL

As Bit have values 1, 0 and NULL. See truth table for this. So plan values accordingly. It might add confusion by allowing NULL value for bit data type.

Reference

You can use the BIT field

To create new table:

CREATE TABLE Tb_Table1
(
ID              INT,
BitColumn       BIT DEFAULT 1
)

Adding Column in existing Table:

ALTER TABLE Tb_Table1 ADD BitColumn  BIT DEFAULT 1

To Insert record:

INSERT Tb_Table1 VALUES(11,0)