What is a Transaction and explain the types in it.

Transaction:

A Transaction is a group of SQL statements which are executed as a single SQL statement. Transactions ensures that all the statements in it are executed successfully and committed to the database. Otherwise, entire transaction will be rolled back.

There are two types of Transactions.

  • Implicit Transaction
  • Explicit Transaction

Implicit Transaction:

Whenever we execute a DDL or DML statement, an implicit transaction will be created by the SQL Server Engine automatically. Implicit transactions are Auto commit. It executes each statement individually and rollback the transaction if there is any error occurs in it. This kind of transactions are fully created and maintained by SQL Server Engine. We need not worry about these :)

Explicit Transaction:

Explicit transactions are user defined transactions. i.e. User has to define the beginning and ending of the transaction by using the keywords BEGIN TRAN/BEGIN TRANSACTION and COMMIT TRAN/ROLLBACK TRAN.

Ex:

———————————————————-
USE [SQLVersity_6_InterviewQ&As]
GO
———————————————————-
CREATE TABLE Transactions_Demo(
Tran_ID INT IDENTITY(1,1),
Tran_Name Varchar(50)
)
———————————————————-
— Implicit Transaction
———————————————————-
DECLARE @Increment int
SET @Increment = 0
WHILE (@Increment < 90000)
BEGIN
INSERT INTO Transactions_Demo VALUES (‘ABC’)
SET @Increment = @Increment + 1
END
———————————————————-
— Explicit Transaction
———————————————————-
DECLARE @Increment int
SET @Increment = 0
BEGIN TRAN
WHILE (@Increment < 90000)
BEGIN
INSERT INTO Transactions_Demo VALUES (‘ABC’)
SET @Increment = @Increment + 1
END
COMMIT TRAN
———————————————————-

Performance:

Transactions

From the above example, we can summarize that Explicit Transactions are much faster than Implicit Transactions.

The main difference between these two types of transactions is

  • Implicit Transactions are statement level and Explicit Transactions are batch level.
  • Compare to implicit transactions, explicit transaction are faster.(We can see in the above example)

Happy Reading :)

Leave a Reply

Your email address will not be published. Required fields are marked *