opc.three (9/19/2012)
For A: Unlike Oracle, in SQL Server if you want each statement to implicitly start a new transaction then you must SET IMPLICIT_TRANSACTIONS ON. The default in SQL Server for "implicit transactions" is actually OFF, i.e. "autocommit mode", meaning if you want to start a transaction you must issue a BEGIN TRAN to start it.
A data modification statement run without an explicit transaction and without implicit transactions on still runs in a transaction, it's just automatically started and committed (triggers run within the automatically created transaction). You can see this if you query @@trancount in a trigger, the transactions DMV while the query is running or by looking at the log records written.
Implicit transactions just means that SQL automatically starts the transaction (assuming there isn't one open), but does not commit it
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability