WHAT IS A TRANSACTION IN MSSQL SERVER

Anupam Mishra

Total Post:23

Points:115
Posted by  Anupam Mishra
 607  View(s)
Ratings:
Rate this:
  1. Anupam Mishra

    Post:23

    Points:115
    What is a Transaction in MSSQL SERVER

    Transaction:

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!