|
| Featured Contents |
| Question of the Day |
|
| The Voice of the DBA |
Transactional DDL in SQL Server: In Oracle, It Does Not Compute When you’ve spent your career working with Oracle, the idea that you can wrap a DDL statement in a transaction and then roll it back sounds like science fiction, but no, it’s real and I’m asked often by my Redgate Flyway team if Oracle can do the same as SQL Server, which does have this capability. Oracle treats most DDL as auto-committing, meaning once it executes, it's done. SQL Server, however, blurs that line in fascinating ways, allowing some DDL operations to be part of an explicit transaction. Let’s explore how far that functionality goes and where the edges start to fray. What SQL Server Can Roll Back with Transactional DDLSQL Server supports transactional DDL to a surprising extent and for DevOps pipelines, it’s a feature put to good use. You can wrap many schema-changing operations in a BEGIN TRAN block and roll them back if needed. Here is an example: BEGIN TRAN CREATE TABLE TestTable (ID INT); -- Other operations ROLLBACK TRAN The above will successfully roll back the table creation. That’s not just cosmetic, the DDL for the new table won't exist afterward. Transactional DDL is supported for a large selection of DDL commands, including:
In these cases, you can treat DDL like DML, group it with other changes, test logic, and back out cleanly if needed. This is an incredibly foreign concept to Oracle developers and DBAs, who would need a drop script to remove the object the DDL created. There is no rollback unless you create one. Limitations and GotchasWhile the capability is powerful, it's not universal or without caveats. Here's where SQL Server draws the line, as not all DDL is the same. The DDL Statements Auto-Commited or can’t be rolled back, requiring the same type of undo scripts as Oracle include the following:
These commands either auto-commit or change things at a level that can't be safely encapsulated in a transaction. Implicit Transactions vs. Explicit TransactionsWhen I first started out as a DBA, I was working in both Oracle and SQL Server. I had a sign that was over my desk that said, “Commit or Be Committed” which was to remind me that in Oracle, while I need to commit my DML, in SQL Server, this was a given unless I explicitly chose to change the default behavior. SQL Server can be configured with IMPLICIT_TRANSACTIONS ON, where even standalone DDL is wrapped in a transaction (until a commit/rollback is issued), but this behavior is session-specific and often disabled by default. You must remember, if you're relying on rollback behavior, always use explicit BEGIN TRAN and remember to comment your code so other folks know what your code is about: ... ROLLBACK blocks in scripts to be sure. One of the biggest surprises I discovered is that not all tools will result in transactional DDL responding the same. In tools like SSMS (SQL Server Management Studio), some DDL behavior might appear transactional in the script window but not behave that way in a deployed change script, depending on how the engine and the GUI coordinate. This reminds us all how important it is for organizations to use proper release tools for deployments, (like Flyway) to eliminate inconsistent behaviors. Always test your assumptions in the environment and mode you'll be using (interactive vs. deployment tool, etc.) no matter what you decide to do! SQL Server uses a unified transaction log for both DML and (some) DDL operations. As long as the schema change doesn't require a full internal recompilation or system-level metadata update (like in ALTER DATABASE), the engine can keep track of it just like any other change, which makes rollback feasible. This isn't the case in Oracle, where DDL creates implicit commits before and after execution, effectively sealing the operation and flushing the redo/undo buffers. If you want to rollback any changes in Oracle, an undo script must be generated to remove the DDL change. Practical Uses for Transactional DDLIf you're like me and working with customers on numerous DevOps pipelines, the topic of transactional DDL will come up, especially if they are multiplatform organizations. The ability to have that "get out of jail free card" while doing a deployment can come in very handy. If focused on releases, there are several benefits that I would add to the top of my list:
My Final Thought: Missing Out and Use with CautionJust because SQL Server lets you roll back DDL doesn’t mean you always should. Some operations, especially those involving large tables or indexes, may still have performance implications or require exclusive locks. In mission-critical systems, rolling back DDL can lead to prolonged blocking or blocking chain effects. And for cross-platform developers, never assume these behaviors translate to Oracle, PostgreSQL, or MySQL. Each engine has its own transaction model and limitations and differences abound. If you’ve never used transactional DDL with SQL Server, test it out, it’s not hard to do and provides a unique look into a feature that simply doesn’t exist in the Oracle world. BEGIN TRAN; CREATE TABLE Sample (ID INT); INSERT INTO Sample VALUES (1); SELECT * FROM Sample; -- Works ROLLBACK; -- Now try to SELECT again (will fail if rollback worked) SELECT * FROM Sample; -- Error: Invalid object Next time, maybe we’ll dig into the Oracle RECYCLE BIN and Flashback feature. Yes, it's different, but the ability to travel in time with Oracle, is one I think you'll find interesting after we've touched on transactional DDL in SQL Server. Peace out. dbakevlar Join the debate, and respond to today's editorial on the forums |
|
| Featured Contents |
