How Data is Stored when using a Columnstore Index in SQL Server 2012

We know that in traditional tables (heap) and indexes (B-tree), SQL stores data in pages which is in a row based style. This type of storage model known as a row store. But in Columnstore index, it is like turning the traditional storage model to 90 degrees, where all the values from a single column are stored continuously in a compressed form. The Columnstore index stores each column in a separate set of disk pages rather than storing multiple rows per page, which has been the traditional storage format. With the help of an example, we will see the differences.

TABLE 1: Traditional Table Containing Employee Data

EmployeeID	EmpName	EmpCity	EmpState
1	John	New York	NY
2	Mary	New Jersey	NJ
3	Simon	New York	NY
4	Joy	New York	NY
5	Ronald	New Jersey	NJ

Database administrator can organize above data either traditionally or Columnstore index.

TABLE 2: Employee Data Stored in a Traditional i.e “Row Store” Format

Row Store

1 John New York NY
2 Mary New Jersey NJ
3 Simon New York NY
4 Joy New York NY
5 Ronald New Jersey NJ

TABLE 3 Employee Data Stored in the New i.e. Columnstore Format

Columnstore

1 2 3 4 5

John Mary Simon Joy Ronald

New York New Jersey New York New York New Jersey

NY NJ NY NY NJ

It is clear that the major difference between the Columnstore format in Table 3 and the row

store method in Table 2 is that a Columnstore index groups and stores data for each column and

then joins all the columns to complete the whole index, whereas a traditional index groups and

stores data for each row and then joins all the rows to complete the whole index.