Thursday, February 7, 2013

Implicite transactions in SQL Server

Assume you are "quickly" fireing a single statement like this within SQL Server Management Studio:

 UPDATE Person SET Birthdate = '1976-09-20'  

Dependend on the number of rows in the Person table and the overall performance of your database server this statement will run for some amount of time. During it runs you have the possibility to stop it hitting the "Cancel Executing Query" button.

Why can you cancel the query without having any of the Birthdates updated?
This works because internally, SqlServer is performing single statements in a transaction, even if you did not specify this explicitely. So the above statement is actually the same as

 BEGIN TRANSACTION  
 UPDATE Person SET Birthdate = '1976-09-20'  
 COMMIT