Home > DeveloperSection > Interviews > What is a Transaction in MSSQL SERVER

Posted on    February-28-2016 11:25 PM

 MSSQL Server SQL Server  SQL  SQL Server 2008  SQL Server 2012 
 1 Answer(s)
  266  View(s)
Rate this:

Anupam Mishra

Total Post:23

Posted on    February-29-2016 12:25 AM

A transnational database is a DBMS where write transactions on the database are able to be rolled back if they are not completed properly (e.g. due to power or connectivity loss).
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:
1. Implicit Transaction: Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. 
2. Explicit Transaction:  Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be execute as a unit. 

Here, we have two tables Employee & Department: Structures  is below:

Department Table:

Employee Table:

Here we are creating simple Transaction for inserting data:

Now, we have viewing my table data:

Modified On Feb-28-2016 11:27:33 PM

Don't want to miss updates? Please click the below button!

Follow MindStick