SQL SERVER – ColumnStore Indexes Without Aggregation

During the recent Comprehensive Database Performance Health Check, I noticed there were lots of aggregation going on with various data in the table. I suggested that the client can experiment with the columnstore indexes and it may help the performance. When we attempted the option of using the index, we noticed a significant difference.

Now during the query execution, the DBA team really appreciated the performance of the query due to index. However, they were not sure if the column store index is a good idea if some of the queries were not using the aggregation. Essentially the question was – Do columnstore index really help regular query which is not using aggregations.

The answer to this question is – Absolutely Yes! 

Here is the query which I had run on the WideWorldImporters database. You may notice that there is no aggregation on the query. The table which I have used in the query is Sales.SalesOrderDetail. The table already contained one Column Store Index created on it.

Here is the query:

SELECT StockItemID, OrderID, OrderLineID
FROM Sales.OrderLines
WHERE StockItemID > 1 AND StockItemID < 999
GO

When you run the above query, it gives the following execution plan:

You may notice that the execution plan is using the Batch Mode and columnstore Index. I think it is a myth that columnstore index is only used during the aggregation operation.

Here are a few blog posts on the same subject which you may find interesting:

• SQL SERVER – ColumnStore Frequently Asked Queries
• How to List All ColumnStore Index with Table Name in SQL Server? – Interview Question of the Week #153 
• SQL SERVER – Columnstore Index Cannot be Created When Computed Columns Exist
• SQL SERVER – 2016 – Creating Additional Indexes with Clustered ColumnStore Indexes
• SQL SERVER – Fix Error Msg 35336 Working with ColumnStore Indexes
• SQL SERVER – COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression

Let me know if you have any comments about the blog post. Please leave a comment. On a separate note here is the link to my Linkedin profile.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.