ColumnStore Indexes (Run By Microsoft’s VertiPaq technology) as described by BOL (http://msdn.microsoft.com/en-IN/library/gg492088(v=sql.110).aspx) :

“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

4) ColumnStore Index by default compressed data by New ColumnStore Compression

Restriction of Column store index :-

1) Cluster ColumnStore index cannot be combined with other index types, you can use Non-Cluster index to combine it with other indexes

2) Only Clustered Column index are updatable on the other side Non-Clustered ColumnStore index are read only

3) Non-Clustered CloumnStore Index requires extra storage space for column copy in index

4) Majorly introduced to get performance benefit in OLAP systems, but if required you can use it in OLTP

New Enhancements with SQL 2014 :-

1) SQL Server 2014 supports Clustered ColumnStore Index whereas SQL Server 2012 supports only Non-Clustered ColumnStore Indexes

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

3) Deltastore used with clustered ColumnStore indexes only, to gain performance.

a. Deltastore is a container or storage for rows before it moves to ColumnStore index. Deltastore introduced to improve performance for DML & select operations.

b. Bulk operation having minimum 102,400 rows goes to ColumnStore directly & operation having rows less than 102,400 rows will be moved to deltastore before it moves to ColumnStore

c. When the deltastore reaches the maximum number of rows, it becomes closed. A tuple-move process checks for closed row groups & moves them to ColumnStore

4) Features not compatible with ColumnStore indexes :-

a. Page and row compression

b. Replication

c. Change tracking

d. Change data capture

e. Filestream

Datatype not supported :-

1) binary and varbinary

2) ntext , text, and image

3) varchar(max) and nvarchar(max)

4) uniqueidentifier

5) rowversion (and timestamp)

6) sql_variant

7) decimal (and numeric) with precision greater than 18 digits

8) datetimeoffset with scale greater than 2

9) CLR types (hierarchyid and spatial types)

10) xml

Rebuild ColumnStore index :-

Rebuilding ColumnStore index having not different syntax that regular index Rebuild syntax.

ALTER INDEX <COLUMNSTORE INDEX NAME> ON <TABLE NAME> REBUILD

How to create ColumnStore Index? :-

Creating column store index having not different syntax that regular index creation syntax. You just need to mention keyword COLUMNSSTORE to create column store index.

o Create Cluster ColumnStore Index :-

CREATE CLUSTERED COLUMNSTORE INDEX <CLUSTER COLUMNSTORE INDDEX NAME> ON <TABLE NAME>

Note : No need to mention columns for index, all columns ill be automatically included in columnstore index.

o Create Cluster Non-ColumnStore Index :-

CREATE NONCLUSTERED COLUMNSTORE INDEX <NONCLUSTER COLUMNSTORE INDDEX NAME> ON <TABLE NAME> (<COL1>,<COL2>,…)

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)