How do you create a yes/no boolean field in SQL server?
Sql ServerBooleanSqldatatypesSql Server Problem Overview
What is the best practice for creating a yes/no i.e. Boolean field when converting from an access database or in general?
Sql Server Solutions
Solution 1 - Sql Server
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.
Solution 2 - Sql Server
The BIT datatype is generally used to store boolean values (0 for false, 1 for true).
Solution 3 - Sql Server
You can use the bit column type.
Solution 4 - Sql Server
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).
Solution 5 - Sql Server
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
Solution 6 - Sql Server
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
Solution 7 - Sql Server
Sample usage while creating a table:
[ColumnName] BIT NULL DEFAULT 0
Solution 8 - Sql Server
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)
Solution 9 - Sql Server
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.
Solution 10 - Sql Server
bit is the most suitable option. Otherwise I once used int for that purpose. 1 for true & 0 for false.
Solution 11 - Sql Server
> 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.