Columnstore index in SQL server 2012

Introduction

Here i am going to introduce new feature of SQL-2012

SQL Server 2012 introduced a new feature Columnstore Index which can be used to improve query performance. It is used in data warehousing.

Description

The property of column storage is the ability to read the values of a particular column of a table without having to read the values of all the other columns. In row-oriented storage this is impossible because the individual column values are physically stored grouped in rows on the pages and reading a page in order to read a column value must fetch the entire page in memory, thus automatically reading all the other columns in the row.

There are two kind of storage in database-Row Store and Column Store

Row Store : Row store stores rows of data on a page

Column Store: Column store stores all the data in a column on the same page

These columns are much easier to search – instead of a query searching all the data in an entire row whether the data is relevant or not, column store queries need only to search much lesser number of the columns. This means major increases in search speed and hard drive use.

Column store indexes are heavily compressed, which translates to even greater memory and faster searches.

Creating a ColumnStore index:

You can create a columnstore index using the following ways:

1. Using syntax for creating columnstore index:

To create a ColumnStoreIndex we need to specify the keyword “COLUMNSTORE” and enter the data as we normally do. Once we add a column store to a table, we cannot delete, insert or update the data – it is READ ONLY.

A columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored.

Syntax:

CREATE NONCLUSTERED COLUMNSTORE INDEX <IndexName> ON <TableName>(Col1,Col2,....Coln

Example

Step 1

Create a table

CREATE TABLE EMPLOYE1 (E_NO(VARCHAR2(20)),E_NAME(VARCHAR2(50)),SAL(NUMERIC(20)));

Step 2

Creating  nonclustered index on the table

CREATE NONCLUSTERED INDEX INDEX_1 ON EMPLOYE1(E_NO.E_NAME,SAL)

2.      You can also create a columnstore index using  Object Explorer in Management Studio

· Expand the tree structure for the table and then right click on the Indexes icon.

· Select New Index and then Nonclustered columnstore index

· Click Add in the wizard and it will give you a list of columns with check boxes.

· You can either choose columns individually or click the box next to Name at the top, which will put checks next to all the columns.

· Click OK.