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?

score 552 · Accepted Answer · edited May 19 '20 at 06:50

552

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.

edited May 19 '20 at 06:50

Denys Bondarenko

183
1
9

answered Nov 22 '09 at 00:18

Guffa

687,336
108
737
1,005

4

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). – Henrik Erlandsson Sep 20 '13 at 10:02
3

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 – Darren Griffith Sep 19 '14 at 18:26
@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. – Guffa Jun 07 '15 at 10:31
1

Re Mgt Studio, if you are copy+pasting data in you need to have it as True / False also, not as 1 or 0. – gorlaz Oct 27 '15 at 21:44
Then, how about the size? Is it really smaller than a tinyint or char(1)? – Zhang Mar 16 '23 at 02:55
1

@Zhang 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. – userfuser Jul 28 '23 at 09:30

score 131 · Answer 2 · edited Oct 02 '19 at 12:26

131

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

edited Oct 02 '19 at 12:26

Muhammad Waheed

1,048
1
13
30

answered Nov 22 '09 at 00:18

Alex Martelli

854,459
170
1,222
1,395

1

Are you at all concerned that the semantics of bits and booleans are different? – Darth Egregious Mar 14 '17 at 20:15

score 27 · Answer 3 · answered Nov 22 '09 at 00:18

27

You can use the bit column type.

answered Nov 22 '09 at 00:18

Mark Byers

811,555
193
1,581
1,452

score 24 · Answer 4 · answered Jul 30 '13 at 18:51

24

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

answered Jul 30 '13 at 18:51

John Mark

2,458
2
21
17

score 22 · Answer 5 · edited Jun 20 '20 at 09:12

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

`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? — spencer741, Feb 07 '21 at 23:42

score 21 · Answer 6 · answered Oct 16 '13 at 18:53

21

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

answered Oct 16 '13 at 18:53

P_Fitz

819
9
16

3

@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). – CJ Dennis Jun 27 '16 at 03:33

score 18 · Answer 7 · answered Feb 24 '17 at 14:58

18

Sample usage while creating a table:

[ColumnName]     BIT   NULL   DEFAULT 0

answered Feb 24 '17 at 14:58

torina

3,825
2
26
31

1

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? – jslatane Nov 17 '22 at 19:43

score 15 · Answer 8 · answered Apr 12 '17 at 18:55

15

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)

answered Apr 12 '17 at 18:55

Sachith Wickramaarachchi

5,546
6
39
68

score 10 · Answer 9 · answered Nov 22 '09 at 00:21

10

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.

answered Nov 22 '09 at 00:21

o.k.w

25,490
6
66
63

4

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 :) – Rob Grant Jan 15 '14 at 10:46

score 6 · Answer 10 · answered Mar 17 '16 at 12:25

6

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

answered Mar 17 '16 at 12:25

Baqer Naqvi

6,011
3
50
68

2

Normally its used 0 for False and non-zero for True. – Edu Jul 15 '16 at 16:23
2

there are a lot of flavors or true might say a good politician :D – Buda Florin Dec 02 '16 at 08:53

score 2 · Answer 11 · answered Aug 01 '19 at 10:07

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.

Super Kai - Kazuya Ito · Answer 12 · 2022-09-29T22:56:55.150

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

Raja Mali · Answer 13 · 2023-04-21T06:31:53.433

0

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.

edited Apr 21 '23 at 06:31

answered Apr 21 '23 at 06:25

Raja Mali

15
5

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

13 Answers13

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

Linked

Related

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

13 Answers13

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

Linked

Related

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