One of my favorite presentations is Revenge the SQL by Rob Volk (blog|twitter). It’s a demonstration of a lot of strange, fun, and sometimes terrifying things you can do with SQL Server. Sometimes just for amusement and sometimes if you want to burn down your house. None of it should be used at work!!!
That said, at the end of the presentation during Summit this year Rob asked if anyone had any interesting additions and maybe not so surprisingly I had thought of one.
BEGIN TRANSACTION GO 1000
If you are already cringing you can probably skip the rest of this post. This could be really nasty at the beginning of a piece of code if someone isn’t paying attention. It actually came from a piece of code I was asked to debug a while back and has a couple of moving parts. The piece of code looked something like this:
BEGIN TRANSACTION DELETE TOP (10000) FROM dbo.LotsOfData WHERE Status = 'Done'; GO 1000 COMMIT
The database was in SIMPLE recovery mode and the idea was to remove data in chunks. However the developer complained that it was still blowing up the log and then when he tried to close the query window there was an open transaction.
Let me start by explaining that the number after GO means to run the above batch that number of times. So GO 10 means run the batch 10 times. In this case the DELETE was being run 1000 times, just as the developer wanted, but they missed that the BEGIN TRANSACTION was being run each time was well. Now, in reality there is only one transaction (a single ROLLBACK will undo all levels of the transaction) but each time you run BEGIN TRANSACTION it creates a new level of the transaction, and requires an additional COMMIT to clear. So in order to clear this a COMMIT had to be run an additional 999 times.