If you’ve got a regular rowstore table and you need to modify thousands of rows, you can use the fast ordered delete technique to delete rows in batches without hitting the lock escalation threshold. That’s great for rowstore indexes, but…columnstore indexes are different.
To demo this technique, I’m going to use the setup from my Fundamentals of Columnstore class:
An 4-core, 30GB RAM VM with the data & log files on ephemeral (fast) SSD SQL Server 2019 Cumulative Update 11 The Stack Overflow public database as of 2018-06 (about 180GB) The dbo.Votes table – which has 151M rows
I’ll create a