From the course: Complete Guide to Advanced SQL Server

Unlock this course with a free trial

Join today to access over 25,300 courses taught by industry experts.

Transaction savepoints

Transaction savepoints - SQL Server Tutorial

From the course: Complete Guide to Advanced SQL Server

Start my 1-month free trial Buy for my team

Transaction savepoints

“

Within a transaction, you can create save points that allow you to roll back just a portion of the transaction while committing other portions of the transaction. To do this, I first want to introduce the @@TRANCOUNT system variable. Let's see what it currently contains by running this statement on Line Number 6. We'll SELECT @@TRANCOUNT and I'll give it a column name as Open Transactions. This variable is used to track whether you're currently working inside of a transaction or not. The number zero here indicates that we're not in a transaction at this moment. If you begin a transaction by running Line Number 9 and then selecting that variable again, you'll see that that number has incremented up to one. We're now inside of a single transaction. Now, it is possible to create transactions within transactions, or what's referred to as nested transactions, and this can be helpful to manage complex programming routines. If a rollback is encountered at any level within a nested…

Contents