SQL Server - BIT 数据类型问题

Question

我在我的 SQL Server 2012 中有一张表格，如下所示：

CREATE TABLE TEST (ID INT,NAME VARCHAR(100),STATUS BIT);

我错误地以这种方式插入了数据：

，。

INSERT INTO TEST SELECT 1,'A',2;

当我查询结果时，我发现“STATUS”列存储了“1”。无论我插入什么值，它都会存储“1”。我认为如果我将列定义为BIT，则不应允许插入0和1之外的任何值。

是否有任何跟踪标志限制此类插入？我现在正在使用SQL Server 2012 SP3。

- Yashwanth Aluru

---

你说的“无论我插入什么值”是什么意思？能给一个示例值吗？ - Harry

我是指除了“0”以外的任何东西。我在我的问题中举了一个例子“2”。 - Yashwanth Aluru

是的，这是已记录的行为："将值转换为位会将任何非零值提升为1." - Damien_The_Unbeliever

问题在于隐式转换。只要使用带有明确数据类型的参数化查询，一切都会好起来的。 - Luaan

让我们面对现实，如果 ANSI_WARNINGS 开启，Microsoft 应该在试图将非 NULL、ZERO 或 ONE 值存储到 BIT 列或变量上时发出警告，而不是永远保留这种无意义的错误。 - Julio Nobre

Answer 1

使用位数据类型。

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

字符串值“TRUE”和“FALSE”可以转换为位值：“TRUE”转换为1，“FALSE”转换为0。

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

参考链接：MSDN。

- Harry

---

为什么不在将值插入数据库之前，在应用程序中设置此限制？或者这不是一个选项吗？ - Harry

这里不适用 CHECK 约束。在应用此约束时，已经发生了隐式或显式转换为 bit 的操作，因此所有 CHECK 约束可能观察到的值都是 0 或 1。 - Damien_The_Unbeliever

我觉得你没有理解我的问题。我不能在所有的表上声明CHECK约束。我想知道这是否是SQL Server中的一个bug，是否有任何追踪标志可以限制用户除了0和1之外插入任何值。这意味着如果我插入除了0和1之外的任何值，我应该会收到类似"数据过长"或"数据溢出"的错误提示。 - Yashwanth Aluru

@YashwanthAluru - 这不是一个“bug”。这是已记录的行为。你必须记住，T-SQL很古老，以至于在其他语言中，“在过去”，将0视为false，“任何非零值”视为true也被认为是合理的。因此，他们保持了这种行为以实现向后兼容性。 - Damien_The_Unbeliever

如果您想要那个功能，似乎应该使用带有检查约束的 tinyint。 - Nick.McDermaid