Sometimes, on a high-volume / high TPS database, I want to make sure I've made updates to ALL the recovers, and not leave some orphaned data out there. This usually occurs because someone has a locked record while they're making a change. So when I'm making updates to multiple records (remember, SQL is SET based, so it applies to all records that match a condition), I use a belt and suspenders approach.
I verify the number of records that are going to be updated from the new value to the old value both before and AFTER I make the change. If I'm updating a set of users that are going to be changed from "Admin" to "SysAdmin", the code would look like the following:
BEGIN TRANSACTION SELECT COUNT(*) AS 'admin' FROM Users WHERE level = 'admin' SELECT COUNT(*) AS 'sysadmin' FROM Users WHERE level = 'sysadmin' UPDATE Users SET level = 'sysadmin' where level='admin' SELECT COUNT(*) AS 'admin' FROM Users WHERE level = 'admin' SELECT COUNT(*) AS 'sysadmin' FROM Users WHERE level = 'sysadmin' -- COMMIT -- ROLLBACK
It should return something like the following:
If all the numbers match up, I can highlight and execute the COMMIT statement that is commented out. If the numbers DO NOT MATCH I can execute the ROLLBACK statement to safely undo my work.