Every developer that has worked with SQL for any length of time has most likely been there... You are running an ad-hoc DML (data manipulation language) SQL statement against a database. Depending on the security processes in place at your company/client, you may be executing this statement against a development database, staging database, or (gasp) a production database. You've written an update statement and are thoroughly convinced it will update only the select few rows that you would like changed. However, you execute the statement and see the message "30072 row(s) affected". Depending on the environment you executed this in, you are either mildly frustrated (development environment) or your heart is in your throat (production environment).
So, how do you avoid this? Test against data you don't care about? Run a select statement that mimics the update or delete statement before you actually execute it? These are both a step in the right direction, but another way to verify that your SQL will do what you intend is to utilize transactions.
Instead of:
UPDATE Product
SET Price = 10.99
WHERE ProductName LIKE 'Football%'
(Which will cause the price of your nerf footballs to be 10.99, however the Football stadium that you happen to have for sale will also be quite the bargain.)
Why not use:
BEGIN TRANSACTION
SELECT ProductName -- View the "Before" data
FROM Product
WHERE Price = 10.99
AND ProductName LIKE 'Football%'
UPDATE Product
SET Price = 10.99
WHERE ProductName LIKE 'Football%'
SELECT ProductName -- View the "After" data
FROM Product
WHERE Price = 10.99
AND ProductName LIKE 'Football%'
ROLLBACK TRANSACTION
Utilizing a transaction and then rolling it back allows you to see the number of rows affected and, if you choose, the "before" and "after" state of the database without actually committing the changes.
The concept is simple, but re-using it as a best practice will save you time, headaches, and perhaps even disaster.