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.