Column Store Index Life Cycle

In my series on the Column Store Index, I thought I would blog on what happens when you insert/ delete into a column store index.

1) Lets insert some rows into a table with a column store index.

2) Rebuild the Index. The state of index changes to Compressed from Open and size_in_bytes is populated.

3) Insert some more rows. We now have two rows, one ‘OPEN’ and second ‘COMPRESSED’

4) Delete some rows. The deleted rows appear to have come from the ‘COMPRESSED’ row.

5) Rebuild the index again.

 

6) When I updated 1 row in the COMPRESSED table, the engine deleted the row and added a new row in the OPEN / delta table.

 

The page on MSDN about the sys.column_store_row_groups system table  http://msdn.microsoft.com/en-us/library/dn223749(v=sql.120).aspx , explains why the 2nd table / delta table or delta row set is used , how and when data is moved from open of the threee states; ‘OPEN’,’COMPRESSED' and ‘CLOSED’ – I’ve not seen ‘CLOSED’ yet.