Transactions and Error Handling in SQL Server
Learn to catch and raise T-SQL errors, commit or roll back transactions, and choose isolation levels in SQL Server, with interactive exercises.
Transactions and error handling decide whether a T-SQL script leaves a SQL Server database consistent when a statement fails. Left unhandled, an error can leave inconsistent data behind and lead to wrong business decisions. You learn to catch errors, raise and customize your own, commit or roll back work when something goes wrong, and see what happens when two or more people change the same data at once, practicing on a bank accounts dataset and an electric bike store dataset.
What You Will Achieve
- Write T-SQL scripts that catch a failing statement and report details about the error
- Raise errors with RAISERROR or THROW and build custom messages with FORMATMESSAGE
- Decide when to commit or roll back a transaction, using savepoints, @@TRANCOUNT and XACT_ABORT
- Choose the isolation level that fits how much concurrent change a query can tolerate
Program Overview
Error Handling in T-SQL
Builds error handling into scripts, then raises and throws errors with messages written for the situation.
- TRY…CATCH syntax and nested constructs
- Error anatomy, uncatchable errors and error functions
- RAISERROR and THROW, with or without parameters
- Custom messages by concatenation and FORMATMESSAGE
Transactions: Commit, Rollback, and Savepoints
Introduces transactions and the statements that commit or roll them back, then tracks how many are open and what state they are in.
- Transaction statements and rolling back after an error
- @@TRANCOUNT and savepoints
- XACT_ABORT, XACT_STATE and doomed transactions
Concurrency and Transaction Isolation Levels
Defines concurrency and how it affects transactions, then works through each isolation level to avoid or allow specific kinds of reads.
- Dirty, non-repeatable and phantom reads
- READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE
- SNAPSHOT and READ COMMITTED SNAPSHOT
- WITH (NOLOCK) and avoiding being blocked
Certificate Awarded (Paid)
Job Outlook
Error handling and transaction control are used wherever T-SQL scripts and stored procedures change data in SQL Server, keeping records consistent when a statement fails or several users touch the same rows.
Good for: SQL Server developers who write T-SQL scripts and stored procedures and need them to fail safely without leaving inconsistent data.
Keep in mind: Every exercise runs T-SQL on SQL Server, so statements such as RAISERROR, THROW, XACT_ABORT and the SNAPSHOT setting are taught in that product’s syntax.
Bottom line: A focused T-SQL course that ties error handling to transactions and isolation levels, practiced on bank and bike store data.
Frequently Asked Questions
Which concurrency problems do the isolation levels address?
The concurrency chapter covers dirty reads, non-repeatable reads and phantom reads, then works through READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE and SNAPSHOT to show how each level avoids or allows them.
Are both RAISERROR and THROW taught?
Yes. One chapter covers RAISERROR and how to catch it, then THROW with and without parameters, including a stored procedure that throws an error. It ends with custom messages built by concatenation or by FORMATMESSAGE with a message string or a message number.
What does the course teach about XACT_ABORT and XACT_STATE?
The transactions chapter has lessons on how XACT_ABORT behaves, how it works together with THROW, and how XACT_STATE reports a transaction's state. These lead into a lesson on doomed transactions, after the lessons on @@TRANCOUNT and savepoints.
How are WITH (NOLOCK) and READ UNCOMMITTED related here?
The concurrency chapter includes a lesson that compares WITH (NOLOCK) with the READ UNCOMMITTED isolation level. It sits alongside lessons on the SNAPSHOT level, turning READ COMMITTED SNAPSHOT on, and avoiding being blocked by other sessions.


There are no reviews yet.