ColumnStore Index in MS SQL 2012 and Extension In MS SQL 2014

Introduction

In this article we are going to discuss about the ColumnStore Index of Microsoft SQL Server 2012. The feature is developed in MS SQL 2012 first and Modification of it came at MS SQL Server 2014. Here we discuss both of them. Hope it will be interesting and useful.

What Technology the ColumnStore used

ColumnStore indexes are based on xVelocity technology. This is formerly known as VertiPaq. It is an advanced storage and compression technology that originated with PowerPivot and Analysis Services but has been adapted to SQL Server 2012 databases.

For Which Purpose it is Created

ColumnStore Index is specially designed for handling large amount data in FACT table of Sql Server Analytical Services (SSAS) Cube.

Understanding ColumnStore Index

At the heart of this model is the columnar structure that groups data by columns rather than rows. To better understand it lets take a simple example.

First of all we create the Base Table

-- Base Tabhle

IF OBJECT_ID(N'dbo.tbl_EMPDTLS', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPDTLS];

   END

GO

CREATE TABLE [dbo].[tbl_EMPDTLS]

   (

     EMPID    INT         NOT NULL IDENTITY PRIMARY KEY,

     EMPFNAME VARCHAR(50) NOT NULL,

     EMPLNAME VARCHAR(50) NOT NULL,

     EMPGRADE CHAR(1)     NOT NULL

   )

GO     

As the table named dbo.tbl_EMPDTLS contains a primary key on the columns EMPID. So a CLUSTERED INDEX is generated automatically on EMPID columns of the table. So it is a ROW store INDEX. Before understanding the ColumnStore Index we need to understand the ROW Store index first and how it’s works.

In the case of a Row Store Index, data from all the columns of the rows are stored together on the same page.

Now we put the query to retrieve data

SELECT EMPID, EMPGRADE FROM [dbo].[tbl_EMPDTLS];

Now we see what happened when the above query fires.

When the database engine processes the above query, it retrieves all three data pages into memory, fetching the entire table.  Even if most of the columns aren’t needed. Than the requested fetched and show us the result.

Now in case of ColumnStore Index store each column data in separate pages. That is column wise fashion, rather than the traditional Row Store Index, which stores data from all the columns of a row together contiguously (row wise fashion). If we query only selects a few columns of the index, it reads less pages as it needs to read data of selected columns only and improves the performance by minimizing the IO cost.

Creating ColumnStore Index

Creating ColumnStore Index is not a hard job. No extra syntax is needed. June one key word named COLUMNSTORE is needed with old Index syntax.

CREATE NONCLUSTERED COLUMNSTORE

INDEX IX_ClStr_tbl_EMPDTLS

ON [dbo].[tbl_EMPDTLS]

(EMPID, EMPFNAME, EMPLNAME, EMPGRADE);

Limitation

·         A table with a ColumnStore Index cannot be updated.

·         A table can have only one ColumnStore Index and hence you should consider including all         columns or at least all those frequently used columns of the table in the index.

·         A ColumnStore Index can only be non cluster and non unique index; you cannot specify       

      ASC/DESC or INCLUDE clauses.

·         The definition of a ColumnStore Index cannot be changed with the ALTER INDEX   

       command; you need to drop and create the index or disable it then rebuild it.

·         A ColumnStore Index cannot be created on view.

·         A ColumnStore Index cannot be created on table which uses features 

      like Replication, Change Tracking, Change Data Capture and Filestream

What’s New in 2014

A new storage engine in SQL Server 2014 overcomes that limitation.

A Clustered Columnstore Index, allowing the table to operate normally when it comes to DML operations like INSERT, UPDATE and DELETE.

Hope you like it.

Posted By: MR. JOYDEEP DAS