11

I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table.

Will there be any change in performance based on the order of columns that I use in the columnstore index? If yes, what is the logic behind it?

15

No, order does not matter. Each column is considered individually.

From the SQL Server team (emphasis added):

Typically you will want to put all the columns in your table into the columnstore index. It does not matter what order you list the columns because a columnstore index does not have a key like a B-tree index does. Internally, the data will be re-ordered automatically to get the best compression.

  • Is there no concept of high density columns should be first in the order and low density columns should be last in the order. Frequently used columns should be first in the order. – Vinay Kumar Chella Apr 2 '12 at 6:36
  • 1
    Not that I am aware of - columnstore indexes don't work anything like traditional B-tree indexes. – Aaron Bertrand Apr 2 '12 at 6:37
  • Ok. I heard in some conference but not finding any source for it. Let's wait for some more comments... – Vinay Kumar Chella Apr 2 '12 at 6:43
2

order doesn't matter, what does matter if that you should include ALL columns from the base table in the columnstore index - more via my previous answer to this question here

0

In SQL Server 2012-2016 you have the concept of Segment elimination (http://social.technet.microsoft.com/wiki/contents/articles/5651.understanding-segment-elimination.aspx) and you actually can force the order of at least 1 column (plus 1 extra inside each of the partition).

Consider the following article that explains data loading for better segment elimination: Data Loading for Better Segment Elimination

0

There is an existing great answer by Aaron from 2012, To expand on that...

With SQL 2014+ you have clustered columnstore indexes available. When creating a clustered columnstore index there are no column selection or order options available. Columnstore Indexes Described

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.