14

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?

Improve this question
1

5 Answers 5

Reset to default
22

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.

Improve this answer
3
  • 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, 2012 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, 2012 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, 2012 at 6:43
3

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

Improve this answer
1
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

Improve this answer
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

Improve this answer
-1

Ordering is significant for columnstore indexes on large tables since it helps to ensure good segment elimination. (See the following for a good example: https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/hands-on-with-columnstore-indexes-part-2-best-practices-and-guidelines/)

Unfortunately, the tedious process to coerce and maintain ordering involves the following: (a) add a clustered index first (and not a PK-clustered one otherwise you will get an error), (b) replace the clustered index using WITH (MAXDOP = 1, DROP_EXISTING = ON), and (c) append the table with new data in the same order pattern.

Improve this answer
5
  • 2
    The question is not about ordering data, but about the order in which the columns are listed in the index definition.
    – mustaccio
    May 27, 2022 at 18:12
  • 1
    The question is, "Will there be any change in performance based on the order of columns that I use in the columnstore index?" And my answer is still yes for a large table considering data load and sort patterns. On a table with hundreds of billions of records, I get orders of magnitude higher segment elimination and better performance using coerced data order, which is actualized through column order on the initial (but replaced) clustered index and through subsequent data appends that continue with the same clustering order.
    – Kirk Vukonich
    May 29, 2022 at 0:20
  • And now in SQL Server 2022, the MS team has made it even easier to coerce order for clustered columnstore indexes by introducing the ORDER property! learn.microsoft.com/en-us/sql/t-sql/statements/…
    – Kirk Vukonich
    Sep 15, 2022 at 23:23
  • @KirkVukonich you are talking about the order of the rows, the question is about the order of the columns.
    – Michel de Ruiter
    Nov 6 at 16:26
  • I was referring to the ordering of columns within the columnstore index data structure, which impacts performance. It was not possible to explicitly dictate this prior to 2022, but there was a way to implicitly dictate it. Here is MS's nearly same verbiage on their docs for the 2022 feature update: "Use the column_store_order_ordinal column in sys.index_columns to determine the order of the column(s) for a clustered columnstore index." [learn.microsoft.com/en-us/sql/t-sql/statements/…
    – Kirk Vukonich
    Nov 8 at 0:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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