Update COLUMNSTORE index in DB transaction

Question

Is it possible to update a COLUMNSTORE index in a DB transaction? I would like to use the following SQL command inside transaction:

ALTER INDEX [IX_Name] ON [dbo].[TableName] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

The transaction can take a long time. Will other SQL clients be able to use the index during the transaction?

Answer 1

Note that everything in SQL runs in it's own implicit transaction if you don't specify one, so if you're just running REORGANIZE there's no difference between running it or wrapping it in a BEGIN/COMMIT.

COMPRESSED row groups are immutable, so let's use defragmentation rather than update for your scenario. In columnstore world an update translates into a delete + insert and a delete is "deferred". More specifically deletes are reflected in the deleted bitmap, which the engine joins with the data and returns the rows visible to your transaction. The per-row group state of the delete bitmap can be seen in the sys.dm_db_column_store_row_group_physical_stats DMV as the deleted_rows column. Also note that deleting or updating an OPEN or CLOSED row group happens in-place: for deletes you'll see the row count decrement (updates won't change the row count), however you will never see any deleted_rows in these two types of row groups.

So what does REORGANIZE do? It reads small and/or fragmented row groups and combines them, but not in place, rather it writes them out as new row groups and the old row group's state will change to TOMBSTONE. Old row groups will be around while they have active readers, while transactions started after the REORGANIZE will always read the data from the new row groups.