Techbubbles

Columnstore index in SQL Server 2012

 

Microsoft SQL Server 2012 introduces a new Index type called Columnstore and it offers great performance for data-warehousing loads and other similar queries. Columnstore indexes are designed for data-ware house workloads that are typically read based. This post discuss about how Columnstore index work, how the data is stored in it and what type of queries can benefit from a column store index. It also discusses how to create Columnstore index in SQL Server 2012.

Columnstore Architecture

Traditional indexes (B-trees) in SQL Server stores the data in row based, this model referred as row store. In Columnstore Index all the values from single column are stored contiguously in compressed form. Columnstore index stores each column in separate set of disk page.

Let us take an example to explain the difference with traditional indexes(B-trees)

Employee table which includes ID, Name, City and State columns.

image

Traditional Indexes stores the data in below format

image

Columnstore index stores the data on disk page in below format

image

The major difference is Columnstore index groups and stores data for each column and then joins all the columns to complete the whole index.

Let’s see how this storage model significantly increases the query performance and speed up the retrieval of data.

1. Higher level of compression can be achieved compared to data organised across rows. Columnstore uses Vertipaq compression algorithm. When data is compressed, queries require less IO because the amount of data transferred from disk to memory is significantly reduced.

2. When user runs a query using a Columnstore index, SQL Server fetches data only for columns that required for the query

image  

DataTypes supported by columnstore indexes

  • Char and Varchar
  • All integer types(int, bigint, smallint and tinyint)
  • real and float
  • string, money and all date time types

Note: Only one columnstore index can be created per table

If a columnstore index exists, you can read the table but can not update it. INSERT,UPDATE, DELETE and MERGE statements are not allowed on tables using columnstore indexes.

Creating a Columnstore Index

Creating a Columnstore index is similar to creating a traditional SQL SERVER indexes.

All Columnstore indexes must be non-clustered

1. Use SQL Server Management Studio and connect to the SQL Server instance where you want to create the index

2. In Object explorer , Expand database, and expand table in which you would like to create a new Columnstore-Index.

3. Right click on the index folder and choose new index and then click non-clustered columnstore index.

4. Type the name for index and click ok

image 

5. In select columns dialogue box, select the columns to be participate in columnstore index and then click ok.

You can also use the below T-SQL to create the columnstore index

CREATE [ NONCLUSTRED ] COLUMNSTORE INDEX index_name ON (column [,...n])

You can force the query to use a columnstore index using WITH statement

example

SELECT DISTINCT (EMPID) FROM dbo.Employee WITH (Non-ClusteredIndexEMPID )

Reference content and pictures from http://rossmistry.com/ and http://blog.datainspirations.com/


Share this post :


Related Posts:

%d bloggers like this: