You can group several SQL statements together to perform a desired logical database task and still take advantage of the automatic rollback feature in Vantage in case of any statement failures or any errors occurring during the transaction.
However, you should have one SQL statement per group if you desire to execute each statement in its own transaction.
When multiple SQL statements are specified in one DML group in the APPLY statement, the database enforces the rule that if the group contains a DDL statement, it must be the last statement in the implicit transaction, which means the last statement in a group.
Therefore, given that the information in parentheses represents a group, the validity of the statements can be determined as follows:
- Group 1: (DDL) is valid.
- Group 2: (DDL, DDL) is invalid because only one DDL statement is allowed.
- Group 3: (DML, DML, DDL) is valid.
- Group 4: (DML, DML, DML) is valid, even though the group contains no DDL statement.
- Group 5: (DML, DDL, DML) is invalid because the DDL statement is not the last statement in the group.
If a script contains unsupported or invalid statements, the job terminates so the script can be fixed before continuing.