4.2.3. T-SQL Error Handling (TRY/CATCH)

💡 First Principle: T-SQL transactions require explicit error handling to ensure data consistency. Without TRY/CATCH, partial failures can leave your database in an inconsistent state—some records inserted, others not.

Scenario: An INSERT operation must be atomic—either all records insert successfully, or none do. Without proper error handling, partial inserts can corrupt data.

Proper Transaction Pattern

BEGIN TRY
    BEGIN TRANSACTION;
    
    INSERT INTO dbo.Customers (FirstName, LastName)
    VALUES (N'Jeff', N'Price');
    
    -- If no error, commit
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- On error, rollback and report
    SELECT ERROR_NUMBER() AS ErrorNumber, 
           ERROR_MESSAGE() AS ErrorMessage;
    ROLLBACK TRANSACTION;
END CATCH;

⚠️ Exam Trap: COMMIT without BEGIN TRANSACTION attempts to commit a transaction that was never started. Always pair BEGIN TRANSACTION with COMMIT/ROLLBACK. This is a common distractor in exam questions.

• Transient concurrency errors: retry them from the CATCH block a limited number of times. In SQL Server a deadlock victim receives error 1205. Fabric Data Warehouse uses snapshot isolation with table-level locking, so deadlocks are rare, but two transactions modifying the same table can hit a write-write conflict (error 24556 or 24706, detected at commit: the first transaction wins and the others roll back); the same catch-and-retry pattern applies.
• No error, missing rows: if the batch succeeded and no CATCH block fired, nothing failed. Look at the statement logic (a TOP (n), a filter or a join) before blaming transaction handling; autocommit leaves partial data only when a statement actually errors.