393

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

Muhammad Waheed
  • 960
  • 1
  • 11
  • 29
leora
  • 177,207
  • 343
  • 852
  • 1,351

11 Answers11

524

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.

Denys Bondarenko
  • 149
  • 1
  • 8
Guffa
  • 666,277
  • 106
  • 705
  • 986
  • 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
128

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

Muhammad Waheed
  • 960
  • 1
  • 11
  • 29
Alex Martelli
  • 811,175
  • 162
  • 1,198
  • 1,373
27

You can use the bit column type.

Mark Byers
  • 767,688
  • 176
  • 1,542
  • 1,434
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).

John Mark
  • 2,398
  • 2
  • 19
  • 17
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

P_Fitz
  • 779
  • 8
  • 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
21

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.

enter image description here

Reference

Community
  • 1
  • 1
Somnath Muluk
  • 51,453
  • 32
  • 215
  • 222
  • `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
16

Sample usage while creating a table:

[ColumnName]     BIT   NULL   DEFAULT 0
torina
  • 3,305
  • 2
  • 22
  • 28
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)
Sachith Wickramaarachchi
  • 4,467
  • 5
  • 34
  • 57
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.

o.k.w
  • 24,849
  • 6
  • 63
  • 62
  • 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
6

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

Baqer Naqvi
  • 5,162
  • 2
  • 43
  • 64
2

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.

PatsonLeaner
  • 1,092
  • 12
  • 24