SQL Server 2012 Columnstore Indexes – Explained

A new feature of Microsoft SQL Server 2012 is the introduction of columnstore indexes. These structures can be used to dramatically increase query performance in your database environment. In order to understand columnstore structures, let’s first consider thy traditional row-based approach. You create a table that (in theory) is composed of millions of rows of data stored in many columns. Let’s say you perform a simply query that only looks at values related to a couple of columns. In a traditional row-based approach, this would require the database engine to scan across all of your rows, reading past the other column data and intentionally ignoring it. Now imagine turning your table on its side. You could perform this same query by only focusing on the items you are querying, regardless of how much other data is involved.

Columnstore indexes can be extremely useful in a data warehouse environment. Since many dimension values are repeated multiple times and have similar values, these columns can easily be compressed and indexed in such a way that performing queries on it can be far more effective than the traditional row-based approach.

This functionality also cannot be used with Change Data Capture or Filestream¬†functionality. However the major drawback of a columnstore index is that it is read-only. This means that if any change occurs in the table that is being indexed, your index values will not update automatically. This means that in order to modify the index, you should drop it and then recreate it. There are a couple of ways of working around this restriction (e.g. partition switching) but we’ll leave that conversation for another day.

Image source: Microsoft Developer Network