Fastest MS SQL Inserts — Part 7: Index Impact

Every production table has indexes. Every INSERT has to maintain them. Add 5 non-clustered indexes to a table and the Bulk API — the fastest single-thread strategy — slows down 7x. The other strategies converge: with enough indexes, the choice of insert strategy stops mattering because index maintenance dominates.

But that's only the start. The surrounding table design decisions matter as much: row width, compression, columnstore, fill factor, GUID vs INT keys, change tracking, temporal history, JSON/XML indexes, statistics auto-update, encryption, and so on all impose their own INSERT tax — and some of them are counter-intuitive (compression makes inserts slower; stats OFF makes them slower; sparse columns are worse than nothing when filled).

This post measures them all. If you only read the first table, index count is the headline — but the rest of the post is where the surprises live.

The Test

We create 0, 1, 3, or 5 non-clustered indexes on the person table, then run the same 100,000-row insert with three strategies: Bulk API, JdbcTemplate, and Hibernate persist.

 1@Service
 2public class IndexBenchmarkHelper {
 3    private final JdbcTemplate jdbcTemplate;
 4
 5    // Five indexes representing realistic patterns
 6    private static final List<IndexDef> ALL_INDEXES = Arrays.asList(
 7        new IndexDef("IX_person_username",        "person", "user_name"),
 8        new IndexDef("IX_person_lastname",        "person", "last_name"),
 9        new IndexDef("IX_person_firstname",       "person", "first_name"),
10        new IndexDef("IX_person_years",           "person", "years"),
11        new IndexDef("IX_person_name_composite",  "person", "last_name, first_name")
12    );
13
14    public void setIndexCount(int count) {
15        dropAllIndexes();
16        for (int i = 0; i < count; i++) {
17            createIndex(ALL_INDEXES.get(i));
18        }
19    }
20}

The indexes represent a mix of:

• Selective columns (username — unique values)
• Low-selectivity columns (years — few distinct values)
• Composite indexes (last_name + first_name)

Results

Bulk API

JdbcTemplate

Hibernate Persist

Analysis

Bulk API is the most sensitive

With 0 indexes, Bulk API is by far the fastest (784ms). But with 5 indexes, it's 5,290ms — slower than JdbcTemplate with 0 indexes. The reason: Bulk API uses minimal-logging INSERT operations that bypass some of the normal logging overhead. But when non-clustered indexes exist, the engine must update each index B-tree for every row, and this overhead dominates.

JdbcTemplate is the most resilient

JdbcTemplate's index penalty is relatively modest: only 2.3x slower with 5 indexes vs 0. Since JdbcTemplate sends standard INSERT statements, the SQL Server optimizer can plan the index maintenance more efficiently.

The crossover point

With 3+ indexes, all strategies converge to similar performance. The strategy choice matters less when index maintenance dominates the insert cost.

Practical Implications

1. Drop indexes before bulk loads if possible. Recreating them after is often faster than maintaining them during the insert.

2. Choose your strategy based on your table's index count:

   • 0-1 indexes: Bulk API is king
   • 3+ indexes: JdbcTemplate or Hibernate — the overhead is comparable
   • Many indexes: Consider ALTER INDEX ... DISABLE before load, then rebuild

3. Composite indexes cost the same as single-column indexes for insert overhead. A composite index on (last_name, first_name) costs the same as two separate single-column indexes.

4. Filtered indexes (with a WHERE clause) are cheaper — they only need updating when the inserted row matches the filter.

Index count: the summary

A table with 5 non-clustered indexes is 6-11x slower to load than one with just the clustered PK — and the choice of insert strategy barely matters anymore. The actionable advice: disable non-essential indexes before bulk loads, rebuild them after.

Beyond Indexes: Table Design & Logging Choices

The index count is only one dimension of INSERT cost. Below are 10 more table-design decisions that each add their own tax, in order of magnitude:

Row size impact

Wider rows = proportionally slower inserts, regardless of strategy:

LOB columns (NVARCHAR(MAX)) force row-overflow storage and out-of-row allocation, adding significant I/O overhead beyond the raw data size increase.

Data compression

SQL Server supports ROW and PAGE compression on tables:

Compression makes inserts slower, not faster. The CPU overhead of compressing each row or page outweighs any I/O savings. For narrow rows like our person table, compression adds cost without meaningful space savings. Compression is a read optimization (smaller pages = faster scans), not a write optimization.

Columnstore index

Columnstore indexes store data in compressed column segments rather than row pages. They excel at analytical queries but have different insert characteristics:

Columnstore inserts are slower because data goes into a delta store (a hidden B-tree rowgroup) until it reaches ~1 million rows, then gets compressed into a columnstore segment. Larger batch sizes help because they fill the delta store faster, triggering compression sooner.

Heap + TABLOCK (minimal logging)

A heap table (no clustered index) with the TABLOCK hint enables minimal logging in SIMPLE/BULK_LOGGED recovery mode:

The heap without TABLOCK is surprisingly slow — without a clustered index, SQL Server uses IAM-driven allocation which leads to page contention. TABLOCK fixes this by allowing bulk allocation. But a clustered table with TABLOCK is still fastest overall.

BCP (bcp.exe) — the external baseline

The bcp command-line utility is SQL Server's native bulk loader:

1bcp testdb.dbo.person in data.txt -c -t"\t" -S localhost,1433 -U weblogic -P weblogic -b 1000

BCP (843ms) is slower than the Java Bulk API (691ms). This is because BCP reads from a flat file (disk I/O) while our Java tests generate data in memory. BCP also does not parallelize across connections. The Java Bulk API over in-memory data is the actual performance ceiling.

Fill factor tuning

The clustered index fill factor controls how full each page is filled during index creation/rebuild. Lower fill factor = more empty space per page = fewer page splits during random-key inserts, at the cost of more pages (larger table size, more I/O on reads).

Fill factor 100 is fastest for sequential inserts — which makes sense: our test inserts monotonically increasing IDs, so there are no page splits regardless of fill factor. Lower fill factors just waste space. Lower fill factors (70-80) help when you have random-key inserts or heavy update patterns that cause page splits.

Partitioning & SWITCH

SQL Server partitioning splits a table into independent chunks on-disk. The SWITCH PARTITION command atomically swaps a staging table into a partition — a metadata-only operation that takes milliseconds regardless of row count:

1-- Partition function and scheme
2CREATE PARTITION FUNCTION pf_person(INT) AS RANGE LEFT FOR VALUES (25000, 50000, 75000);
3CREATE PARTITION SCHEME ps_person AS PARTITION pf_person ALL TO ([PRIMARY]);
4
5-- SWITCH operation (metadata-only, no data movement)
6ALTER TABLE person_staging SWITCH TO person_partitioned PARTITION 4;

The SWITCH pattern is faster because the heavy work (bulk loading) happens on an unpartitioned heap table, and only the final metadata update touches the partitioned table. This is the industry standard for zero-downtime bulk loads — the main table stays queryable throughout the load.

Caveat: The staging table must have an identical schema plus a CHECK constraint matching the target partition's range. Both tables must live on the same filegroup.

Clustered key: INT vs GUID

The clustered key defines the physical order of rows on disk. Random keys (like UUIDs) cause page splits — new rows must be inserted into the middle of existing pages, so SQL Server has to split a full page in two and copy half the rows.

The 19% penalty for random GUIDs is actually small — because we inserted into an empty table. On an existing populated table, random GUIDs can be 2-5x slower as the index grows and new insertions hit densely-packed pages. If you must use GUIDs, prefer NEWSEQUENTIALID() on SQL Server or an "ordered GUID" (UUIDv7/ULID) on the application side.

Change Tracking

SQL Server Change Tracking is a lightweight CDC alternative that records which rows were modified (not the changed values). It's enabled per-database and per-table:

1ALTER DATABASE testdb SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
2ALTER TABLE person_ct ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

Change Tracking has significant insert overhead — each row generates an internal side-table entry with the operation and version. Enable it only if downstream consumers actually need the change feed. For audit alone, temporal tables are cheaper.

Note: Full Change Data Capture (CDC) — which records entire row values — is Enterprise-only and would have even higher overhead than CT.

Temporal (system-versioned) tables

Temporal tables automatically track row history in a shadow table. Every INSERT, UPDATE and DELETE writes to both the current table and the history table:

1CREATE TABLE person_temporal (
2    person_id INT PRIMARY KEY,
3    user_name NVARCHAR(30), first_name NVARCHAR(10),
4    last_name NVARCHAR(15), years INT,
5    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
6    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
7    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
8) WITH (SYSTEM_VERSIONING = ON);

Surprisingly cheap — only 31% overhead for full point-in-time history. Compare that to the AFTER INSERT trigger approach from Part 8 which was 2.2x slower. For audit requirements, temporal tables are the right answer.

For pure inserts the history table doesn't receive rows (only updates/deletes populate it), so the overhead is almost entirely the extra period column maintenance.

Filtered & INCLUDE indexes

Not all non-clustered indexes are created equal. Two variations can dramatically cut insert cost:

• Filtered index — only indexes rows matching a WHERE clause (WHERE years >= 50)
• INCLUDE index — stores extra columns only at the leaf level (not in the sort key)

Filtered indexes are the biggest win: 60% cheaper than a full index on the same column because most rows never touch the index. If your queries only need "the senior customers", only index them.

INCLUDE beats composite by 30% because INCLUDE columns aren't maintained in sort order — they're just stored at the leaf level for covering reads.

Computed columns

Computed columns are either PERSISTED (materialized on write) or non-persisted (computed on read).

1CREATE TABLE person_computed (
2    person_id INT PRIMARY KEY, user_name NVARCHAR(30), first_name NVARCHAR(10),
3    last_name NVARCHAR(15), years INT,
4    full_name AS (first_name + N' ' + last_name) PERSISTED,
5    years_bucket AS (CASE WHEN years < 30 THEN N'young' ELSE N'old' END)
6)

Essentially free. The persisted column's string concatenation adds negligible CPU cost compared to I/O. The non-persisted column costs nothing on insert — it's evaluated on reads. Use them freely for derived values; they're much cleaner than application-side computations and support indexing.

Trace flag 610

TF 610 is a legacy trace flag that enables minimally-logged bulk inserts into clustered tables.

No measurable difference for our workload. Why? TF 610 helps BULK INSERT, INSERT INTO ... SELECT, and BCP operations — not regular INSERT statements with addBatch(). The MS SQL JDBC driver's Bulk Copy API already uses minimal logging when applicable. Skip this flag unless you're using BULK INSERT or BCP directly.

Delayed durability

DELAYED_DURABILITY = FORCED makes transaction commits asynchronous — the log flush happens on a background timer instead of synchronously at COMMIT. Risk: a crash loses recent transactions.

Counter-intuitive finding. Delayed durability makes our test slower, not faster. Why?

Delayed durability batches multiple small transactions' log flushes together. It helps workloads with many frequent small COMMITs (think OLTP with per-row inserts). Our test uses one large transaction, so there's nothing to batch — DD just adds coordination overhead without any benefit.

Takeaway: DD is a per-transaction optimization. For bulk inserts, use fewer, larger transactions instead of enabling DD on frequent small ones.

XML vs JSON columns

Semi-structured data in SQL Server comes in two flavors:

• XML column type: Schema-less XML with built-in XQuery support, validation on insert
• JSON stored as NVARCHAR(MAX) + CHECK (ISJSON(data) = 1): Lightweight JSON validation

XML is 2.3x slower than JSON for the same semantic payload. XML columns:

• Parse the XML into a native tree representation on insert
• Validate well-formedness
• Store in an optimized binary tree format (not the original string)

If you need semi-structured storage, prefer JSON (NVARCHAR + ISJSON) unless you specifically need XQuery, XPath or XML schema validation.

Sparse columns

Sparse columns are SQL Server's optimization for tables with many mostly-NULL columns:

Surprise: sparse columns offer no insert benefit when NULL-heavy (expected tie) but are 14% SLOWER when filled. Sparse columns use a special on-disk representation that's efficient for NULL storage but more expensive when the column has a value. Use SPARSE only when:

• The column is >95% NULL on real data
• You're optimizing storage (sparse saves disk space) rather than insert speed

AUTO_UPDATE_STATISTICS

1ALTER DATABASE testdb SET AUTO_UPDATE_STATISTICS { ON | OFF }

Counter-intuitive: turning OFF auto-stats doesn't speed up inserts — it slows the overall workload by 50%. Why? With stats OFF, SQL Server's query optimizer makes poor decisions for the INSERT plan itself. The optimizer uses statistics to choose index access paths, batch sizes, and memory grants even for simple INSERTs with no WHERE clause.

Takeaway: leave AUTO_UPDATE_STATISTICS on. The savings from skipping stats maintenance are dwarfed by the cost of a worse query plan.

Server-side encryption (ENCRYPTBYPASSPHRASE)

SQL Server supports server-side encryption via ENCRYPTBYKEY, ENCRYPTBYPASSPHRASE, etc. We tested with 3 encrypted columns per row (user_name, first_name, last_name):

Encryption is CPU-expensive. The actual AES-256 operations happen server-side, consuming SQL Server CPU. Minimize the number of encrypted columns and consider:

• Always Encrypted — client-side encryption (CPU cost moves to the app)
• TDE — database-level encryption (works at page level, lower per-row overhead)
• Column-level encryption only on actual PII, not every text field

ID Generation Strategies

How you generate primary keys matters more than you might think:

App-side ID generation wins. The surprises:

• RETURN_GENERATED_KEYS is a significant tax (+34%) — many ORMs set it by default so they can populate the entity's id field. If you don't need the generated IDs back, suppress it.
• SET IDENTITY_INSERT ON costs more than you'd expect — the mode toggle isn't free, and the engine has to validate uniqueness against the identity seed.
• SEQUENCE objects are convenient but add a DEFAULT lookup per row.

Recommendation: generate IDs on the application side (ULID, Snowflake, pre-fetched sequence batch, or a SELECT NEXT VALUE FOR seq FROM GENERATE_SERIES(1,1000) pre-fetch pattern) for the fastest batched inserts.

Foreign Keys

Trusted (enforced) foreign keys cost about 21% — the engine validates every row's dept_id against the department table. Disabled FKs still cost a small 5% because the optimizer treats them as untrusted and may generate different plans.

For large bulk loads with known-good data: disable FK constraints, load, then re-enable with WITH CHECK to re-validate. The re-validation is faster than per-row checking.

Batch Retry Patterns

Real-world data often has bad rows. How you handle one bad row in a batch has dramatic cost:

The abort-on-error pattern completes in 302ms because each 1,000-row sub-batch that contains a bad row is simply discarded (we lose up to 999 good rows per bad row). The per-row pattern inserts 9,999 of 10,000 rows but takes 7.5x longer.

The right answer depends on your data integrity requirements:

• Known-good data: use the batch pattern — if any row fails, there's a bug upstream.
• Dirty data with some bad rows: use smaller batches (100 instead of 1000) so you lose less on each failure; the per-row path should be reserved for the final retry pass.
• Zero tolerance for data loss: stage into a temp table, VALIDATE then move to the real table.

Temp table staging

A common ETL pattern: INSERT into a heap temp table, then INSERT-SELECT into the real table:

Staging is slower for simple inserts because you're writing data twice. It's useful when you need to validate, deduplicate, or transform data before loading — not for raw throughput.

The one thing to remember

Table design dominates insert speed once you have more than one non-clustered index. Five indexes slows inserts 6-11x — far more than any client-side strategy change can recover. If your bulk load is slow, drop indexes first, retry; then look at the client.

Most other table-design knobs save or cost 20-50% each. The exceptions worth remembering: server-side encryption (4x slower), XML columns (2.3x slower than JSON), AFTER INSERT triggers (2.2x slower), and AUTO_UPDATE_STATISTICS = OFF (50% slower — don't turn it off).

Next: Part 8 — TVP and Plain JDBC covers Table-Valued Parameters, bare-metal JDBC, and the final rankings across all 110+ configurations.