19
DecSQL Server Transactions Management
A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.
We use transaction in that case, when we try to modify more than one tables/views that are related to one another. Transactions affect SQL Server performance greatly. Since When a transaction is initiated then it locks all the tables data that are used in the transaction. Hence during transaction life cycle no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.
Types of Transactions
Implicit Transaction
Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements runs under the implicit transaction. If there is an error occurs within these statements individually, SQL Server will roll back the complete statement.
Explicit Transaction
Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be execute as a unit. Since SELECT statements doesn’t modify data. Hence generally we don’t include 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) )

--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

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

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

--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

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

Summary
In this article I try to explain transaction management in Sql Server with example. I hope after reading this article you will be know how to manage transaction in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.
Take our free skill tests to evaluate your skill!

In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.