Back To Blog

SQL Server Interview question: - Column store indexes.

By Shiv Prasad Koirala in SQL
Aug 18th, 2012
15918
0

With SQL Server 2012 coming interviewers are asking questions around those new features. In this section we will try to answer some important SQL Server interview question's around the most discussed feature "Column store indexes" from SQL Server 2012 interview perspective.

Buy my SQL Server interview question book from flipkart.

What are column store indexes?

Relational database store data "row wise". These rows are further stored in 8 KB page size. For instance you can see in the below figure we have table with two columns "Column1" and "Column2". You can see how the data is stored in two pages i.e. "page1" and "page2". "Page1" has two rows and "page2" also has two rows.

Now if you want to fetch only "column1", you have to pull records from two pages i.e. "Page1" and "Page2", see below for the visuals.

As we have to fetch data from two pages its bit performance intensive.

sql

If somehow we can store data column wise we can avoid fetching data from multiple pages. That's what column store indexes do. When you create a column store index it stores same column data in the same page.

You can see from the below visuals, we now need to fetch "column1" data only from one page rather than querying multiple pages.

sql

What are the other benefits of column stored indexes?

There are two benefits of column store indexes:-

Increases select performance (already discussed in the previous question).
Efficient compression due repeating and similar data.

Let's discuss the second point. When data is stored row wise it's not ideal for compressing data as you get disparate data from various columns. When you create column store indexes compression algorithm exploits repeating/ similar data and makes compression more efficient. You can expect 50% or more benefit in space saving when you use column store indexes.

How can we create column store indexes?

To create column store indexes, expand the table, right click on the indexes folder and click on "Non-Clustered column stored index". Once you click on this menu you can specify the columns on which column store indexes can be applied. See the below figure for visuals.

sql

Are there any limitations of Column store indexes?

Column stored indexes was mainly created for OLAP applications to increase the select performance. Due to this focused implementation below are some the important limitations:-

Insert, update and delete SQL commands do not work on table which have column store indexes.
Column store indexes do not support the following data types :-