why tables with columnstore indexes can’t be updated directly using INSERT, UPDATE, DELETE, and MERGE statements?

Question

Why tables with columnstore indexes can’t be updated directly using INSERT, UPDATE, DELETE, and MERGE statements?

That is to say, what's the side effects if tables with columnstore indexes can be updated directly using INSERT, UPDATE, DELETE, and MERGE statements?

Thank you.

Answer 1

I think a columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored. so for that we cant NSERT, UPDATE, DELETE, and MERGE statements, or bulk load operations.

In the Denali release, tables with columnstore indexes can’t be updated directly using INSERT, UPDATE, DELETE, and MERGE statements, or bulk load operations. To move data into a columnstore table you can switch in a partition, or disable the columnstore index, update the table, and rebuild the index. Columnstore indexes on partitioned tables must be partition-aligned. Most data warehouse customers have a daily load cycle, and treat the data warehouse as read-only during the day, so they’ll almost certainly be able to use columnstore indexes.
You can also create a view that uses UNION ALL to combine a table with a column store index and an updatable table without a columnstore index into one logical table. This view can then be referenced by queries. This allows dynamic insertion of new data into a single logical fact table while still retaining much of the performance benefit of columnstore capability.
All tables that don’t have columnstore indexes remain fully updateable. This allows you to, for example, create a dimension table on the fly and then use it in successive queries by joining it to the column store-structured fact table. This can be useful, for example, when a retail analyst wants to put, say, about 1000 products into a study group, and then run repeated queries for that study group. The IDs of these products can be placed into a study group dimension table. This table can then be joined to the columnstore-structured fact table.
8
Index build times for a columnstore index have been observed to be 2 to 3 times longer than the time to build a clustered B-tree index on the same data, on a pre-release build. Customers will need to accommodate this time difference in their ETL processes. However, these customers typically will no longer need summary aggregates, which can take a lot of time to build, so in fact, ETL time may decrease.

---

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

THANK YOU Manjunath,

so why a columnstore index stores each column in a separate set of disk pages  can't do INSERT, UPDATE, DELETE, and MERGE statements, or bulk load operations?

Answer 3

so why a columnstore index stores each column in a separate set of disk pages

That's one of the tricks that cause a columnstore index to return
results (on some queries) much faster than conventional indexes.

can't do INSERT, UPDATE, DELETE, and MERGE statements, or bulk load operations?

Due to how the columnstore indexes are built (with the data being
divided into 100M-row chunks, with the data for each 100M-row chunk of
each column being compressed in a method specificilly chosen for that
chunk, and with sorting of rows specifically optimized to maximize
compression ratio. I can't even begin to imagine the complexity of how
updates would affect the indexed data.

---

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

---

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Answer 4

Hello Hugo,

I got it ,thank you.

Answer 5

I would like to add that next major release will have updatable column store indexing (as promised by Microsoft on PASS 2012).

Answer 6

I would like to add that next major release will have updatable column store indexing (as promised by Microsoft on PASS 2012).

Is that transactional update, or batch/merge update like SSAS?

Josh

Answer 7

Due to how the columnstore indexes are built (with the data being divided into 100M-row chunks, with the data for each 100M-row chunk of each column being compressed in a method specificilly chosen for that chunk, and with sorting of rows specifically optimized to maximize compression ratio. I can't even begin to imagine the complexity of how updates would affect the indexed data.

Inverted b-tree systems like VSAM and navigational systems like IMS were doing this on mainframes and minis thirty years ago.  Everything old is new again!

Josh