What does a transaction around a single statement do?

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

As Charles Bretana said, "it does nothing" -- nothing in addition to what is already done.

Ever hear of the "ACID" requirements of a relational database? That "A" stands for Atomic, meaning that either the statement works in its entirety, or it doesn't--and while the statement is being performed, no other queries can be done on the data affected by that query. BEGIN TRANSACTION / COMMIT "extends" this locking functionality to the work done by multiple statements, but it adds nothing to single statements.

However, the database transaction log is always written to when a database is modified (insert, update, delete). This is not an option, a fact that tends to irritate people. Yes, there's wierdness with bulk inserts and recovery modes, but it still gets written to.

I'll name-drop isolation levels here too. Fussing with this will impact individual commands, but doing so will still not make a declared-transaction-wrapped query perform any differently than a "stand-alone" query. (Note that they can be very powerful and very dangeroug with multi-statement declared transactions.) Note also that "nolock" does not apply to inserts/updates/deletes -- those actions always required locks.

For me, wrapping a single statement in a transaction means that I have the ability to roll it back if I, say, forget a WHERE clause when executing a manual, one-time UPDATE statement. It has saved me a few times.

e.g.

--------------------------------------------------------------CREATE TABLE T1(CPK INT IDENTITY(1,1) NOT NULL, Col1 int, Col2 char(3));INSERT INTO T1 VALUES (101, 'abc');INSERT INTO T1 VALUES (101, 'abc');INSERT INTO T1 VALUES (101, 'abc');INSERT INTO T1 VALUES (101, 'abc');INSERT INTO T1 VALUES (101, 'abc');INSERT INTO T1 VALUES (101, 'abc');INSERT INTO T1 VALUES (101, 'abc');SELECT * FROM T1--------------------------------------------------------------/* MISTAKE SCENARIO     (run each row individually) */--------------------------------------------------------------BEGIN TRAN YOUR_TRANS_NAME_1;   /* open a trans named YOUR_TRANS_NAME_1 */    UPDATE T1 SET COL2 = NULL;  /* run some update statement */    SELECT * FROM T1;       /* OOPS ... forgot the where clause */ROLLBACK TRAN YOUR_TRANS_NAME_1;    /* since it did bad things, roll it back */    SELECT * FROM T1;       /* tans rolled back, data restored. */--------------------------------------------------------------/* NO MISTAKES SCENARIO (run each row individually) */--------------------------------------------------------------BEGIN TRAN YOUR_TRANS_NAME_2;    UPDATE T1 SET COL2 = 'CBA' WHERE CPK = 4;   /* run some update statement */    SELECT * FROM T1;               /* did it correctly this time */COMMIT TRAN YOUR_TRANS_NAME_2           /* commit (close) the trans */--------------------------------------------------------------DROP TABLE T1--------------------------------------------------------------