The rollback SQL statement is used to manually rollback transactions in MS SQL Server.
Transactions in SQL Server are used to execute a set of SQL statements in a group. With transactions, either all the statements in a group execute or none of the statements execute.
In the case where one of the queries in a group of queries executed by a transaction fails, all the previously executed queries are rollbacked. Transactions in the SQL server are rollbacked automatically. However, with the rollback SQL statement, you can manually rollback a transaction based on certain conditions.
In this article, you will see what a transaction is and how it can be rollbacked both manually and automatically.
First, let’s create a dummy dataset for you to practice on unless you are 100% confident that your database is fully backed.
Create a dummy database
The following script creates a dummy database named BookStore with one table, i.e., Books. The Books table has four columns: id, name, category, and price:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE Database BookStore; GO USE BookStore; CREATE TABLE Books ( id INT, name VARCHAR(50) NOT NULL, category VARCHAR(50) NOT NULL, price INT NOT NULL ) |
Let’s now add some dummy records in the Books table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE BookStore INSERT INTO Books VALUES (1, 'Book1', 'Cat1', 1800), (2, 'Book2', 'Cat2', 1500), (3, 'Book3', 'Cat3', 2000), (4, 'Book4', 'Cat4', 1300), (5, 'Book5', 'Cat5', 1500), (6, 'Book6', 'Cat6', 5000), (7, 'Book7', 'Cat7', 8000), (8, 'Book8', 'Cat8', 5000), (9, 'Book9', 'Cat9', 5400), (10, 'Book10', 'Cat10', 3200) |
The above script adds 10 dummy records to the Books table.
Executing multiple queries without using transactions
In this section, we will see the problems that occur if we execute multiple queries in a group without transactions. In the latter section, we will see how transactions can be used to automatically and manually rollback SQL queries and deal with these issues.
Look at the following script:
|
1 2 3 4 5 6 |
INSERT INTO Books VALUES (15, 'Book15', 'Cat5', 2000) UPDATE Books SET price = '25 Hundred' WHERE id = 15 DELETE from Books WHERE id = 15 |
In the script above, we execute three queries. The first query inserts a new record in the Books table where the id of the record is 15. The second query updates the price of the book with id 15. Finally, the third query deletes the record with id 15. If you execute the above query, you should see the following error:
The error is pretty self-explanatory. It says that we cannot assign the string value ‘25 Hundred’ to the ‘id’ column, which is of integer type. Hence, the second query fails to execute. However, the problem with the above script is that while the second query fails, the first query still executes. You can verify this by selecting all the records from the Books table, as shown below:
What if you really want is that if the second query fails, the first query should be rollbacked as well so that you go back to how you were before you executed the queries?
To achieve this, you need to use transactions.
Automatically rollback SQL transactions
As I said earlier, if one of the queries in a group of queries executed inside a transaction fails, all the previously executed SQL statements are rollbacked. Let’s see how transactions can be used to rollback SQL queries:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
BEGIN TRANSACTION INSERT INTO Books VALUES (20, 'Book15', 'Cat5', 2000) UPDATE Books SET price = '25 Hundred' WHERE id = 20 DELETE from Books WHERE id = 20 COMMIT TRANSACTION |
To start a transaction, the BEGIN TRANSACTION statement is used, followed by the set of queries that you want to execute inside the transaction. To mark the end of a transaction, the COMMIT TRANSACTION statement can be used.
In the script above, we execute the same three SQL queries that we did in the last section. However, this time the queries have been executed inside a transaction. Again, the first query will execute successfully and an error will occur while executing the second query. Since the queries are being executed inside a transaction, the failure of the second query will cause all the previously executed queries to rollback. Now, if you select all the records from the Books table, you will not see the new record with id 20, inserted by the first query inside the transaction.
Manually rollback SQL transactions
In the previous section, you saw how transactions automatically rollback themselves if one of the queries cannot be executed successfully. However, you may want to rollback a query based on certain conditions as well. For example, you may want to rollback a transaction that inserts a record in the books table if a book with the same name already exists.
In that case, you can use the rollback SQL statement.
Look at the following example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @BookCount int BEGIN TRANSACTION AddBook INSERT INTO Books VALUES (20, 'Book15', 'Cat5', 2000) SELECT @BookCount = COUNT(*) FROM Books WHERE name = 'Book15' IF @BookCount > 1 BEGIN ROLLBACK TRANSACTION AddBook PRINT 'A book with the same name already exists' END ELSE BEGIN COMMIT TRANSACTION AddStudent PRINT 'New book added successfully' END |
In the script above, we declare a variable @BookCount. Next, we create a transaction named AddBook. To create a named transaction, you simply have to pass any string name for the transaction after the BEGIN TRANSACTION statement.
Inside the transaction, a book with id 20 and name Book15 is inserted in the Books table. After that, the COUNT function is used to count the Books with the name Book15.
If the count is greater than 1, that means a book already exists with the name Book15. In this case, the rollback SQL statement is used to rollback the AddBook transaction manually; otherwise, the transaction will be committed and an appropriate message is displayed to the reader.
You can see that the syntax of the rollback SQL statement is simple. You just have to write the statement ROLLBACK TRANSACTION, followed by the name of the transaction that you want to rollback.
Now, try to run the AddBook transaction to insert the record where the name is Book15 (make sure that no book with this name already exists in the Books table).
You will see that the transaction will execute successfully, and the following message will be displayed to the reader:
Now, again try to run the AddBook transaction. You will see that this time the transaction will fail since a book with the name Book15 already exists in the database. Therefore the transaction will be rolled back with the following message displayed to the user:
Conclusion
The article explains how to rollback SQL queries using transactions. Queries can be automatically or manually rolled back via transactions. Automatic rollback happens when a query fails to execute for any reason. Manual rollback occurs depending on user-defined conditions. The rollback SQL statement is used to manually rollback SQL queries in SQL Server.
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021