ALTER TABLE DROP COLUMN in Azure SQL Database: Uncertain Rollback Safety

Direct answer

ALTER TABLE DROP COLUMN in Azure SQL Database is transactional and can be rolled back when executed inside an explicit BEGIN TRANSACTION. It is not auto-committed by default. The confusion comes from other RDBMS engines where DDL is auto-committed, and from Azure SQL Database operations that are truly auto-committed at the database level.

Confirmed facts

• Azure SQL Database runs the SQL Server engine. DDL is logged and participates in the transaction log.
• An explicit transaction wraps the metadata change: BEGIN TRANSACTION; ALTER TABLE dbo.T DROP COLUMN c; ROLLBACK; will leave column c present.
• There is no server-level setting or query hint to force transactional behavior because transactional DDL is the default for supported statements.
• There is no catalog flag that marks a statement as "rollback-safe". Safety is determined by whether the statement executes inside an active user transaction with XACT_STATE() = 1.

Likely explanation for the uncertainty

The premise that DROP COLUMN is auto-committed is likely carried over from MySQL or from Azure SQL Database database-level operations. The engine does auto-commit a small set of database-scoped changes that cannot be rolled back in a user transaction, e.g., CREATE DATABASE, DROP DATABASE, ALTER DATABASE, and some system catalog operations. Table-level ALTER TABLE DROP COLUMN is not in that set.

What you can do for this case

1. Wrap the change explicitly:

BEGIN TRANSACTION;
ALTER TABLE dbo.YourTable DROP COLUMN YourColumn;
-- verify with SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.YourTable')
ROLLBACK; -- or COMMIT;

1. Check transaction state in the same session: SELECT @@TRANCOUNT, XACT_STATE();
2. Confirm active transaction: SELECT session_id, name FROM sys.dm_tran_active_transactions WHERE transaction_id IN (SELECT transaction_id FROM sys.dm_tran_session_transactions WHERE session_id = @@SPID);

Cautions

Rollback is only possible if the statement succeeds inside the transaction. If the column is referenced by an index, foreign key, primary key, computed column, or an indexed view, the ALTER will fail before the metadata change and the transaction will be aborted. Rolling back large schema changes can generate significant log activity and undo time.

One missing diagnostic detail that changes the recommendation: are there dependent objects on the column, such as indexes, constraints, or indexed views? If yes, the drop must be preceded by dropping those dependencies, otherwise the transaction will fail and rollback will be moot.