Question: When is a columnstore index not a columnstore index?

Answer:

When its a rowstore index.

or

When its not been rebuilt.

 

Explanation:

The answer is on MSDN – “The updateable columnstore first inserts new data into an OPEN rowgroup, which is in rowstore format” –> Yes, rowstore, not column store.

MSDN says re-organising the index will put it in columnstore format, but in CTP1 this doesn’t work. You need to rebuild the index, not a problem because we are using developer edition (or enterprise edition).

I’ve blogged here what happens when you insert into a column store index here

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.

Clustered Column store index recap

Last week I blogged on the Clustered Column store index, I’ve been playing with it – its very early days, as its a feature of SQL2014 CTP1.

Just to recap.  I noticed that something wasn’t working as I expected.

The number of rows returned from the report ‘Disk usage by table’ is incorrect, shown by the top results set, the correct number is shown from select count (*) query and the select from sys.partitions.

Also notice on sys.partitions, the compression description.

So how do I get the standard report to show the right data?

Well, restarting the instance doesn’t help.

But what about rebuilding / re-organising the index? Re-organising the index does nothing, but rebuilding the index works!

All the counts are returning the expected number of rows, now everything is alright with the world.