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:
Here we are creating simple Transaction for inserting data:
Now, we have viewing my table data: