SnowPro Advanced: Data Engineer — Question 92

A Data Engineer is debugging a SQL stored procedure that contains a transaction with a rollback using this code:

DROP TABLE A1;
CREATE OR REPLACE TABLE A1(i int);
BEGIN TRANSACTION;
INSERT INTO A1 VALUES (1), (2);
INSERT INTO A1 VALUES (3), (4);
CREATE OR REPLACE TABLE table2 (i VARCHAR);
INSERT INTO A1 VALUES (5), (6);
ROLLBACK;

How many rows will the code write into table A1?

Answer options

• A. 0 rows - the ROLLBACK command at the end of the query will remove all inserted data.
• B. 2 rows - the CREATE statement acts as an implicit ROLLBACK, so all prior inserted rows are COMMITTED. The ROLLBACK command is outside of the transaction.
• C. 4 rows - the ROLLBACK command at the end of the query will result in values 5 and 6 not being committed.
• D. 6 rows - the CREATE statement acts as an implicit END to the BEGIN transaction. All values are therefore COMMITTED.

Correct answer: D

Explanation

The correct answer is D because the CREATE statement for table2 acts as an implicit commit, finalizing all previous inserts into table A1. Therefore, all six rows (1, 2, 3, 4, 5, and 6) are committed before the ROLLBACK command is executed. Options A, B, and C are incorrect as they misunderstand how implicit commits work in relation to the transaction.