SQL Server 2016中INSERT SELECT UNION ALL操作的原子性咨询

嘿，针对你升级SQL Server 2016后遇到的这个INSERT SELECT UNION ALL原子性问题，我来给你梳理清楚：

在SQL Server（包括2016版本）中，单条INSERT语句（哪怕包含SELECT UNION ALL这类多数据源合并的子查询）本身是完全原子的。简单说就是：整个INSERT操作要么100%成功，所有符合条件的行都被写入目标表；要么彻底失败，不会有任何一行被插入，绝对不会出现“部分行插入成功、部分失败”的情况。

针对你场景的具体拆解 #

你场景里的这条关键语句：

INSERT INTO dbo.TestTable ( AsAt, Key1, Key2 )
SELECT AsAt, Key1, 130 FROM dbo.TestTable AS tt
UNION ALL
SELECT AsAt, Key1, 129 FROM ...

• 这里的UNION ALL只是用来把两个数据源的结果集合并成一个，作为INSERT的输入数据。整个语句是一个独立的隐式事务单元——哪怕你没手动写BEGIN TRANSACTION，SQL Server也会自动为这条单语句创建事务并保证原子性。
• 如果执行过程中出现任何错误（比如违反了你创建的唯一非聚集索引ixu_TestTable的约束、某个子查询执行出错、权限不足等），整个INSERT操作会立刻回滚，不会留下任何痕迹。注意：前面那条单独的INSERT ... VALUES是另一个独立的隐式事务，不会被这次回滚影响。

关于SQL Server 2016版本的特殊提醒 #

虽然原子性规则本身没有变化，但2016的一些新特性可能让你产生“和旧版本不一样”的错觉：

• 错误处理逻辑微调：2016对批量操作的错误处理有一些优化，比如SET XACT_ABORT的默认行为，但这不会破坏单条INSERT的原子性。
• 锁机制优化：你的表有聚集索引和唯一非聚集索引，2016在锁的获取和释放逻辑上做了调整，这只会影响并发性能，和原子性无关。

快速验证的小方法 #

如果你想亲自确认这个行为，可以故意在UNION ALL的第二个查询里构造一条违反唯一索引的行，执行这条INSERT后去查dbo.TestTable，你会发现这条INSERT的所有行都没被插入，而之前单独插入的('20180131', 1, 3)依然存在。

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