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?

The equivalent is a BIT field.

In SQL you use 0 and 1 to set a bit field (just as a yes/no field in Access). In Management Studio it displays as a false/true value (at least in recent versions).

When accessing the database through ASP.NET it will expose the field as a boolean value.

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

You can use the bit column type.

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

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

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)

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.

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

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.

If you are using Postgres, the BIT type won't support it. So, you have to use INT for storing boolean type. In that case, you can assume 0 as false and 1 as true.

[ColumnName]  INT NULL  DEFAULT 0

You can use BIT type which can have 1 or 0, or also NULL if NULL is allowed.

BIT type converts:

• Any integer values except 0 to 1.
• Any integer string values except "0" to 1.
• "0" to 0.

Then, you can create a table with BIT type as shown below:

CREATE TABLE doctor (
  id INT IDENTITY,
  name NVARCHAR(50),
  on_call BIT, -- Here
  PRIMARY KEY(id)
)
GO

Then, insert rows as shown below:

INSERT INTO doctor 
VALUES ("John", 1), ("Tom", 0), ("Lisa", "-23"), ("Kai", "0"), ("Bob", NULL)
GO

1> SELECT * FROM doctor
2> GO
id name on_call
-- ---- -------
 1 John       1 <- 1
 2 Tom        0 <- 0
 3 Lisa       1 <- "-23"
 4 Kai        0 <- "0"
 5 Bob     NULL <- NULL