Saturday, December 8, 2012

Difference Between Columnstore Index and Rowstore Index

ColumnStore Index is one of the new feature in SQL server 2012, this feature was introduced to reduce the over all disk I/O for data warehousing queries by storing the data in columnar manner.

You may ask what's the difference between column store index and row store index. 

Row Store Index:
In B Tree and heap structure the data gets stored in row wise for example if one table has 5 columns and 6 rows then in one page the data was stored in row wise instead of column wise. In larger table the column data will store in multiple pages so here performance will drop compare to column store index.

Here is the picture how the row store index stores data in pages.

ColumnStore Index:
Where as in column store index the data is stored in columnar manner which means the data of individual column is stored in one page, each individual column from each row is stored together on single page, like individual column has store in individual page so the performance of column store index is higher than the row store index.

Here is the picture how the column store index stores data in a pages.

Basics of Columnstore Index. Basics of row store Index. Columnstore Indexes. Understanding New Column Store Index of SQL Server 2012. Inside the SQL Server 2012 Columnstore Indexes. how Columnstore Indexes work in sql server 2012. sql server 2012 column store index. sql server index computed column. sql server index column order. sql server index bit column. sql server multi column index. sql server index included column. sql server add index to column. sql server index xml column


How does storing the data or the information in the column store increases the performance of the database?
For example if i am querying
Select * from Table1

For this query i believe the performance of Row store will be much better than column store. As here in case of the column store the database have to first combine all the pages to form the table and then display it.

Post a Comment