Snowflake DDL and DML Statements in Transactions compared to SQL Server - Part 2
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.
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:
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:
After executing the DDL statement, we can see that there are 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:
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:
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:
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.
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.
For additional information, please follow the links below:
- Snowflake Transactions vs SQL Server Transactions
- DDL Commands in Transactions in SQL Server versus Oracle
- Transactions — Snowflake Documentation
About the author
View all my tips
Article Last Updated: 2022-10-26