SQL Server Columnstore Indexes


In this section we will look at columnstore indexes and how these indexes can improve performance of your queries.

What is a columnstore index?

A column store index is an index that was designed mainly for improving the query performance for workloads with very large amounts of data (eg. data warehouse fact tables).  This type of index stores the index data in a column based format rather than row based as is done with traditional indexes. 

Why use a columnstore index?

There are two main benefits of column store indexes.  First they reduced storage costs.  Column store indexes provide a very high level of compression, up to 10x, due to the fact that the data across columns is usually very similar and will compress quite well.  Second is better performance.  This benefit is multi-faceted.  With a smaller index footprint, due to the compression, we reduce the amount of IO we have to perform.  Also because of this small footprint we can fit more of the index into memory which helps to speed up processing.  Finally queries often only query a few columns from the table.  Since the data is stored in a column based format this also reduces the amount of IO that needs to be performed.

How to create a columnstore index?

Creating a column store index is done by using the CREATE COLUMNSTORE INDEX command and has many of the same options as a regular index. 

The below TSQL create a simple column store index with all the defaults.

CREATE COLUMNSTORE INDEX IX_SalesOrderDetail_ProductIDOrderQty_ColumnStore
ON Sales.SalesOrderDetail (ProductId,OrderQty);

Confirm Index Usage

Once the index is created we can write a basic aggregation query similar to what you would see in any data warehouse environment.  This TSQL will simply sum the order quantity across each product.

SELECT ProductID,SUM(OrderQty)
FROM Sales.SalesOrderDetail 
GROUP BY ProductId;

If we look at the EXPLAIN plan we can see that the entire query was satified by scanning the column store index.  No table access was even required.

query plan

Comments For This Article

Saturday, May 23, 2020 - 3:45:40 AM - dbaAlek Back To Top (85735)

Ben's comment hits target.

Wednesday, May 29, 2019 - 3:26:14 AM - Vasant Back To Top (81244)

Hi Ben,

Every article explains about the pros of indexes. Please bring out the cons of the uses of indexes, so that we are guarded against the drawabacks. This will also help us understand what scenario to test before adding a new index in the test environment and thereby take an informed decision. 

Regards, Vasant

get free sql tips
agree to terms