SQL: Bulk reloading of clustered columnstore indexes

I recently posted about rebuilding clustered columnstore indexes when they needed maintenance . One of the questions that a reader asked was:

Which is best in performance and CCI packing for fast query:

• Truncate table, drop CCI, insert data, create CCI.
• Truncate table, insert data and therefore keep CCI.

And of course, that’s a great question. Here are the issues:

If I had to just pick one of those, for best query performance and best CCI packing, the first option is usually better:

Truncate table, drop CCI, insert data, create CCI

That approach lets SQL Server build the clustered columnstore index in bulk, producing well-compressed rowgroups with better segment quality.

When you load into a heap or rowstore first and then create the CCI, SQL Server can:

• sort and organize data more efficiently during index creation
• create mostly compressed rowgroups
• avoid lots of rows sitting in the delta store
• usually achieve better rowgroup density
• often produce better segment elimination, depending on load order
• reduce fragmentation in the columnstore structure

For a full reload of a table, this is commonly the cleanest and fastest end state for analytics queries.

Truncate table, insert data and therefore keep CCI

This can still work well, especially for large bulk inserts, but the result depends heavily on batch size and loading method.

If inserts are not large enough, SQL Server may load rows into the delta store first. Later, the tuple mover compresses them into columnstore rowgroups, but you can end up with:

• open or closed delta rowgroups
• smaller compressed rowgroups
• less optimal compression
• more deleted rows over time if the pattern changes
• potentially poorer query performance

For CCI, ideally you want rowgroups close to the maximum size of about 1,048,576 rows per rowgroup. Smaller rowgroups generally mean poorer compression and less efficient scanning.

Practical recommendation

For a complete refresh / reload of a CCI table:

TRUNCATE TABLE dbo.YourTable;

DROP INDEX CCI_YourTable ON dbo.YourTable;

-- bulk insert / insert data

CREATE CLUSTERED COLUMNSTORE INDEX CCI_YourTable
ON dbo.YourTable;

This is usually best when you reload the whole table, when you can afford the CCI rebuild time, when the table is mainly used for reporting/analytics, and when you want best compressed rowgroup quality.

But there is one important exception: if the table is very large and dropping/recreating the CCI takes too long. In that case, the second option may be acceptable if you load data in large batches.

For direct-to-compressed-columnstore loading, aim for batches of at least 102,400 rows, and preferably much larger. Small inserts are the main problem.

Rule of thumb

2026-08-06