I, Rohit Garg, am working as Consultant in IT Company. I am having an around 5 years of experience in MSSQL server & other Microsoft technologies. I am working as DBA in Microsoft SQL Server from last 5 years in e-Commerce, Telecom, Finance domain. In this tenure, I got a chance of working as Database administrator, Developer and trainer on SQL server 2000 to SQL Server 2012. I am holding Master’s degree in Computer Science along with certification in SQL Server & .Net. I like to learn new things by hand-on experience on regular basis. This journey is so far is delightful & valuable with the addition of wonderful friends.

“An xVelocity memory optimized ColumnStore index, groups and stores data for each column and then joins all the columns to complete the whole index. This differs from traditional indexes which group and store data for each row and then join all the rows to complete the whole index. For some types of queries, the SQL Server query processor can take advantage of the ColumnStore layout to significantly improve query execution times. The explosive growth of data warehousing, decision support and BI applications has generated an urgency to read and process very large data sets quickly and accurately into useful information and knowledge. This growth of data volumes and escalating expectations are challenging to maintain or improve through incremental performance improvements. SQL Server ColumnStore index technology is especially appropriate for typical data warehousing data sets. ColumnStore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries.”

ColumnStore Indexes is completely a new type of indexes introduced with SQL Server 2012. ColumnStore index is totally different in architecture from previous index structure. Regular index are row store that means regular indexes (B+ index structure) stores data on basis of rows but column store indexes sorts data on basis of column. ColumnStore indexes are basically introduced for OLAP (Data warehousing) systems. New Type of data compression also added called ColumnStore Compression.

Benefits of Column store index :-

1) Each page stores data only on basis of column. That give significant improve in performance when fetching selected columns from table

2) One page one column data, increase the chances of high percentage of data compression because of similar data type & data for same column

3) Does not physically store columns in a sorted order. Instead, it stores data to improve compression and performance

2) In SQL Server 2012, As soon as you had created a ColumnStore Index on a table, the underlying table was read only, and no changes to the data were allowed. But SQL Server 2014 allowed to have updatable ColumnStore Index