Using explicit transactions

OOPS! I didn’t mean to do that!

So, you are doing some harmless updates and all of a sudden, you realize that you just updated the whole entire table. NOT GOOD!

Hopefully this happens in a DEV environment, and the damage is at best an inconvenience.  Here are a few tips to help you make data changes in a NON production environment without the accidental mishaps. Use an explicit transaction.

Example:

You need to update some rows to create data that fits a scenario that you want to test.

In adventureworks, I select from the person table where Suffix = ‘Jr.’ I get 39 rows

I want to update that suffix to ‘II’ after making a change to my webform to use ‘II’ instead of ‘Jr.’ Innocent enough. this should take no time at all.

I begin my script with a “Begin Tran”

I run my update, but it is taking WAYYYYYYYYY too long. OH NO! I didn’t have a where Clause, and now every row in the person table has the Suffix of ‘II’ !!!! AGONY!

No problem, I have an open transaction. I can simply rollback, and add a DADGUM WHERE CLAUSE!

There now, that’s better. 39 rows updated. But wait, there’s more. You have an OPEN TRANSACTION!!! That can do all kinds of damage. Let’s commit. And as a bonus, we can use a built in function to check the number of open transactions for our spid. Select @@TRANCOUNT The answer should be 0 before we go on about our day.

CODE:

Begin Tran

Update [Person].[Person]
Set [Suffix] ='II'

--add where clause
Where Suffix = 'Jr.'

--Rollback tran
--Commit tran

-- BONUS!
Select @@TRANCOUNT

I hope this helps!
Yell McGuyer