Very interesting things about updateable column store indexes

After more digging, I’ve found a link on msdn to information about the new system table sys.column_store_row_groups. http://msdn.microsoft.com/en-us/library/dn223749(v=sql.120).aspx

This bit “The hobt_id for delta row groups, or NULL if the row group type is not delta. A delta row group is a read/write row group that is accepting new records. A delta row group has the OPEN status. A delta row group is still in rowstore format and has not been compressed to columnstore format.”

So this sounds like there are still two tables [or row groups], the delta row group which you can read and write into and a 2nd row group which is compressed.

This is the work-around people have been using with the non-updateable column store indexes to make to work like they are updateable, but its happening behind the scenes. 

 

Very very nice.

More interesting things about the Clustered Column store index..

I’ve previously blogged on some interesting things about the clustered column store index or CCSI for short.

I’ve found my CCSI in an internal table.  sys.Column_store_row_groups

Noticed the size_in_byte…NULL

Just for fun, I tried to create a 2nd column store index on the same table. (Non-clustered of course)

So we can’t add a non-clustered column store index (NCCSI) to a table that already has a CCSI.

But can we add two NCCSI to a table? The answer is no, but the UI doesn’t let you get that far.  Its non-selectable.. interesting..

And more digging….

Interesting thing about updateable Clustered Column Store Index

I downloaded SQL Server 2014 CPT1 last night and I’ve started playing with it.

I’m really interested in the updateable clustered column store index, so I thought I would see if it works in the CTP1 (not all features are released in the CTPs)

I created two identical tables, added a Clustered Column store index to one of them, and inserted 15000 rows into both of them.

Guess what, it only bloody worked! Excellent! Happy bunny.

So the next step, how does it work? While looking at the IO for the insert, I stumbled on this..

TestTB_1 has 15,000 rows and TestTB_2 has 0 rows… Wait a second, that’s not right.

No – There are 15,000 records in both tables.

 

Wow – check out the logical reads,physical reads…etc…etc..

All coming back as 0 – I guess the column store engine isnt reporting back in the same way the normal engine is.

This will get fixed at some point, no doubt, but the fact that the column store index was updated is so cool…

I’m going to do some more digging now…

Going to SQL Relay?

Going to SQL Relay? Show some support and impress your friends and colleagues with these two badges.  Not going to SQL Relay? Why not! Go to the SQL Relay web site and find out where your nearest event is.

“I’m speaking at SQL Relay”

<a href="http://www.SQLRelay.co.uk"><img src="http://www.SQLRelay.co.uk/images/SQLRelaySpeaking.png"></a>

“I’m going to SQL Relay”

<a href="http://www.SQLRelay.co.uk"><img src="http://www.SQLRelay.co.uk/images/SQLRelayGoing.png"></a>