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 ...
Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.