How to 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?

You can use the `bit` column type.

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

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

Sample usage while creating a table:

[ColumnName] BIT NULL DEFAULT 0

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)

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

You should use [bit](

[To see links please register here]

) 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](

[To see links please register here]

)

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

> [![enter image description here][1]][1]
>
> [Reference](

[To see links please register here]

)


[1]:

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