DDL and Transactions

When I started in database management many years ago I worked exclusively on Oracle and I got used to the fact that transactions worked with the DML statements SELECT, INSERT, UPDATE and DELETE but that things were different when it came to DDL statements such as CREATE TABLE and DROP TABLE. In short, transactions did not support DDL.

I then started working with SQL Server and for a long time I assumed that the same was true. It came as quite a surprise when I discovered that it was not true and that SQL Server supports transactions for both DML and DDL.

Consider the following T-SQL statements:

CREATE TABLE TestTable1 (ID int NOT NULL)

INSERT INTO TestTable1 VALUES (1), (2), (3)

BEGIN TRAN

DROP TABLE TestTable1

ROLLBACK

SELECT COUNT(*) FROM TestTable1

The answer is 3. After the rollback the table still exists and still has 3 rows it it. The ROLLBACK has successfully undone the DROP TABLE.

Same applies to a rollback of CREATE TABLE. Even TRUNCATE TABLE can be rolled back.

Transactions work by storing before and after images of rows in the transaction log. This log data is then used when undoing transactions in the database.

If I have a table with 10 million rows and I delete all of them using DELETE it takes quite a while as the rows are effectively copied into the transaction log file.

If on the other hand I start a transaction and delete all the rows using TRUNCATE TABLE it happens in an instant, but the roll back still works!  It clearly is not copying all the deleted rows to the log, so how does it work?

The answer is that is still logs data in the transaction log but at a much higher level of granularity. As it deletes rows, empty 8KB data pages are de-allocated and it is these page de-allocations that are logged and not the rows. A very effective and efficient solution.