Columnstore Index

Introduced in SQL Server 2012, the columnstore index is used to index information by columns rather than by rows. This is especially useful when working within data warehousing systems where very large amounts of data have to be aggregated and accessed quickly. The information stored within a columnstore index is grouped on each column, and these groupings are stored individually. This makes aggregations on different sets of columns extremely fast since the columnstore index can be accessed rather than accessing large numbers of rows in order to aggregate the information and because the columnar data is stored compressed. There are a number of limits on using columnstore indexes:

·         Tables that have a columns store index cannot be updated. You must first drop the columnstore index or you must partition around the columnstore index in order to support changes to the underlying data. 

·         You can’t use certain data types such as binary, text, varchar (max), uniqueidentifier, clr data types, xml, or decimal with a precision greater than 18.

·         You can’t create a columnstore index on a sparse column.

A columnstore index has two operations, batch mode and row mode. Of the two, batch mode is much faster. It takes advantage of a special method of accessing the data that allows for pulling large sets of data, in batches, so that decompression isn’t necessary. The batch mode processing also has extra benefits when working with multiprocessor machines. To see if a columnstore ran in batch or row mode, you just have to check the properties of the columnstore operation.

Columnstore indexes don’t require you to have the columns in a particular order, unlike clustered and nonclustered indexes. Also, unlike these other indexes, you should place multiple columns within a columnstore index so that you get benefits across those columns. But if you’re retrieving large numbers of columns from a columnstore index, you might see some performance degradation. While the limitations for the use of the columnstore index are stringent, the benefits for structures that can operate within those limitations are very clear.