What’s and How’s of Columnstore Indexes

A ColumnStore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored. Row store does exactly as the name suggests – stores rows of data on a page – and column store stores all the data in a column on the same page. These columns are much easier to search – instead of a query searching all the data in an entire row whether the data is relevant or not, column store queries need only to search much lesser number of the columns. This means major increases in search speed and hard drive use.
Column oriented storage is the data storage of choice for data warehouse and business analysis applications. It works well for mostly read-only queries that perform analysis on large data sets. Column oriented storage allows for a high data compression rate and as such it can increase processing speed primarily by reducing the IO needs. SQL Server allows for creating column oriented indexes known as ColumnStore Indexes and thus brings the benefits of this highly efficient BI oriented indexes in the same engine that runs the OLTP workload. With SQL2016, a rowstore table can have one updateable nonclustered columnstore index. Previously, the nonclustered columnstore index was read-only. Columnstore supports index defragmentation by removing deleted rows without the need to explicitly rebuild the index.

How to change data in a Non-Clustered Index (Applies to SQL2012/4):
Basically,  once you create a non-clustered columnstore index on a table, you cannot directly modify the data in that table. A query with INSERT, UPDATE, DELETE, or MERGE will fail and return an error message like this:

Msg 35330, Level 15, State 1, Line 1
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

Disabling, rebuilding the index or dropping and then recreating the index is one probable solution but this can be an expensive process especially in mid of our business day. Another workaround here is to create partitioning and make use of staging tables. This will not require you to disable the columnstore indexes and you’ll still be able to update your data.

How to ignore Column Store Indexes if your query performance takes a hit?
There may be some cases when columnstore index is not ideal and needs to be ignored the same. You can use the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX to ignore the columnstore index. SQL Server Engine will use any other index which is best after ignoring the columnstore index.

Hope this helps!

Happy Learning!
Aman Kharbanda