SQL server transaction interview questions and answers

 
<<Previous  Next>>

Explain the characteristics of a transaction. i.e. Atomicity, Consistency, Isolation, Durability.
Explain various types of SQL Server transactions. I.e. Explicit, Autocommit and Implicit Transactions.
What is distributed transactions in SQL Server? When are they used?
Describe the characteristics of transactions i.e. atomicity, consistency, isolation.
Explain the types of transaction i.e. explicit, autocommit and implicit
What is Distributed transaction?
What is nested transaction? Explain with an example.

SQL Server transactions - October 24, 2008 at 18:10 pm by Rajmeet Ghai Explain the characteristics of a transaction. i.e. Atomicity, Consistency, Isolation, Durability. Characteristics of a transaction:- Atomicity This characteristic of a transaction means that a transaction is performed completely not performed at all. I.e. all the tasks in a transaction are completed or none are completed. E.g. transferring money from one account to another involves credit from one account and debit to another. Consistency This characteristic means that the database should be consistent before and after the transaction. For a successful transaction database can move from one state to another. Both the states should abide by the same rules. For an unsuccessful transaction, if the transaction fails to abide by the rule and leads to an inconsistent state, the transaction should be rolled back. Isolation A transaction should be isolated. This means that that no other operation should be allowed to access or see the intermediate state data. Durability A transaction on completion must persist. It should withstand system failures and should not be undone. Explain various types of SQL Server transactions. I.e. Explicit, Autocommit and Implicit Transactions. Explicit transaction A transaction which has START and END defined explicitly. The explicit transaction mode lasts until the transaction is over. When the transaction is over, the transaction is reverted back to the mode in which it started. This mode could either be implicit or autocommit. Autocommit transactions This is the default mode of transaction. In this mode, if the transaction is completed successfully, it is committed. If the transaction faces any error, it is rolled back. Implicit transactions In this mode, a new transaction is started when the current is committed or rolled back. Nothing is done to begin a transaction. One only needs to commit or roll back a transaction. Implicit transactions have a chain of continuous transactions. What is distributed transactions in SQL Server? When are they used? Distributed transactions involve two or more databases within a SQL server. The management of such transactions is done by a component called as transaction manager. Distributed transactions must be used when real time updates are required simultaneously on multiple databases. SQL Server transactions - Dec 1, 2008 at 18:10 pm by Rajmeet Ghai Describe the characteristics of transactions i.e. atomicity, consistency, isolation. Atomicity: This property of a transaction ensures that a transaction either completely or does not happen at all. E.g. transferring money from one account to another. Consistency: This property ensures the data is consistent before the transaction and left in a consistent state after the transaction. If the transaction violates the rules, it must be rolled back. Isolation: This property means that the transaction should be isolated. I.e. until the transaction is over other data or operations cannot access the transaction. This is to maintain the performance. Explain the types of transaction i.e. explicit, autocommit and implicit Explicit transaction: transactions that have a START and END explicitly written are called as an explicit transaction. They last only for the duration of the transaction. When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started Auto commit: This is the default management mode. Every SQL statement is either committed or rolled back when complete. If it completes successfully it is committed else it is rolled back. Auto commit is default mode. Implicit: when the transaction is in implicit mode, a new transaction starts automatically after the current transaction is committed or rolled back. Nothing needs to be done to define the start of the transaction. It generates continues chain of transactions. What is Distributed transaction? A distributed transaction is one in which it updates data present on two or more systems. They are useful in updating data that is distributed. They must be robust because they are subjected to failures very often. Failures like client server failure etc. interactions between these computers that are distributed is with the help of transaction managers. What is nested transaction? Explain with an example. A nested transaction is one in which a new transaction is started by an instruction that is already inside another transaction. This new transaction is said to be nested. The isolation property of transaction is obeyed here because the changes made by the nested transaction are not seen or interrupted by the host transaction. BEGIN TRANSACTION trans_1 INSERT INTO TestTrans1 VALUES (1,’mark’) COMMIT TRANSACTION trans_1; GO      /* Start a transaction and execute trans_1. */            BEGIN TRANSACTION trans_2; GO      EXEC trans_1 1, 'aaa'; //execute some procedure GO      ROLLBACK TRANSACTION trans_2; GO      EXECUTE TransProc 3,'bbb'; GO << Previous  Next>> Also read Question: Define the rules for designing Files and File groups in SQL Server. Answer - A file or file group can only be used by one database. For example, the files abc.mdf and abc.ndf contains.... Question: What are the Authentication Modes in SQL Server? Answer - SQL Server supports two security (authentication) modes.... Question: Explain Data Definition Language, Data Control Language and Data Manipulation Language. Answer - Data definition language is used to define and manage all attributes and properties of a database..... Question: What are the steps to process a single SELECT statement? Answer - SQL Server uses the following steps to process a single SELECT statement.... Question: What are the restrictions while creating batches in SQL Server? Answer - CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements.... Question: Explain GO Command. Answer - GO Command is used to signal the end of a batch..... Transferring a View from Oracle 10G XE to an SQL Server 2005 Database Here you will learn how to copy a table on an Oracle 10G XE database to a database on the SQL Server 2005. You will also learn how to install an Oracle 10G XE server and work with its database objects. The answers to following questions will be made available soon. Keep visiting. Describe the characteristics of transactions i.e. atomicity, consistency, isolation. Explain the types of transaction i.e. explicit, autocommit and implicit Explain the phases a transaction has to undergo.- already have ans What is Distributed transaction? What is nested transaction? Explain with an example. Write your comment - Share Knowledge and Experience