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 page that stores data. If you have 10 rows and the total size of those 10 rows is 16 KB, then each row should consume 2 data pages, in case of a rowstore index.

The columnstore index doesn't contain a whole row, but the data of one column only...