DP-203 · Question #328
Drag and Drop Question You are batch loading a table in an Azure Synapse Analytics dedicated SQL pool. You need to load data from a staging table to the target table. The solution must ensure that…
The correct answer is BEGIN DISTRIBUTED TRANSACTION; ROLLBACK TRAN; COMMIT TRAN. In Azure Synapse Analytics dedicated SQL pools, distributed transactions must be explicitly managed using BEGIN DISTRIBUTED TRANSACTION (not BEGIN TRAN) because data operations span distributed nodes. The correct pattern is: BEGIN DISTRIBUTED TRANSACTION to start the…
Question
Drag and Drop Question You are batch loading a table in an Azure Synapse Analytics dedicated SQL pool. You need to load data from a staging table to the target table. The solution must ensure that if an error occurs while loading the data to the target table, all the inserts in that batch are undone. How should you complete the Transact-SQL code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Answer:
Exhibit
Answer Area
Drag items
Correct arrangement
- BEGIN DISTRIBUTED TRANSACTION
- ROLLBACK TRAN
- COMMIT TRAN
Explanation
In Azure Synapse Analytics dedicated SQL pools, distributed transactions must be explicitly managed using BEGIN DISTRIBUTED TRANSACTION (not BEGIN TRAN) because data operations span distributed nodes. The correct pattern is: BEGIN DISTRIBUTED TRANSACTION to start the transaction, ROLLBACK TRAN inside the error-handling block (e.g., CATCH) to undo all inserts if an error occurs, and COMMIT TRAN at the end of the TRY block to persist changes if no error occurs. This ensures atomicity - either all inserts succeed or none are committed.
Topics
Community Discussion
No community discussion yet for this question.