ColumnStore index is a new type of index available in SQL 2012 and has been enhanced in SQL 2014 version. ColumnStore index stores the data column wise instead of a regular row based B tree format and hence, it has better ability to compresses the data. When it was first released in SQL 2012, it has several limitations and some of those have been addressed in later versions of SQL(SQL 2014).
Quick pointers on limitations on ColumnStore Indexes in SQL 2012 and 2014:
- NonClustered columnstore does not require the table to have a clustered index.
- In SQL 2014, Clustered ColumnStore index is the only index that can exist on the table.
- In SQL 2014, Clustered ColumnStore Index is updateable. Data can be inserted,updated and deleted from the table.
- In SQL 2012/14 – NonClustered ColumnStore Index are NOT updateable. One solution is, to disable the columnstore Index and do the data load and then re enable the columnstore index.
- Varchar(max),nvarchar(max),xml, ntext,text,image etc are not suitable datatypes for Columnstore index.
- Foreign Key, Unique constraints cannot be defined.
- In SQL 2012, there is no clustered columnstore option only NonClustered ColumnStore index.
- In SQL 2014, we can create clustered columnstore or NonClustered ColumnStore index. However, if the table has clustered columnstore index, we cannot create any other index.
I will add more points as I come across them.