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 ...