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.
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.
I hope this helps!