我正在使用SQL Server Management Studio v18.5和SQL Server 12运行一些简单的测试，以测试可能迁移到标识列或使用序列，因为在使用INSERT INTO(...); SELECT MAX(ID) + 1 FROM dbo.Test (...);等策略时，大型插入的性能问题。
我在交易中获得了更好的性能，但我不明白，也找不到原因。
当我只选择并运行事务中的内容时，存在速度差异。
这是我正在使用的代码：
CREATE TABLE [dbo].[TEST_MIGRATION] (
[ID] INT NOT NULL,
[Nome] INT NOT NULL,
[Pass] INT NOT NULL
);
测试INSERT INTO(...); SELECT MAX(ID) + 1 FROM dbo.Test (...);：
BEGIN TRAN
DECLARE @i int = 1;
WHILE @i < 10000
BEGIN
SET @i = @i + 1;
INSERT INTO TEST_MIGRATION (ID, Nome, Pass)
VALUES ((SELECT MAX(ID) + 1 FROM TEST_MIGRATION), @i, @i + 20);
END
--COMMIT TRAN
ROLLBACK TRAN
这个的结果是：
[带事务回滚]
结果测试 1：
00：01：16
结果测试2：
00：02：11
[使用事务提交]
结果测试 1：
00：02：28
顺序：
DECLARE @ID INT = (SELECT ISNULL(MAX(ID) + 1, 0) FROM TEST_MIGRATION);
EXEC ('
CREATE SEQUENCE TEST_MIGRATION_ID_Seq
START WITH ' + @ID +
' INCREMENT BY 1;'
)
;
ALTER TABLE TEST_MIGRATION
ADD CONSTRAINT df_TEST_MIGRATION_ID
DEFAULT (NEXT VALUE FOR dbo.TEST_MIGRATION_ID_Seq) FOR ID
;
BEGIN TRAN
DECLARE @i int = 1;
WHILE @i < 10000
BEGIN
SET @i = @i + 1;
INSERT INTO TEST_MIGRATION (Nome, Pass)
VALUES (@i, @i + 20);
END
COMMIT TRAN
这个的结果是：
[带事务回滚]
结果测试 1：
00：00：01
结果测试2：
00：00：01
结果测试3：
00：00：02
[使用事务提交]
结果测试 1：
00：00：01
结果测试2：
00：00：00
[无交易]
结果测试 1：
00：00：07
结果测试2：
00：00：08
结果测试3：
00：00：07
使用"标识"列：
CREATE TABLE [dbo].[TEST_MIGRATION_IDENTITY] (
[ID] INT NOT NULL IDENTITY(1, 1),
[Nome] INT NOT NULL,
[Pass] INT NOT NULL
);
BEGIN TRAN
DECLARE @i int = 1;
WHILE @i < 10000
BEGIN
SET @i = @i + 1;
INSERT INTO TEST_MIGRATION_IDENTITY(Nome, Pass)
VALUES (@i, @i + 20);
END
COMMIT TRAN
这些是这个的结果：
[带事务回滚]
结果测试 1：
00：00：00
结果测试2：
00：00：00
[使用事务提交]
结果测试 1：
00：00：00
结果测试2：
00：00：00
[无交易]
结果测试 1：
00：00：07
结果测试2：
00：00：07
TL;DR：通过使用事务，我获得了更好的性能，而不是单独的查询块。对此有何解释？
我通过使用事务获得了更好的性能，而不是单独的查询块
如果没有事务，SQL Server 必须在每次插入后刷新日志文件。 这是一个物理 IO，您的会话在每次插入后等待日志强化到磁盘。
使用事务，SQL Server 不必在每次插入后刷新日志。 相反，日志是在后台异步写入的，您只需等待事务的最后一个日志记录在commit transaction时被强化。