Clustered Columnstore Indexes – part 7 (“Transaction Isolation”)

Continuation from the previous 6 parts, starting from http://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/

In this part we shall be dedicating to the Transactional support for Clustered Columnstore Indexes:

Isolation Levels:
As I have already mentioned in part 2, Isolation support for Clustered Columnstore Indexes is quite impressive, almost every Isolation Level of SQL Server is supported: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ & SERIALIZABLE are all in. READ_COMMITED_SNAPSHOT is supported as well.

Lets try to verify the announced support for all of those isolation levels in the next script, which is inserting 50.000 rows using each of the supported isolation levels:

This script works without any problem.
Lets check now on the READ_COMMITED_SNAPSHOT isolation level, which is set on the database level:

Works fine 🙂 All 250.000 rows are successfully inserted into dbo.CCTest table and we are having just one Row Group – a Delta Store which is still open.

And what about the unsupported SNAPSHOT isolation level?

Msg 35371, Level 16, State 1, Line 120
SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.

Ok, confirmed that it is not supported. I can only guess the reasons behind it, and as soon as find them – this post will get updated.

To be updated. 🙂

to be continued with Clustered Columnstore Indexes – part 8 (“Locking”)

3 thoughts on “Clustered Columnstore Indexes – part 7 (“Transaction Isolation”)

  1. Pingback: Clustered Columnstore Indexes – part 1 (“Intro”) | Nikoport

  2. Richard Lee

    Hi,

    great posts, these are really helpful. I was interested to know why SNAPSHOT wouldn’t work and tried to have a play around. The steps below were on a test instance and my conclusion is based totally on conjecture and I never normally alter system databases in production, but thought I’d share my findings.

    I think maybe that SNAPSHOT isolation is not supported because it stores the updated rows in tempdb. I executed the sproc you wrote about in a previous post

    exec [sys].[sp_db_enable_clustered_columnstores]
    @dbname = ‘tempdb’, @value = ‘on’;

    and it returned with

    “The clustered_columnstores setting changed from 0 to 1.”

    So I tried again with SNAPSHOT enabled and it still failed. I tried to turn the columnstore support off for tempdb and i got the error below:

    exec [sys].[sp_db_enable_clustered_columnstores]
    @dbname = ‘tempdb’, @value = ‘off’;

    Msg 35366, Level 16, State 3, Procedure sp_db_enable_clustered_columnstores, Line 165
    Cannot disable clustered columnstore index support in database ‘tempdb’ because it is a system database. Make sure to target a user database. If you want temporary clustered columnstore tables, consider creating a regular user database for them.

    So it MIGHT be something to do with tempdb not supporting columnstore indexes…

    1. Niko Neugebauer Post author

      Hi Richard,

      thank you for the comment!
      I personally think the lack of the support has to do with the amount of work and the priorities of the Development Team.
      Of course it means that we won’t have support for Readable Secondaries in SQL Server 2014 (Availability Groups), but hey – there is always a hope for the next version after SQL Server 2014. 😉

      Also, I would not hold use sp_db_enable_clustered_columnstores as an indication for anything.

Leave a Reply

Your email address will not be published. Required fields are marked *