Importance of transaction in SQL Server
Recently I have written few articles on error handling (list given in the last paragraph of this article) topic whereas I have used Transaction in my TSQL script there and have promised blog reader that I will come up with more details and explanation of transaction. Finally today I thought to write about transaction.
Transaction is one of the most important part for developer to be master in development. We might have cases where we are doing more then one combination of INSERT / UPATE / DELETE (DML statements) in TSQL code block. It is quite possible that any of the DML operation failed in-between execution and we want to revert back any DML changes made prior from that failure to keep consistency and integrity. Transaction comes to your help for this need and makes your database ACID (Atomicity, Consistency, Isolation and Durability) compliant (Click here to know more about ACID.).
According to me, there should be Begin Transaction, Commit Transaction and ROLLBACK Transaction with each TSQL block if it is using DML statements, no matter whether code block has one or more DML operation going.
Transaction creates locking so it is also advisable to keep your transaction as short as possible to avoid locking and blocking issue.
Let us see how we can use transaction in TSQL code block. We will need to table with some data for demonstration. Let us create two table and define foreign key between them.
CREATE TABLE tblEmployeeDetail
ID INT IDENTITY(1,1) CONSTRAINT pk_tblEmployeeDetail_Id PRIMARY KEY
CREATE TABLE tblEmployeeSalary
ID INT IDENTITY(1,1)
,EmployeeID INT CONSTRAINT fk_tblEmployeeDetail_id FOREIGN KEY REFERENCES tblEmployeeDetail(ID)
,Salary INT NOT NULL
INSERT INTO tblEmployeeDetail
SELECT ‘Ritesh Shah’,’DBA’,’Ahmedabad’ UNION ALL
SELECT ‘Teerth Shah’,’Developer’,’Ahmedabad’
INSERT INTO tblEmployeeSalary
SELECT 1,50000 UNION ALL
Execute following UPDATE statements which will ROLLBACK transaction when error come across.
–We are now going to update designation in EmployeeDetail table
–and update the salary. if there is an issue in any of the UPDATE statement
–both table should be in the previous state
UPDATE tblEmployeeDetail SET Designation=’DBA Manager’ WHERE ID=1
–giving NULL value though it is not allowed in SALARY field (as it is NOT NULL field)
–it will generate an error and the Designation in tblEmployeeDetail table will also revert back
UPDATE tblEmployeeSalary SET Salary=NULL WHERE EmployeeID=1
declare @trancount int;
set @trancount = @@trancount;
–Confirm whether any record is changed or not
SELECT * FROM tblEmployeeDetail
SELECT * FROM tblEmployeeSalary
Here is the error I have received while executing UPDATE code block.
Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.
If you are interested to read error handling article I have written previously, have a look at list below:
Error handling with “THROW” command in SQL Server 2012 (Click Here)
List of Errors and severity level in SQL Server with catalog view sysmessages (Click Here)
Create custom error message by sys.sp_addmessage in SQL Server 2012 (Click Here)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.