SQL Server Interview Questions and Answers Part 6

1.) What is Transaction ?                                                                                                         
 A Transaction insure that changes will be made or none of changes will be done.

2.) What are the four Properties of Transaction ?                                                             

• Atomicity
• Consistency
• Isolation
• Durability

3.) What are the types of Transaction in SQL Server?                                                     

• Implicit Transaction
• Explicit Transaction

4.) What is the implicit transaction in sql server ?                                                         

In Implicit transaction ,if we want, we can not Rollback statement back.Implicit Transaction is by default Automatic  in SQL Server.

5.) How to implement implicit transaction in sql server ?                                    

set implicit_transactions on

insert into employee values(101,'sachin','store',14000)

Rollback

6.) What is the Explicit transaction in sql server ?                                                           

An explicit transaction is one in which we explicitly define the begin (start) and end of the transaction. 

There are some controls of transactions which are given below:-

1.) Begin Transaction:- It is used to set starting point of any transaction.

2.) Commit:- It is to save the changes in Database.

7.) What are the four main controls of  transaction in sql server ?                           

• BeginTransaction 
• Commit
• RollBack
• SavePoint

More Details...

8.) What is the Savepoint Explain ?                                                                                      
We can provide a 'save point'  to divide a transaction into sub logical transaction.It means, if within a transaction ,there is a set of statement which must be executed and another  set of statement will be executed or Rollback ,which dependent on some  condition.Then we can provide a Save Point to those statement which must be committed.

9.) What is RollBack Explain ?                                                                                               
 It is used to Rollback the changes in Database.When error is encountered in between the transaction,with the help of Rollback ,we can easily Rollback the state of the transaction.

10.) What is the meaning of Implicit Transaction ON in sql server Explain ?      

You can set implicit_transactions on.But there are some differences as given below:-.

• When you  set implicit_transactions on ,Then you will not see Begin Transaction in the Log file.
• In implicit mode ,Transaction automatically started after each commit.