在测试一些查询时，我将数据库的恢复模式设置为完整，并运行了两个相同的 1M 行插入，有和没有TABLOCK.
在一个TABLOCK方面，我绕过9295了 sql server 2008 实例上8714的日志记录，并绕过了 SQL Server 2017 实例上的日志记录。
在不使用 tablock 的情况下运行插入时，我绕过10356592008 年实例的1068599记录和 2017 年实例的记录。
在 sql server 2008 上进行测试的原因是为了匹配数据加载性能指南关于 ML 操作恢复模型的声明：
仅当您的数据库处于大容量日志记录或简单恢复模式时，最少记录的操作才可用。
那么，如果不是最小日志记录，我在这里看到的是什么？
Use DatabaseName
ALTER DATABASE DatabaseName SET RECOVERY FULL;
GO
BACKUP DATABASE DatabaseName TO DISK = '\\location\DatabaseName.bak';
BACKUP LOG DatabaseName TO DISK = '\\location\DatabaseName_log.trn';
GO
IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Accounts;
END;
GO
CREATE TABLE dbo.Accounts( AccountID INT PRIMARY KEY NOT NULL,
AccountName varchar(255),
DateCreated DATETIME2);
-- Insert 1M Rows into dbo.Account without TABLOCK
GO
SET STATISTICS IO, TIME ON;
INSERT INTO dbo.Accounts (AccountID,AccountName,DateCreated)
SELECT TOP(1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
'Name N ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(255)),
DATEADD(MINUTE,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())
FROM MASTER..SPT_VALUES SPT1
CROSS APPLY MASTER..SPT_VALUES SPT2;
GO
-- check the amount of records in the log file
SELECT count(*)
FROM
fn_dbLog(NULL,NULL);
--1035659 rows
GO
-- clear the log
BACKUP LOG DatabaseName to disk = '\\location\DatabaseName_log2.trn';
GO
--drop the table
IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Accounts;
END;
GO
-- create the table
CREATE TABLE dbo.Accounts( AccountID INT PRIMARY KEY NOT NULL,
AccountName varchar(255),
DateCreated DATETIME2);
-- Insert 1M Rows into dbo.Account WITH TABLOCK
GO
SET STATISTICS IO, TIME ON;
INSERT INTO dbo.Accounts WITH(TABLOCK) (AccountID,AccountName,DateCreated)
SELECT TOP(1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
'Name N ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(255)),
DATEADD(MINUTE,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())
FROM MASTER..SPT_VALUES SPT1
CROSS APPLY MASTER..SPT_VALUES SPT2;
GO
-- check the amount of records in the log file
SELECT count(*)
FROM
fn_dbLog(NULL,NULL);
--9295 rows
