One of the questions I often receive from my clients is whether we can use the CREATE statement in a transaction in SQL Server. The answer is yes, you can definitely use Data Definition Language (DDL) statements like CREATE inside transactions. Let’s explore this concept through an example.

First, let’s understand how transactions and rollbacks work. Consider the following script:

BEGIN TRAN
CREATE TABLE #Test1 (ID INT)
ROLLBACK

In the script above, we create a temporary table called #Test1 inside a transaction and then roll it back. If the transaction is rolled back, the table will not exist. To verify this, let’s try to retrieve data from the table:

SELECT * FROM #Test1

When you run the above script, you will receive an error message stating “Invalid object name ‘#Test1′”. This confirms that the rollback has occurred and the table no longer exists.

Now, let’s perform another test where we will commit the transaction instead of rolling it back:

BEGIN TRAN
CREATE TABLE #Test1 (ID INT)
COMMIT

After committing the transaction, let’s retrieve data from the table:

SELECT * FROM #Test1

When you run the above script, you will notice that it returns “0 rows affected”. This indicates that the table now exists since the transaction was committed.

From this simple example, it is evident that SQL Server supports DDL statements like CREATE inside transactions. This allows you to perform various operations on your database schema within a transaction, ensuring data integrity and consistency.

If you found this blog post helpful, I encourage you to follow my YouTube channel where you can find more informative videos on SQL Server concepts and best practices.