skip to Main Content

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.

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to [email protected]

About the Author

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).

 

Previous Post
Next Post
Back To Top
Contact us for a chat