Contents
What are transactions in SQL Server?
Transactions (Transact-SQL) A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.
How to wrap database modifications within a transaction?
When working with the SqlClient provider in ADO.NET, transactions are initiated through a call to the SqlConnection class s BeginTransaction method, which returns a SqlTransaction object. The data modification statements that makeup the transaction are placed within a try…catch block.
What happens when a transaction is successful in SQL Server?
If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased. SQL Server operates in the following transaction modes:
When to wrap a statement in a transaction?
As per Ypercube’s comment, there is no real purpose in placing one statement inside a transaction, but possibly this is a coding standard or similar. Often, when issuing adhoc statements directly against SQL, it is a good idea to wrap your statements in a transaction, just in case something goes wrong and you need to rollback, i.e.
What happens when a transaction is rolled back in SQL?
If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
What makes a Transact-SQL transaction a batch scoped transaction?
Batch-scoped transactions Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.