patternsqlMajor
Does the order of columns in a columnstore index matter?
Viewed 0 times
theordercolumnscolumnstoredoesindexmatter
Problem
I have a table with ~200 million rows and ~15 columns in it. I am planning to create a
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?
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#15930, answer score: 22
Revisions (0)
No revisions yet.