This blog post will try to respond this question from the perspective of the data loading performance.
For this research I decided to pick 3 distinct scenarios to investigate, which refer to different ways to approach the solution:
– a CCI table with an Identity column
– a CCI table with a Sequence as a default value
– a CCI table without Identity
There’s a pretty substantial performance difference, so this is well worth the read for large columnstore data loads.