If you have a nightly load scenario, which is the case for most data warehouses and data marts, then there is no need to worry about the restriction that you can't update a table that has a columnstore index on it. All you need to do is drop the columnstore index, do your updates, then rebuild the index.

The time it takes to create a columnstore index is only a little more than the time it takes to created a clustered B-tree. For many fact tables, a 4-core processor can build a columnstore index on 100 million rows in a few minutes. Larger machines can index billions of rows in half an hour or less in many cases. Columnstore indexes can save time in ETL because for many customers, they can drop all their non-clustered B-tree indexes on fact tables. Not having these B-trees frees up time to build the columnstore.

If the time it takes to rebuild your indexes becomes too long to fit in your load window, consider partitioning your columnstore-indexed fact tables, and switching in partitions containing the new rows to add data. That way you won't have to drop the columnstore indexes to put new data in the fact tables.

Return to main SQL Server columnstore index tuning page.