See how the columnstore option for "The Aggregator" query compares between Standard Edition and Enterprise-- there's a measurable difference, even on this small test instance.
SQL Server really wants to use the nonclustered columnstore index on our new table, but things get weird pretty fast.
Some drawings and dinosaurs explain why the partitioned index is used differently in our execution plan.
There's a couple of different ways to get the speed of the original query.
Performance for this simple query changes dramatically when you partition the table... and not in a good way.
We can speed up this query. But I'm warning you, this is some of the ugliest TSQL you'll see today.
Why doesn't this query stay in its own lane? It's a terrible driver, it's going into all the partitions.