Tag Archives: Column store Index

ByHariharan Rajendran

Data Storage Format in Columnstore Index

Go through my previous article purpose of columnstore index.

Normally SQL Server stores the data in pages in a row based manner for tables (heaps) and B-tree indexes. This is the traditional approach and technically this is called “Row store”.

Column store is something like turning traditional storage model into 90 degrees. In this approach, all the values in single column will be in compressed form. Column store index stores each column in a separate set of disk pages which is different from the traditional storage format.

Let’s see an example,

ID Name Department
1 xyz Production
2 Robert Information Technology
3 Mary Sales
4 John Account

 

Row store
1 xyz Production
2 Robert Information Technology
3 Mary Sales
4 John Account

 

Column Store
1 2 3 4
Xyz Robert Mary John
Production Information Technology Sales Account

Column store index is more compressed than the row store. This can understand by seeing above examples. In row store format we have different data types involved in a single row (ID is Integer, Name is string, Department is string). Row store method trying to compress the different data type fields, but in column store, all the values in a same data type so it is easy to compress the data.

Data Compression percentage of column store is greater than the row store.

 

1