locked
Columnar index on Table makes it read-only

    Question

  • Hi,
    Why a table become read-only if it has a columnar index on it?

    Thanks!
    Rakesh
    Tuesday, December 20, 2011 5:11 AM

Answers

  • Because the sky is blue.

    No, seriously it is a restriction. And not entirely illogical. A columnstore index is quite different from a regular index, which essentially is just a materialised view of the underlying table. If you update an indexed column, the index has to be updated as well, but that's not really a big deal. And if update several columns in the index, all the values are in the same place.

    This is different in a column-store index, where each value on a row is on a different page. And it is not that every cell has a pointer back to the original data page. On top of that the data is compressed, so for an update, the data would have to be inflated and then recompressed. Keep in mind that the compression is one of the key properties to make the columnstore index such a speed booster.

    But it cannot be denied, that the readonly propery limits the usability of the feature to certain data-warehouse scenarios.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Rakesh Mishra Wednesday, December 21, 2011 4:17 AM
    Tuesday, December 20, 2011 10:26 PM

All replies

  • Because the sky is blue.

    No, seriously it is a restriction. And not entirely illogical. A columnstore index is quite different from a regular index, which essentially is just a materialised view of the underlying table. If you update an indexed column, the index has to be updated as well, but that's not really a big deal. And if update several columns in the index, all the values are in the same place.

    This is different in a column-store index, where each value on a row is on a different page. And it is not that every cell has a pointer back to the original data page. On top of that the data is compressed, so for an update, the data would have to be inflated and then recompressed. Keep in mind that the compression is one of the key properties to make the columnstore index such a speed booster.

    But it cannot be denied, that the readonly propery limits the usability of the feature to certain data-warehouse scenarios.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Rakesh Mishra Wednesday, December 21, 2011 4:17 AM
    Tuesday, December 20, 2011 10:26 PM
  • Thanks for the reply but I think or I would like to have feature where we have an option not compress the data (still storing them into per column per page wise) so that we can do the DML operations.

     

    Thanks!
    Rakesh

     

    Wednesday, December 21, 2011 4:17 AM
  • Thanks for the reply but I think or I would like to have feature where we have an option not compress the data (still storing them into per column per page wise) so that we can do the DML operations.

    I am not sure how much that would pay off, but there is always
    http://connect.microsoft.com/sqlserver/feedback


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 21, 2011 10:37 PM
  • Another option that I am thinking is, we should have an option similar to Full Text Index so that we can build indexes on schedule basis and by doing so we can make the table updatable.
    Thursday, December 22, 2011 2:12 AM