Load Into a Heap or a Clustered Table: Which Bulk Load Wins

You can load into a heap first and build the clustered index afterward, but that moves the work, it does not remove it. The load looks fast. The index build then pays the bill.

The shortcut everyone has heard about

Somewhere in every team there is a person who says, “Drop the indexes, load into a heap, then build the indexes. It is much faster.” They are not wrong about the first step. A heap has no tree to maintain, so rows go in quickly.

The trouble starts when the stopwatch stops too early. Your nightly load is not finished when the insert ends. It is finished when your readers can query the table the way they need to. For a clustered table, that includes the index.

So let me time the whole pipeline both ways. I will load 500,000 rows into two tables and count everything that happens until each table has its clustered index.

Set up two identical targets

The first table starts as a heap. The second has its clustered primary key from the start. A small temp table collects the time and the log space used by each step. Run this in a test database, not in production.

DROP TABLE IF EXISTS dbo.LoadHeap;
DROP TABLE IF EXISTS dbo.LoadClustered;
DROP TABLE IF EXISTS #Stages;

CREATE TABLE dbo.LoadHeap
    (Id int NOT NULL, Payload char(100) NOT NULL);

CREATE TABLE dbo.LoadClustered
    (Id int NOT NULL PRIMARY KEY CLUSTERED, Payload char(100) NOT NULL);

CREATE TABLE #Stages
    (Pipeline nvarchar(30), Step tinyint, Stage nvarchar(30), Ms int, LogKB bigint);

SELECT name, recovery_model_desc
FROM sys.databases
WHERE database_id = DB_ID();

The last query shows the recovery model, because logging depends on it. On my server it says FULL. The rules for minimal logging depend on that setting, so check yours.

Pipeline one: heap first, index second

Each step runs inside a transaction, so we can read the log bytes it used before the commit. The first step loads the heap. The second step builds the unique clustered index.

DECLARE @t datetime2 = SYSDATETIME(), @kb bigint;

BEGIN TRANSACTION;
INSERT dbo.LoadHeap WITH (TABLOCK) (Id, Payload)
SELECT value, 'payload' FROM GENERATE_SERIES(1, 500000);
SELECT @kb = database_transaction_log_bytes_used / 1024
FROM sys.dm_tran_database_transactions
WHERE transaction_id = CURRENT_TRANSACTION_ID() AND database_id = DB_ID();
COMMIT;
INSERT #Stages VALUES (N'Heap, then index', 1, N'Load rows', DATEDIFF(MILLISECOND, @t, SYSDATETIME()), @kb);

SET @t = SYSDATETIME();
BEGIN TRANSACTION;
CREATE UNIQUE CLUSTERED INDEX CX_LoadHeap ON dbo.LoadHeap (Id);
SELECT @kb = database_transaction_log_bytes_used / 1024
FROM sys.dm_tran_database_transactions
WHERE transaction_id = CURRENT_TRANSACTION_ID() AND database_id = DB_ID();
COMMIT;
INSERT #Stages VALUES (N'Heap, then index', 2, N'Build index', DATEDIFF(MILLISECOND, @t, SYSDATETIME()), @kb);

Pipeline two: clustered from the start

Now the same rows go into the table that already has its clustered key. The generated Id values arrive in key order, which is the friendly case for a clustered load.

DECLARE @t datetime2 = SYSDATETIME(), @kb bigint;

BEGIN TRANSACTION;
INSERT dbo.LoadClustered WITH (TABLOCK) (Id, Payload)
SELECT value, 'payload' FROM GENERATE_SERIES(1, 500000);
SELECT @kb = database_transaction_log_bytes_used / 1024
FROM sys.dm_tran_database_transactions
WHERE transaction_id = CURRENT_TRANSACTION_ID() AND database_id = DB_ID();
COMMIT;
INSERT #Stages VALUES (N'Clustered at load', 1, N'Load rows', DATEDIFF(MILLISECOND, @t, SYSDATETIME()), @kb);

Compare the finish lines

First, confirm both tables ended up equal: same rows, same key range, a clustered index each. Then read the stages and the totals.

SELECT N'Heap, then index' AS Pipeline, COUNT_BIG(*) AS RowsLoaded, MIN(Id) AS FirstId, MAX(Id) AS LastId
FROM dbo.LoadHeap
UNION ALL
SELECT N'Clustered at load', COUNT_BIG(*), MIN(Id), MAX(Id)
FROM dbo.LoadClustered
ORDER BY Pipeline;

SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName, type_desc
FROM sys.indexes
WHERE object_id IN (OBJECT_ID(N'dbo.LoadHeap'), OBJECT_ID(N'dbo.LoadClustered'))
ORDER BY TableName;

SELECT Pipeline, Step, Stage, Ms, LogKB FROM #Stages ORDER BY Pipeline, Step;

SELECT Pipeline, SUM(Ms) AS TotalMs, SUM(LogKB) AS TotalLogKB
FROM #Stages
GROUP BY Pipeline
ORDER BY Pipeline;

Both tables hold 500,000 rows, with Id from 1 to 500000. Both end with a clustered index. So the final result is the same, and only the road differs.

Your times will differ from mine, so look at the shape. The heap load and the clustered load took a similar time and used almost the same log. The index build on the heap then added more time, and about as much log again. Add the two heap steps together, and the heap pipeline cost clearly more than the clustered one.

What this does and does not prove

This is one table, one key, sorted input, and a fast test machine. Please do not turn it into a rule. A heap first can still make sense. A load with many nonclustered indexes, for example, is a different race. Measure your own shape.

Also, be careful with the words “minimally logged”. TABLOCK helps, but it is not a promise. In this FULL recovery demo, the log use shows the load was fully logged. Check the log numbers on your own server before you claim otherwise.

Finally, count the whole pipeline: load, index, constraints and validation. Then clean up the demo tables.

DROP TABLE IF EXISTS dbo.LoadHeap;
DROP TABLE IF EXISTS dbo.LoadClustered;
DROP TABLE IF EXISTS #Stages;

Next time someone promises a faster load, ask them where they stopped the stopwatch.

A fast heap insert is not a finished load, it is the first stage of the pipeline.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.