MS SQL Server 2 - Is Transaction Really Required ?

Hello All,

I am Chakrapani Upadhyaya a full stack engineer, In this article we will learn about SQL Server Transaction.

What is Transaction?

A transaction in SQL Server is a sequence of database operations(insert, update, delete...) that are executed as a single unit of work, such that either all the operations are committed to the database or none of them are committed(rollback). 

Why We Need?

The reason behind this is to ensure that the database remains in a consistent state even if an error or failure occurs during the execution of one or more database operations.

Transactions are very important in SQL Server because they help to prevent data inconsistencies that can occur when multiple users or processes are accessing the same data at the same time. 

Let's work on simple example:

We are using a transaction with try-catch blocks for inserting and updating data to a SQL Server table.

1. Open SQL Server Management Studio

2. Connect to the Database Engine.

3. Create a new database

CREATE DATABASE MY_LEARNING_SQL_SERVER
GO

4. Open/Use created table

USE MY_LEARNING_SQL_SERVER
GO

 5. Create table called COURSE

CREATE TABLE COURSE
(
	COURSE_ID INT PRIMARY KEY IDENTITY(1,1),
	TITLE     NVARCHAR(300),
	STARTDATE DATETIME,
	ENDDATE   DATETIME,
	STATUS    NVARCHAR(100)
)
GO

6. Create Stored Procedure to perform the insert operation with transaction

CREATE OR ALTER PROC SP_INSERT_COURSE
AS
BEGIN

-- Start the transaction
BEGIN TRANSACTION

BEGIN TRY

	-- Lets Insert Some Records to the COURSE table
	INSERT INTO COURSE(TITLE,STARTDATE,ENDDATE,STATUS)
	VALUES ('ASP.NET WITH MS SQL SERVER', (GETDATE()+1), 

                (GETDATE()+31),'Active')
	INSERT INTO COURSE(TITLE,STARTDATE,ENDDATE,STATUS)
	VALUES ('C# PROGRAMMING', (GETDATE()+1), (GETDATE()+31),

                'Active')

	-- Lets Update Some Records to the COURSE table
	UPDATE COURSE
	SET STATUS='Not Available'
	WHERE COURSE_ID=1

	-- Commit the transaction if both statements are succeed
	COMMIT TRANSACTION

END TRY

BEGIN CATCH

	-- Rollback the transaction if there is an error occurred
	ROLLBACK TRANSACTION

	-- Output the error message
	SELECT ERROR_MESSAGE()

END CATCH

END
GO

In the above stored procedure, we first start a transaction using the BEGIN TRANSACTION statement. 

We then have a try block that contains an INSERT statement to insert data into the table COURSE and an UPDATE statement to update data in the COURSE table. If both statements succeed, we commit the transaction using the COMMIT TRANSACTION statement.

If there is an error in either the INSERT or UPDATE statement, then the control is transferred to the catch block using the BEGIN CATCH statement. Where we rollback the transaction using the ROLLBACK TRANSACTION statement and output the error message using the SELECT ERROR_MESSAGE() statement.

I hope you enjoyed this article, That's it for this article, See you in next article until then take care bye see you.