What does a transaction around a single statement do?

by

It does nothing. All individual SQL Statements, (with rare exceptions like Bulk Inserts with No Log, or Truncate Table) are automaticaly “In a Transaction” whether you explicitly say so or not.. (even if they insert, update, or delete millions of rows).

EDIT: based on @Phillip’s comment below… In current versions of SQL Server, Even Bulk Inserts and Truncate Table do write some data to the transaction log, although not as much as other operations do. The critical distinction from a transactional perspective, is that in these other types of operations, the data in your database tables being modified is not in the log in a state that allows it to be rolled back.

All this means is that the changes the statement makes to data in the database are logged to the transaction log so that they can be undone if the operation fails.

The only function that the “Begin Transaction”, “Commit Transaction” and “RollBack Transaction” commands provide is to allow you to put two or more individual SQL statements into the same transaction.

EDIT: (to reinforce marks comment…) YES, this could be attributed to “superstitious” programming, or it could be an indication of a fundamental misunderstanding of the nature of database transactions. A more charitable interpretation is that it is simply the result of an over-application of consistency which is inappropriate and yet another example of Emersons euphemism that:

A foolish consistency is the hobgoblin of little minds,
adored by little statesmen and philosophers and divines

More Related Contents:

  1. SQL Server – transactions roll back on error?
  2. What happens to an uncommitted transaction when the connection is closed?
  3. Correct use of transactions in SQL Server
  4. Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?
  5. Can anyone help me to get the query to retrieve data like below [closed]
  6. NOT IN vs NOT EXISTS
  7. Simple way to transpose columns and rows in SQL?
  8. Only inserting a row if it’s not already there
  9. Optimal way to concatenate/aggregate strings
  10. How can I delete using INNER JOIN with SQL Server?
  11. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  12. Most efficient way in SQL Server to get date from date+time?
  13. Convert varchar into datetime in SQL Server
  14. Fastest way to perform nested bulk inserts with scope_identity() usage?
  15. SQL performance on LEFT OUTER JOIN vs NOT EXISTS
  16. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  17. How do I perform a GROUP BY on an aliased column in SQL Server?
  18. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  19. How do I pass a list as a parameter in a stored procedure?
  20. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  21. Get previous and next row from rows selected with (WHERE) conditions
  22. Can you have if-then-else logic in SQL? [duplicate]
  23. How to list files inside a folder with SQL Server
  24. Can SQL Server Express LocalDB be connected to remotely?
  25. SQL – Call Stored Procedure for each record
  26. SQL – How do I get only the numbers after the decimal?
  27. How to retrieve records for last 30 minutes in MS SQL?
  28. Connecting to Oracle Database using Sql Server Integration Services
  29. Why do SQL Server Scalar-valued functions get slower?
  30. Executing SQL query on multiple databases

Leave a Comment