Which setting estimates the input, and which setting changes a bulk load's commit batches? ROWS_PER_BATCH helps describe input size, while BATCHSIZE determines the bulk operation's batch size.
Separate BATCHSIZE From ROWS_PER_BATCH
BATCHSIZE specifies how many rows belong to a bulk-import batch. Without an enclosing transaction, completed batches can commit independently. By default, the input file is treated as one batch.
ROWS_PER_BATCH supplies an approximate input row count for optimization. It does not create transactions or enforce a row limit. The estimate should reflect the actual loading input rather than an arbitrary performance wish.
I choose the transaction contract before tuning a batch number. I also distinguish a source manifest count from an optimizer estimate. Those values serve different purposes even when their chosen numbers happen to match.
This article uses disk-based SQL Server on Windows and placeholder local paths. The server must be able to read the actual file. Replace the placeholders with approved locations before testing in an isolated database.
Define a Complete Input Format
The demonstration targets a simple staging heap with three columns. Its file contains SourceId, ItemName, and Amount in that order. Use UTF-8 CSV with CRLF line endings and no header row for the shown statements.
FORMAT CSV requires SQL Server 2017 or later. The code page specifies the input encoding explicitly. A different delimiter, column order, or newline convention needs a corresponding reviewed format change.
CREATE TABLE dbo.BulkStage
(
SourceId int NOT NULL,
ItemName nvarchar(100) NOT NULL,
Amount decimal(12,2) NOT NULL CHECK (Amount >= 0)
);
CREATE TABLE dbo.BulkAtomic
(
SourceId int NOT NULL,
ItemName nvarchar(100) NOT NULL,
Amount decimal(12,2) NOT NULL CHECK (Amount >= 0)
);These staging heaps do not enforce source-key uniqueness. Add a separate acceptance check before publishing their rows to a keyed destination. A fast file import is not the entire data validation process.
The file path belongs to the database server's filesystem perspective. A file visible on the client desktop is not automatically visible to SQL Server. Review file permissions and authentication context rather than weakening the server's access controls.
Use Independent Batches With ROWS_PER_BATCH
The following example uses batches of five thousand rows with a chosen estimate of fifty thousand input rows. Those are illustrative configuration values. They are not observed counts, measured optimal settings, or evidence that a file exists.
No surrounding BEGIN TRANSACTION appears in this example. Earlier successfully committed batches can remain when a later batch fails. The caller must account for that partial-progress contract when deciding how to retry.
BULK INSERT dbo.BulkStage
FROM 'C:\SqlImport\SourceItems.csv'
WITH
(
FORMAT = 'CSV', CODEPAGE = '65001',
BATCHSIZE = 5000, ROWS_PER_BATCH = 50000,
TABLOCK, CHECK_CONSTRAINTS,
MAXERRORS = 1,
ERRORFILE = 'C:\SqlImportErrors\SourceItems-run001.err'
);The error-file directory must exist with suitable write permissions. Its filename must be unused because the command will not overwrite an existing error file. Use a unique approved attempt identifier for each real load.
A retry from the beginning can duplicate previously committed staging rows. Persist a load identity and define a cleanup or deduplication policy. Do not infer exactly-once ingestion from the presence of BATCHSIZE.
The estimate does not validate source completeness. A successful statement can still import a file with fewer legitimate rows than expected. Reconcile accepted and rejected records with authoritative source evidence before marking a batch complete.
Understand Log and Lock Tradeoffs
Smaller independent batches can reduce the active transaction's footprint. They also create more commit boundaries and management overhead. Measure actual log usage, blocking, throughput, and recovery behavior on representative input.
Committing does not automatically make every occupied log byte reusable. Recovery model, backups, availability replicas, and other reuse waits affect truncation. Review the database's log_reuse_wait_desc before treating a larger batch as the sole log problem.
TABLOCK requests table-level locking for the bulk operation. That can support efficient rowstore bulk loading under appropriate conditions. It also affects concurrent access and is not a promise of zero blocking.
A clustered columnstore has different locking and compression behavior from this heap example. Do not apply every rowstore assumption to that structure. Review the actual target type before copying the same loading settings.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases WHERE database_id = DB_ID();
SELECT total_log_size_in_bytes, used_log_space_in_bytes,
used_log_space_in_percent
FROM sys.dm_db_log_space_usage;The diagnostic reports the current database's actual state when executed. It does not establish that any proposed load will fit available log space. Include other overlapping work when estimating operational capacity.
I ran both loads on SQL Server 2025 against a generated file of fifty thousand rows. Test file parsing and failure behavior on an isolated instance. Keep measured outcomes separate from the illustrative batch choices shown here.
Treat Minimal Logging as Conditional
For eligible rowstore bulk loading, simple or bulk-logged recovery and TABLOCK are relevant prerequisites. Target indexing, existing rows, replication, and other table properties also matter. TABLOCK alone does not guarantee minimal logging.
Under full recovery, ordinary rowstore bulk inserts are fully logged. A recovery-model change has backup and recovery consequences. Do not change it casually just to make a demonstration appear faster.
Minimal logging still records required allocation and recovery information. It does not mean no transaction log or no capacity requirement. Plan storage and recovery behavior for the permitted operation rather than assuming the log disappears.
An empty unindexed staging heap gives a straightforward loading target. A heavily indexed destination has different maintenance and logging costs. Review whether staged validation and a later controlled publish better match the real acceptance contract.
Handle Rejected Rows as Acceptance Evidence
MAXERRORS controls tolerance for certain input formatting and conversion errors. The positive value one permits a limited error count before cancellation. It is not a universal switch that makes every possible database error tolerable.
Constraint failures have separate behavior and are not governed by that tolerance in the same way. CHECK_CONSTRAINTS requests checking of relevant CHECK and foreign-key constraints. Primary-key and unique constraints remain enforced independently.
ERRORFILE captures appropriate rejected input rows and accompanying diagnostics. SQL Server also creates an associated control file with error details. Protect those files because they can contain raw sensitive source data.
Skipping a bad row can leave the bulk statement successful but the business batch incomplete. Count and inspect rejected records before accepting the load. Keep operational tolerance separate from the business rule about whether partial data is allowed.
Use an Outer Transaction for Atomic Acceptance
An explicit surrounding transaction changes the commit contract. Internal bulk batches remain inside that larger transaction until its final commit. A failure and full rollback can then remove the entire attempted load into the atomic staging table.
The following example additionally requires an empty target and an authoritative expected row count. Replace that chosen count with the source manifest's actual contract. It is an acceptance assertion, separate from the optimization estimate.
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
THROW 50000, 'Start this atomic load outside another transaction.', 1;
IF EXISTS (SELECT 1 FROM dbo.BulkAtomic)
THROW 50001, 'The atomic demonstration target must be empty.', 1;
DECLARE @ExpectedRows bigint = 50000; -- Replace with authoritative manifest count.
BEGIN TRY
BEGIN TRANSACTION;
BULK INSERT dbo.BulkAtomic
FROM 'C:\SqlImport\SourceItems.csv'
WITH
(
FORMAT = 'CSV', CODEPAGE = '65001',
BATCHSIZE = 5000, ROWS_PER_BATCH = 50000,
TABLOCK, CHECK_CONSTRAINTS,
MAXERRORS = 1,
ERRORFILE = 'C:\SqlImportErrors\SourceItems-run002.err'
);
IF (SELECT COUNT_BIG(*) FROM dbo.BulkAtomic) <> @ExpectedRows
THROW 50002, 'Accepted row count differs from the manifest.', 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;Atomicity preserves all-or-nothing table changes but retains a larger transaction's resource requirements. Error files are external evidence and are not undone by database rollback. Include them in the load-attempt retention and resubmission process.
The count assertion does not replace duplicate, range, or relationship validation. Add the required acceptance rules before committing the real publish operation. Equal row counts can still describe different or invalid records.
Verify ROWS_PER_BATCH Against the Loading Contract
Should a failed load preserve earlier batches or leave no destination rows? Answer that before adopting a retry procedure. The correct choice depends on recoverability, acceptance rules, and the source's ability to resume reliably.
I tune ROWS_PER_BATCH as an estimate and BATCHSIZE as a transaction-related control. I verify both against the intended failure behavior. A bulk load should not leave its recovery plan in a cardboard box marked later.
Test valid input, a rejected row, a constraint violation, and an inaccessible error-file directory. Inspect retained rows and files after each path. Then choose batch settings from measured operational evidence rather than copied numeric defaults.
Related reading on this blog: Loading Large Files Fast With BULK INSERT and Minimal Logging for Bulk Loads.
A batch estimate is not a commit boundary, it is planning information inside a defined loading and acceptance contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
