SQL Server has no CTRL+Z

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”

Oh NO! No Where Clause!
Oh NO! No Where Clause!

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!

A where clause was added.
A where clause was added.

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

Leave a Reply