Microsoft SQL Server 2012 Performance Tuning Cookbook

Improving performance by a columnstore index

All the indexes discussed here, so far, were rowstore indexes, which is a type available in SQL Server for long time. But, there is a new index called columnstore index, which was introduced in SQL Server 2012. So, now there are two types of indexes available in SQL Server 2012:

• Rowstore index
• Columnstore index

The rowstore index stores data row(s) in data pages, whereas the columnstore index stores each column in a different data page(s).

For example, if we had one table, tblEmployee, with columns empId, FirstName, and LastName, and an index on all three fields, the logical image of rowstore as well as columnstore, for illustration purposes, would be something like this:

A data page is nothing but an 8-KB ...