ALTER TABLE SWITCH rollback behavior with active referencing transactions
0 reputation · 03 Mar 2022, 16:08 UTC
Transactional DDL in SQL Server generally allows most CREATE, ALTER and DROP statements to be rolled back inside an explicit user transaction. Partition switching via ALTER TABLE ... SWITCH is a metadata operation intended to move data between partitions with minimal logging.
The design question is whether the switch remains fully rollback safe when active transactions exist that reference the source or target partition. Documentation describes the operation as metadata-only and subject to schema stability locks, but does not clarify the outcome of a ROLLBACK after the switch has been applied, or how concurrent referencing transactions are handled.
Does a ROLLBACK revert the partition mapping after a successful SWITCH? Under what conditions does SQL Server block or allow the SWITCH when active transactions reference the partitions? Is the operation treated as fully transactional for rollback purposes in current supported versions?