如何在SQL server中创建yes/no布尔字段?

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

在从访问数据库或一般情况下进行转换时，创建一个yes/no布尔字段的最佳实践是什么?

10 个解决方案

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

在SQL中，您使用0和1来设置一个位域(就像访问中的一个yes/no字段)。在Management Studio中，它显示为false/true值(至少在最近的版本中)。

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

当通过ASP访问数据库时。NET将字段显示为布尔值。

#2

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

位数据类型通常用于存储布尔值(0表示false, 1表示true)。

#3

You can use the bit column type.

您可以使用位列类型。

#4

You can use the data type bit

您可以使用数据类型位

Values inserted which are greater than 0 will be stored as '1'

插入大于0的值将被存储为“1”

Values inserted which are less than 0 will be stored as '1'

插入小于0的值将被存储为“1”

Values inserted as '0' will be stored as '0'

插入为'0'的值将存储为'0'

This holds true for MS SQL Server 2012 Express

这对MS SQL Server 2012 Express也是如此

#5

You can use the BIT field.

你可以使用位域。

For adding a BIT column to an existing table, the SQL command would look like:

对于向现有表添加位列，SQL命令如下:

ALTER TABLE table_name ADD yes_no BIT

更改表table_name添加yes_no位。

If you want to create a new table, you could do: CREATE TABLE table_name (yes_no BIT).

如果您想要创建一个新表，可以这样做:创建表table_name (yes_no BIT)。

#6

Sample usage while creating a table:

创建表时的示例用法:

[ColumnName]     BIT   NULL   DEFAULT 0

#7

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.

位是最简单的，也占用最少的空间。和“Y/N”比起来不太啰嗦，但我没问题。

#8

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.

从MSDN文章评论。

Bit can take a value of 1, 0, or NULL.

位可以取1、0或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.

SQL Server数据库引擎优化位列的存储。如果一个表中有8个或更少的位列，那么列将存储为1个字节。如果有9到16位的列，这些列将被存储为2字节，依此类推。

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

字符串值TRUE和FALSE可以转换为位值:TRUE转换为1,FALSE转换为0。

Converting to bit promotes any nonzero value to 1.

转换为位将任何非零值提升为1。

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.

位的值为1,0和NULL。看真相表。所以计划相应的值。允许位数据类型为空值可能会增加混淆。

References 1 2

引用1 2

#9

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)

#10

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

位是最合适的选择。否则，我曾经为此目的使用int。1代表真，0代表假。