Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Duplicate of: stackoverflow.com/q/9971012/877069 –  Nick Chammas May 24 '12 at 19:52
add comment

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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