What is Columnstore Index

by Amol 1. March 2011 06:20


SQL Server 2011 CTP1 has been relesed and it has introduced new INDEX type called Columnstore. Microsoft SQL Server is the first who described columnstore index. Non of the other database products introduced this concept. This newly introduced index will help dramatically increase query performance as columnstore index stores each column in a separate pages rather than traditional index pages which stores multiple rows.  Traditional index pages will looks like as shown in figure below.   

Figure 1

Columnstore index structure is bit different. Instead of storing entire columns in single page it store each column in separate page. Columnstore index is structure is show in figure below.


Figure 1 and Figure 2 shows the basic difference between index and columnstore index.
In Figure 1 entire rows (all columns) are stored in page. And in Figure 2 column Col1 to Col4 are stored in different pages.

Benifites of Columnstore Index:

1.      As Columnstore Index stored in separate pages, only required pages are fethced from the disk.
2.      Faster query processing.
3.      Frequently accessed columns remains in memory.
4.      Enhanced query optimization and execution features, improves data warehouse query performance by hundreds to thousands of times in some cases

I will try to post the comparision result between traditinal Index and Columnstore index. Meanwhile you can try downloading SQL Server Denali CTP.

Tags: , ,

SQL Server Denali

Tag cloud