SQL Server插入decimal至bigint丢失精度无报错原因及强制报错方法

I. 为什么会丢失精度且无错误抛出？ #

这本质上是SQL Server隐式数据类型转换规则和默认会话设置共同决定的：

• 当你执行标准INSERT语句，将带小数的decimal值插入bigint列时，SQL Server会自动触发隐式转换。对于数值范围在bigint容纳范围内（即不超过9223372036854775807、不低于-9223372036854775808）但存在小数部分的decimal值，SQL Server默认行为是直接截断小数部分，保留整数部分存入bigint列，而不会抛出错误。
• 这种设计是因为SQL Server将“小数截断到整数”视为一种合法的隐式转换，而非“错误级别的精度丢失”——只有当转换导致数值溢出bigint的取值范围时，才会默认抛出溢出错误。
• 你提到批量插入会触发错误，这是因为BULK INSERT等批量操作默认启用了更严格的转换检查，等效于开启了NUMERIC_ROUNDABORT设置，而单条INSERT的会话默认设置允许这种截断行为。

II. 强制SQL Server在此场景下抛出错误的方法 #

有几种可靠的方式可以实现这个需求，按推荐程度排序：

1. 启用NUMERIC_ROUNDABORT会话设置

这是最直接的全局会话级解决方案，只要开启这个设置，任何会导致精度丢失的数值转换都会触发错误：

-- 先确保ANSI_WARNINGS处于开启状态（NUMERIC_ROUNDABORT依赖此设置）
SET ANSI_WARNINGS ON;
-- 启用精度丢失报错
SET NUMERIC_ROUNDABORT ON;

-- 此时执行插入带小数的decimal值到bigint列会抛出错误
INSERT INTO YourTargetTable (YourBigIntColumn)
VALUES (123.45);

注意：这个设置是会话级的，会影响当前连接下的所有后续操作，如果需要全局生效，可以在服务器级别修改默认设置，或者在应用连接数据库时自动执行这些SET语句。

2. 插入前显式检查并抛出错误

如果你不想修改全局会话设置，可以在插入逻辑里显式判断decimal值是否包含小数部分，若包含则主动抛出错误：

DECLARE @inputValue decimal(18, 2) = 123.45;

-- 检查值是否等于其整数部分（无小数）
IF @inputValue <> FLOOR(@inputValue)
BEGIN
    -- 自定义错误信息和错误码
    THROW 50001, '插入值包含小数部分，无法转换为bigint类型', 1;
END

-- 检查通过后再执行插入
INSERT INTO YourTargetTable (YourBigIntColumn)
VALUES (@inputValue);

3. 给目标表添加CHECK约束或触发器

如果需要对表进行永久的强制检查，可以使用CHECK约束或INSTEAD OF触发器：

• CHECK约束（适用于从同表的decimal列插入的场景）：

  ALTER TABLE YourTargetTable
  ADD CONSTRAINT CK_NoDecimalInBigIntColumn 
  CHECK (YourBigIntColumn = FLOOR(YourBigIntColumn));
  

• INSTEAD OF触发器（适用于所有插入场景）：

  CREATE TRIGGER trg_BlockDecimalTruncation
  ON YourTargetTable
  INSTEAD OF INSERT
  AS
  BEGIN
      -- 检查插入的数值是否存在小数部分
      IF EXISTS (SELECT 1 FROM inserted WHERE inserted.YourBigIntColumn <> FLOOR(inserted.YourBigIntColumn))
      BEGIN
          THROW 50001, '禁止插入带小数的值到bigint列', 1;
      END
      ELSE
      BEGIN
          -- 检查通过后执行实际插入
          INSERT INTO YourTargetTable (YourBigIntColumn)
          SELECT YourBigIntColumn FROM inserted;
      END
  END
  

内容的提问来源于stack exchange，提问作者TheLegendaryCopyCoder