Joe Obbish and Erik Darling tag team on this one. First, Joe looks at the details of what the CCI ordering process does:
The sort for inserting into an ordered columnstore is a DML request sort. It appears to use the same internal mechanism as the sort that’s added for inserting into partitioned columnstore tables. The difference is that the data is sorted by the specified columns instead of a calculated partition id. In my testing, the sort appears to be a best effort sort that does not spill to tempdb. This means that if SQL Server thinks there won’t be enough memory then the data will not be fully sorted. Parallel inserts have an additional complication.
And Erik has a messy work-around:
Anyway, I decided to dig in and see what was going on behind the scenes. Which of course, means query plans, and bothering people who are really good at debuggers.
Most of the problems that you’ll run into in SQL Server will come from sorting data.
Whenever I have to think about Sorts, I head to this post about all the different Sorts you might see in a query plan.
Definitely read both posts.