SQL SERVER – CREATE Statement in TRANSACTION

Recently my client of Comprehensive Database Performance Health Check asked me if we can use the CREATE statement in the TRANSACTION or not. The answer is YES, of course, DDL you can use it in the transactions. Let us learn it via example today.

Transaction and Create Statement

Let us first try out how transaction and rollback works.

BEGIN TRAN
CREATE TABLE #Test1 (ID INT)
ROLLBACK

Now that table is created, let us try to retrieve data from the table created inside the transaction. If the transaction has been rolled back, the table has not existed.

SELECT * FROM #Test1

Now when you run the script above it will give you the following error message:

Msg 208, Level 16, State 0, Line 6
Invalid object name ‘#Test1’.

It is clear from the message above that the rollback has happened and the table does not exist.

Now before we say that DDL statements are supported by transactions let us try another test where we will practice committing the transaction and see if the table exists after it.

BEGIN TRAN
CREATE TABLE #Test1 (ID INT)
COMMIT

Now let us retrieve the data from it.

SELECT * FROM #Test1

When I run the statement above, it will give us the following result:

ID
———–
(0 rows affected)

This means that the table exists now as the transaction is committed.

Well, it is clear from this simple example that in SQL Server DDL statements like CREATE statement, etc are supported inside the transactions.

If you like the blog post, you can always follow my YouTube channel where you can see many similar videos.

Reference: Pinal Dave (https://blog.sqlauthority.com)