rebuilding clustered columnstore index

Question

If i'm inserting into a clustered columnstore index each hour, do I have to rebuild the index each hour as well so the new data gets indexed? Or does it automatically get indexed when inserting like traditional indexes?

Answer 1

any ideas on this??

Answer 2

no one?

Answer 3

has anyone tried this out in the new CTP2 release?

Answer 4

You do not need to rebuild the index. The columnstore works like any other index and keeps up to date. It does keep the inserts in a separate pile until it has enough to compress and make a new page. You might need to rebuild indexes periodically (and more frequently than a rowstore index) to improve performance.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 5

Add to Rick's answer

The data inserted is always indexed. The newly inserted data is first inserted into a row-store (called delta store) and this store is part of CCI. When the number of inserted rows cross a threshold (typically 1 million), it is moved to compressed state by a background thread. As Rick indicates, this happens automatically.

The case of rebuild of an index is a bit more involved. Over time, due to deletes and updates, the data gets fragmented and the rebuild of index can be used to "defrag" the data. I recommend that you play with column_store catalog views. They provide very useful info

thanks
Sunil