DDL and DML Statements in transactions for SQL Server and Snowflake

Problem

The previous article discussed several transaction management differences between Snowflake and MS SQL Server. We will continue comparing how transactions are organized in Snowflake and MS SQL Server. Specifically, we will discuss DDL statements and transactions in both systems.

Solution

In the following two examples, we will demonstrate DDLs and transactions for Snowflake and MS SQL Server correspondingly to point out how they differ in implementation.

DDL and Transactions in Snowflake

In Snowflake, DDL statements are executed as separate transactions and cannot be included in other transactions. If we run a DDL statement when there is an open transaction, the DDL statement commits that transaction and then executes the DDL command as a separate transaction. Let’s illustrate this behavior with an example. First, we will create a test environment in Snowflake:

--Snowflake
CREATE DATABASE TESTDB;
  
CREATE SCHEMA TESTDB.TESTSCHEMA;
 
CREATE TABLE TESTDB.TESTSCHEMA.TESTTABLE(ID INT);

After running the code above, we can see our new objects in the Snowflake Web Interface:

new objects in the Snowflake Web Interface

Now, let’s run the code that includes transactions and a DDL statement (table creation in our example):

--Snowflake
 
--Transactions before starting the first transaction
SHOW TRANSACTIONS;
 
BEGIN TRANSACTION;
 
 
   INSERT INTO TESTSCHEMA.TESTTABLE(ID) VALUES (1), (2);
   
       
   --Transactions before DDL
   SHOW TRANSACTIONS;
   
   --DDL statement
   CREATE TABLE TESTDB.TESTSCHEMA.NEWTESTTABLE(ID INT);
   
   --Transactions after DDL
   SHOW TRANSACTIONS;
      
ROLLBACK;
 
--Transactions after ROLLBACK
SHOW TRANSACTIONS;
  
SELECT * FROM TESTSCHEMA.TESTTABLE;
 
SELECT * FROM TESTSCHEMA.NEWTESTTABLE;

Before executing our DDL statement, we check our current transaction information just after the DML (INSERT statement in our example) statement:

check our current transaction information just after the DML statement

After executing the DDL statement, we can see that there are no open transactions:

no open transactions

This is because the DDL statement was issued when there was an open transaction and, therefore, committed the existing transaction. Then the DDL statement was executed as a separate transaction. Hence, the next ROLLBACK statement will not change anything as there are no open transactions. As a result, we will have two rows inserted into TESTTABLE within the first transaction, which is successfully committed by the DDL statement, and we have a new object – NEWTESTTABLE, created by the DDL statement:

new object-NEWTESTTABLE

DDL and Transactions in SQL Server

Unlike Snowflake, in SQL Server, DDL statements can be included in transactions. We will demonstrate a similar example in SQL Server. To create a test environment in SQL, open SQL Server Management Studio (SSMS) and run the following code:

--MS SQL Server
CREATE DATABASE TESTDB
GO
 
USE TESTDB
GO
 
CREATE SCHEMA TESTSCHEMA
GO
 
CREATE TABLE TESTDB.TESTSCHEMA.TESTTABLE(ID INT)
GO

Then, we will execute the transaction below, which includes a DDL statement:

no active transactions

Looking at the results, we can see that we had no active transactions before starting our transaction (@@TRANCOUNT=0). After starting our transaction, we can see that @@TRANCOUNT=1 before and after the DDL statement. Then, after the ROLLBACK command, we can see that we do not have any active transactions (@@TRANCOUNT=0). The result of the first SELECT statement returns an empty table, which means that the DML(INSERT) statement is rolled back. As a result of the second SELECT statement, we got an error because NEWTESTTABLE does not exist:

an error because NEWTESTTABLE does not exist

This happens because the DDL statement which creates this table was rolled back due to the ROLLBACK command. Thus, we can see that the DDL statement is included in a transaction like a DML statement.

Conclusion

In SQL Server, DDL statements can be included in transactions, and it is possible to commit or roll back more than one DDL statement altogether. In contrast, in Snowflake, it is impossible to include DDL statements in transactions as each DDL statement starts its transaction and is executed as one separate transaction.

Next Steps

For additional information, please follow the links below:

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *