Columnstore Index Restrictions

Although columnstore indexes work with the majority of the data types, components, and features found in SQL Server 2012, columnstore indexes have the following restrictions and cannot be leveraged in the following situations:

• You can enable PAGE or ROW compression on the base table, but you cannot enable PAGE or ROW compression on the columnstore index.

• Tables and columns cannot participate in a replication topology.

• Tables and columns using Change Data Capture are unable to participate in a columnstore index.

• Create Index: You cannot create a columnstore index on the following data types:

      o decimal greater than 18 digits
      o binary and varbinary
      o BLOB
      o CLR
      o (n)varchar(max)
      o datetimeoffset with precision greater than 2

• Table Maintenance: If a columnstore index exists, you can read the table but you cannot directly update it. This is because columnstore indexes are designed for data-warehouse workloads that are typically read based. Rest assured that there is no need to agonize. The upcoming “Columnstore Index Design Considerations and Loading Data” section articulates strategies on how to load new data when using columnstore indexes.

• Process Queries: You can process all read-only T-SQL queries using the columnstore index, but because batch processing works only with certain operators, you will see that some queries are accelerated more than others.

• A column that contains filestream data cannot participate in a columnstore index.

• INSERT, UPDATE, DELETE, and MERGE statements are not allowed on tables using columnstore indexes.

• More than 1024 columns are not supported when creating a columnstore index.

• Only nonclustered columnstore indexes are allowed. Filtered columnstore indexes are not allowed.

• Computed and sparse columns cannot be part of a columnstore index.

• A columnstore index cannot be created on an indexed view.