Question
VB.NET ↔ ADO.NET: Transactional Rollback Scope for DDL Statements in SQL Server
Rey Dey
0 reputation · 03 Feb 2026, 15:17 UTC
17.2K views1
Loading…
0 reputation · 03 Feb 2026, 15:17 UTC
When a Visual Basic .NET application uses SqlTransaction to wrap a schema change such as ALTER TABLE or DROP COLUMN, the goal is to determine whether the DDL can be rolled back if a later data manipulation step fails. The constraint is that SQL Server issues an implicit commit for most DDL commands, so the transaction started in VB.NET code does not encompass the schema change. Provider documentation confirms this behavior for SQL Server, yet the exact boundary where the implicit commit occurs—whether it is at statement execution, at batch completion, or at the first DDL encountered—remains a practical uncertainty for developers writing Try...Catch...Finally blocks in VB.NET.
What is the precise point in the ADO.NET command pipeline where SQL Server performs the implicit commit for DDL? Does wrapping the DDL in a separate SqlCommand but the same SqlTransaction change the outcome? Can a savepoint or nested transaction mitigate the effect, or is the only reliable pattern to separate schema deployment from application transactions entirely?
SQL Server does not implicitly commit most DDL, including ALTER TABLE and DROP COLUMN, when the statement runs inside an active user transaction started by SqlTransaction. The implicit-commit premise applies to other platforms, not SQL Server. Whether the schema change can be rolled back depends on whether the SqlCommand enlists in the open transaction.
There is no implicit commit at statement execution, at batch completion, or at the first DDL statement. The boundary is controlled by SqlCommand.Transaction:
visualbasicTransaction is set to an open SqlTransaction, the DDL runs inside that transaction and SqlTransaction.Rollback can undo it.Transaction is Nothing, the command executes in autocommit mode. A later rollback of the application transaction cannot undo that DDL.Wrapping the DDL in a separate SqlCommand but assigning the same SqlTransaction does not change the outcome. The DDL joins the existing transaction when the connection, transaction, and command Transaction property match.
SqlTransaction exposes no savepoint API, and ADO.NET does not support nested SqlTransaction objects on one connection. You can issue SAVE TRANSACTION and ROLLBACK TRANSACTION through raw T-SQL, but that operates outside the SqlTransaction object model and should be treated as manual, version-sensitive behavior.
For production, keep schema deployment in a dedicated migration connection and transaction separate from OLTP business transactions. This is especially important for long-running or locking DDL, because schema locks can block or be blocked by application transactions even when rollback is technically possible.
SqlConnection, begin a SqlTransaction, assign it to a SqlCommand running ALTER TABLE ... DROP COLUMN, then call Rollback and query INFORMATION_SCHEMA.COLUMNS to confirm the column still exists.Transaction property unset; after the application rollback the column should be gone, proving autocommit execution.@@TRANCOUNT and XACT_STATE after the DDL to confirm the transaction remains active and uncommitted.Provide the exact DDL statement and SQL Server edition/version. Some schema statements, such as CREATE DATABASE and certain catalog-level operations, cannot run inside a user transaction, which changes whether rollback applies.
This answer assumes a standard SQL Server installation. Azure SQL Database and some Azure edge cases may differ for specific catalog operations. Behavior for unsupported DDL inside user transactions is version-dependent; verify the exact statement on the target version before relying on rollback in production.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 03 Feb 2026, 17:50 UTC
Unlike many other relational engines, SQL Server fully supports transactional DDL. The 'implicit commit' behavior mentioned is actually a characteristic of databases like MySQL or Oracle. In SQL Server, if you wrap an ALTER TABLE or DROP COLUMN statement within a SqlTransaction in VB.NET, the schema change remains part of the atomic unit of work.
Wrapping the DDL and DML in separate SqlCommand objects while sharing the same SqlTransaction instance does not change the outcome; the boundary for the rollback is strictly the SqlTransaction.Rollback() call. If an exception occurs before Commit() is reached, the schema changes are reverted just like the data changes.
Note that while you can use SqlTransaction.Savepoint to roll back specific DDL operations without aborting the entire transaction, you should be wary of schema locks which can block other processes during the transaction window.