This is an article I wrote for http://csk.wbcb.com. I had discussed this with Jody as a Tutorial / Guide.

These topics will be covered in this article:

• What are Transactions
• Benefits of using Transactions
• Drawbacks of Transactions
• When to use or not to use Transactions
• Different types of Transactions
• How to use Transactions 

What are Transactions:

Transactions are a way to ensure data consistency and integrity while guaranteeing that all the steps in a multiple step SQL Statement are completed successfully. If all the steps are not completed successfully all work (within the Transaction) is rolled back (undone). SQL Server accomplishes this by keeping track of all work to be done in the transaction logs. If any step fails SQL Server will undo all the work up to the point before the transaction was started.

Benefits of using Transactions:

Transactions ensure data integrity and consistency. If you have multiple steps to complete and have them all complete before the statement is considered completed this is the way to go.

Here is an example of when to use a transaction:

When you are inserting records into a table A from table B, then deleting records in table B. You want to make sure that no errors occured during insertion to A before deleting the records from B.

You could have another statement the checks that the records match by doing a query with a join on the 2 tables before deleting which is more costly as far as SQL Server processor time. Or use a Temp or work table to keep track of what you are changing. These methods are not recommended for several reasons.

• Data in either table can be changed during execution of the multiple steps causing your joins or following steps to not work as intended, such as a deletion or modification of key records.
• SQL Server processor time should be used as efficiently as possible. Your application may not be the only one on the SQL Server.
• It is against "SQL Server Best Programming Practices" My favorite reason!
• Database could crash during your statements even if they are in a Stored Procedure. Don't laugh it could happen! Transactions are preserved in the transaction log and when the SQL server is restarted unfinished Transactions are rolled back, while part of your multistep statement may have completed successfully and changes from that are now Committed. By default SQL Server treats each statement as a Transaction and Commits them after each step. These are known as "Autocommit"

 Drawbacks of Transactions:

Uh... There are no drawbacks that I can think of...

When to use or not to use Transactions:

I would recommend using Transactions in the following situations:

• Any time data is being inserted or updated across tables or databases, preferably using Stored procedures.
• Multiple Statements that need to be completed as one step

You don't need to use Transactions when:

• Selecting rows to view or read data.
• Inserting or Updating a row in a single table when using a Stored Procedure with return values. The return value should let the application know that the statement completed successfully.

Different types of Transactions:

There are a few types of transactions. The standard Transaction which is used primarily for statements that reference tables in the same database and the Distributed Transaction which is used when Statements reference information from two or more sources (Databases or even databases on multiple SQL Servers) Distributed Transactions requires MS DTC (Microsoft Distributed Transaction Coordinator. Both of these types of Transactions can have "Named" versions as well. There are a few other implementations that I won't go into here.

How to use Transactions:

Now for the fun part!

To use a transaction in your Statement or Stored Procedure follow these simple steps:

-- Start the transaction

SET XACT_ABORT ON

BEGIN TRANSACTION TestTrans

-- Do the work

UPDATE TableName
SET FieldName = 'Value'
-- Perform addition steps as needed

-- Complete the transaction and Commit the work

COMMIT TRANSACTION TestTrans

That is a Named Transaction in it's simplest form. I prefer Named Transactions, because they give you an opportunity to visualize what you want to do and give it a label. Distributed Transactions are similar but use the keywords "Begin Distributed Transaction" This will automatically Rollback any work done if there are any errors. Your application should be designed to handle the error returned from a failed Transaction and take the appropriate steps.

You can also use IF conditions to test for errors in between steps and take action accordingly, all within the Transaction. Another useful keyword is SET XACT_ABORT ON/OFF. This controls whether or not SQL Server Rollsback the current transaction if there is a SQL Error. If it set to ON and there is an error then SQL Server stops executing the statement and rolls back the Transaction. If it set to OFF, ONLY the statement that caused the error is rolled back. You must set it ON for any Transaction that modifies data.

Conclusion:

Use Transactions to ensure data integrity when modifying data, especially in multiple step statements and complex Stored Procedures. Transactions guarantee that all steps complete successfully or all changes are rolled back (undone).

Transactions are your friend!

More information can be found online at the MSDN site and SQL Server Books Online.