How to create a yes/no boolean field in SQL server?

Question

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

Calling

Answer 1

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.

Miserere

And if you link the table in an Access database, true will have the value -1 and false will have the value 0. At least in Access 2003. (This is the version I had handy that was connected to a customer's MSSQL database). – Spirant

Please note that it is not exactly equivalent. If a scalar function returns a bit, you still need to test if it is 0 or 1. For example, dbo.IsReturnsBit(value) = 1 – Allveta

@D-Money: Yes, but you only need to do the comparison if you want to use the value in a condition. If you use the value in the result, then you should not do a comparison. – Miserere

Re Mgt Studio, if you are copy+pasting data in you need to have it as True / False also, not as 1 or 0. – Seventeen

Then, how about the size? Is it really smaller than a tinyint or char(1)? – Digestion

@Digestion it will be smaller only if you have multiple bit fields in that table - then you can fit multiple ones into one byte. If it's just one BIT field per table, then it's the same as tinyint. – Maximamaximal

Answer 2

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

Kowalczyk

Are you at all concerned that the semantics of bits and booleans are different? – Sycosis

Answer 3

You can use the bit column type.

Caveator

Answer 4

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

Sully

Answer 5

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

Vicenary

Bit can take a value of 1, 0, or NULL. If the bit data type allows nulls, I don't have to specify NULL within the field definition? That definition makes me think [field1] [bit] NULL vs [field1] [bit] are equivalent? – Cresida

Answer 6

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

Strove

@BiLaL This is common behaviour across most languages. 0 is false, any non-0 number is true. It was also common for -1 to be the default value for true because in signed binary it has every bit set to 1. Nowadays it's very common to see 1 as the default value for true (only the least significant bit set). – Tiffany

Answer 7

Sample usage while creating a table:

[ColumnName]     BIT   NULL   DEFAULT 0

Pneumatometer

Going off the info in @Somnath Muluk's answer, wouldn't allowing for null values in this column create potential confusion, as values could be 1, 0, or null? – Defoliant

Answer 8

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)

Layton

Answer 9

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.

Lumberjack

It's better I think - no need to worry about Y == y and N = n, pure true or false. Intention is totally obvious, and there are no "special" cases that single character fields invite :) – Turbo

Answer 10

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

Caras

Normally its used 0 for False and non-zero for True. – Harleigh

there are a lot of flavors or true might say a good politician :D – Wadmal

Answer 11

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.

Ailurophobe

Answer 12

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

Skull

Answer 13

Below the List of database where what type of datatype is use for Boolean

-> Oracle -> Number(1)

-> SQL Server -> BIT

-> MySql -> BIT or TINYINT

->postgreSQL -> boolean

I hope this is really helpful thanks.

Peen