Archive for the ‘SQL 2012’ Category

Non clustered Column store indexes are introduced in SQL 2012, here we are going to discus about structure , limitations and advantages of non clustered column store index based on SQL 2012.

An index is nothing but arrangement of data to reach each rows in easiest way(here are we are not discussing about selectivity and density since the topic is overview on column store index). When column store indexes came into picture once People started thinking about column instead of rows.Either user interested in a single column or all the columns SQL has to load entire row in to memory and show only the requested columns to the user. If the user is only interested in a single column why should system waste time to read entire row.

Let use see how SQL deals with it and how data being stored in case of column store index.

Row store method data storage

column-store-image001

Column store method data storage

column-store-image002

Based on the above pictures you can see in case of column store same column data is stored in single page, this improves the compression (since data is homogeneous) and If you are interested on a single column then SQL search engine has to read only one column, the over head caused by row store method has been eliminated by column store index.

Limitations

  1. The table will become read only if you create a column store index.
  2.  Sparse columns are not supported.
  3. Cannot be created on a view or indexed view.
  4. Cannot be created by using the INCLUDE keyword.
  5. Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms. Sorting would eliminate many of the performance benefits.

Note :  if you are interested to pull all the rows from a table definitely column store index is not going to help you in terms of performance.

column-store-image003

The above screenshot is from SQL 2012 instance running in my PC.  I have created a table and created a non clustered column store index on it. I tried to insert a row after that then I got that error.

Now I suggest you to create one more column store index on the same and see what happens , please do comment the result below.

Note : The post is described column store index based on SQL2012 version, few of the limitations has eliminated in the latest version.

Ref : MSDN more details about column store index.

Advertisements