SQL Server Transactions Management

SQL Server Transactions Management: An Overview

SQL Server transactions ensure the atomicity, consistency, isolation, and durability (ACID) of database operations. They enable the compilation of numerous SQL statements into a single unit of work, assuring data integrity and recoverability in the event of failure. Understanding transaction management is essential for database developers and administrators, whether through self-study materials like a SQL Server Tutorial or organized training like a SQL Server Certification Course.

What is SQL Server Transactions?

A transaction is a set of T-SQL statements that are executed together as a unit like a single T-SQL statement. If all of these T-SQL statements are executed successfully, then a transaction is committed and the changes made by T-SQL statements are permanently saved to the database. If any of these T-SQL statements within a transaction fail, then the complete transaction is canceled/ rolled back.

To ensure data integrity, SQL Server uses transactions when updating several connected tables/views. They have a substantial influence on performance since they lock the data used in the transaction and prohibit anyone from modifying it during its existence.

Types of Transactions

1. Implicit Transaction

Implicit transactions are maintained by SQL Server for every DDL (CREATE, ALTER, DROP, TRUNCATE), and DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements run under the implicit transaction. If there is an error occurs within these statements individually, SQL Server will roll back the complete statement.

2. Explicit Transaction

Explicit transactions are defined by programmers. In Explicit transactions, we include the DML statements that need to be executed as a unit. Since SELECT statements don’t modify data. Hence generally we don’t include a Select statement in a transaction.

Transactions Example

CREATE TABLE Department
(
 DeptID int PRIMARY KEY, 
 DeptName varchar(50) NULL,
 Location varchar(100) NULL,
 )
GO
 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY, 
 Name varchar(50) NULL,
 Salary int NULL, 
 Address varchar(100) NULL,
 DeptID int foreign Key references Department(DeptID)
) 

This SQL script generates two tables: "Department" (primary key), DeptName, and Location, and "Employee" (primary key), Name, Salary, Address, and DeptID. These tables establish the relationship between an employee and a department.

--Now Insert data
INSERT INTO Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')
GO
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)
SELECT * FROM Department
SELECT * FROM Employee 

This code inserts data into the "Department" and "Employee" tables, containing a record for an IT department in Delhi and an employee named Mohan with a salary of 15000 who lives in Delhi and works for the IT department. It then retrieves the data of the two tables.

BEGIN TRANSACTION trans
BEGIN TRY
INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
 IF @@TRANCOUNT > 0
 BEGIN COMMIT TRANSACTION trans
 END
END TRY
BEGIN CATCH
 print 'Error Occured'
 IF @@TRANCOUNT > 0
 BEGIN ROLLBACK TRANSACTION trans 
 END
END CATCH 

This code starts a transaction called 'trans' and tries to insert data into the "Department" and "Employee" databases. If successful, the transaction is committed; otherwise, it reverses the operation and publishes an error message.

--Now Select data to see transaction affects
SELECT * FROM Employee
SELECT * FROM Department 

This code retrieves data from the "Employee" table to assess transaction consequences, then selects data from the "Department" table to investigate any relevant changes.

--Transaction with Save Point BEGIN TRANSACTION trans
BEGIN TRY
INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
IF @@TRANCOUNT > 0
 BEGIN SAVE TRANSACTION trans;
 END
INSERT INTO Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi') 
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
IF @@TRANCOUNT > 0
 BEGIN COMMIT TRANSACTION trans
 END
END TRY
BEGIN CATCH
 print 'Error Occured'
IF @@TRANCOUNT > 0
 BEGIN ROLLBACK TRANSACTION trans
 END
END CATCH 

This code starts a transaction called 'trans', inserts data into the "Department" table, saves a savepoint within the transaction, proceeds with more insertions, and commits the transaction if successful. If an error occurs, it returns to the last savepoint in the transaction and publishes an error message.

--Now Select data to see transaction affects
SELECT * FROM Employee
SELECT * FROM Department 

These lines fetch data from the "Employee" and "Department" databases to see the transaction's consequences, including any modifications made by previous SQL operations.

Read More:

• Top SQL Server Interview Questions and Answers

Summary

In this article, I try to explain transaction management in Sql Servers with examples. I hope after reading this article you will know how to manage transactions in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE