columnstore index

A columnstore index is a type of data structure that's used to store, manage and retrieve data that is stored in a columnar-style database.

Columnstore indexes work well with read-only queries and large-scale analysis. One of their primary uses is queries for data warehousing. Columnstore indexes come with certain benefits over their row store counterparts; they can achieve higher compression rates and reduce I/O from physical media.

Columnstore indexes come in two varieties -- clustered and non-clustered. Clustered columnstore indexes are updateable and are always the primary storage method for their entire table. They cannot be combined with other indexes and they do not physically store columns in a particular order. Non-clustered columnstore indexes can index subsets of columns. They require extra storage to store a copy of a column in the index and are updated by rebuilding or partitioning the index. Non-clustered columnstores can be combined with other tables and physically store columns in a particular order to optimize compression. 


This was last updated in June 2014

Continue Reading About columnstore index

Dig Deeper on SQL Server Data Warehousing