Seems like a simple question, but wondering what is the "best" way to do this if you were "dotting your i's and crossing your t's".

  • 1
    You need to be more specific. How are you identifying your "handful of rows"? Feb 11, 2012 at 2:04
  • 2
    ...a LOT more specific.
    – JNK
    Feb 11, 2012 at 3:24
  • @Aaron Bertrand: It's unclear what additional information you need, would you please be specific as to the type of information you require to be included or exclude from the question. Thanks.
    – blunders
    Feb 11, 2012 at 11:31
  • 1
    @blunders - table structure, sample data, what criteria you are filtering on...basically ANY information. This is way too generic to be answerable.
    – JNK
    Feb 11, 2012 at 13:12
  • 1
    ...and also what are your concerns? What are you worried about doing or not doing when you update individual rows? And like I asked, how are you identifying the rows that need to be updated? Your sarcasm is noted. You came here for help, right? Why do you want to make it hard and/or annoying for people to try to help you? Feb 11, 2012 at 13:23

1 Answer 1


Without any more specifics, this is all I can offer:

Do not use the visual designers. You may be tempted to use "Edit Top 200 Rows" (previously "Open Table") in Management Studio to "edit" data like a spreadsheet. Resist the temptation. These designers are full of bugs, hold unnecessary locks on the table, pick an arbitrary 200 rows until you massage the SQL query and have to run it again, and just have downright unexpected behavior. (For example, did you know that to enter 1 or 0 into a BIT column, you have to type True or False? Entering 1 or 0 yields an error prior to the SQL Server 2012 version of Management Studio.) Use a query window and write proper UPDATE statements with a WHERE clause - in addition to having full functionality of the DML (including an UPDATE based on a join, which you can't do with the designers), you can also save your queries, put them in source control, etc. The only way you can do that with work you do in a table grid is take a video of what you did.

Verify your work. I typically have something like this:

    SET x = REPLACE(x, 'foo', 'bar'),
    y = x + ' line 2'
FROM dbo.table AS t

This makes it easy to comment out the first two lines and add SELECT so I can check output. So before I run the update, I can sanity check this way:

    x = REPLACE(x, 'foo', 'bar'),
    y = x + ' line 2'
FROM dbo.table AS t

This shows me two things: (1) whether my SET commands altered the column data in the right way, and (2) whether my WHERE clause selected the right rows and the right number of rows.

Protect yourself from fat-fingering. When I'm performing ad hoc updates to critical data, I always start the query with BEGIN TRANSACTION; and then have a commented --ROLLBACK TRANSACTION; and --COMMIT TRANSACTION; statements below, and I highlight and run the COMMIT command only when I'm satisfied the UPDATE affected the right number of rows. In the SO question the guy suggested that you should run it once with ROLLBACK and then once without - I think that's one step too many, especially if the operation takes a long time, holds a lot of locks, etc. You should be able to validate whether it worked or not without performing the operation, rolling it back, and then performing the entire operation all over again. For me the ROLLBACK is there in case I hit F5 at the wrong time, with only part of the query highlighted, etc. Something like this:


/* query goes here */


I actually use Mladen Prajdic's SSMS Tools Pack with a custom template for New Query so that this is there for me always. Yes, it's annoying when I just want to run a quick ad hoc query against non-production instances, but it's quite easy to just Ctrl+A to overwrite it all. It has some other neat features too, and it's free, so worth checking out in any case.

Research the table. You may think you are only updating a single row or a handful of rows, but there may be other side effects you're not aware of. Triggers, indexed views, etc. And depending on how many rows you've updated, you may feel the need to update statistics manually (especially if you've significantly altered the results of a filtered index, something that came up here recently).

Protect yourself from getting fired. We all make mistakes, and even when following these rules you can still cause unintended consequences from ad hoc updates to production data. So do two things, always, when performing these kind of operations:

  1. Take a backup of the data before performing updates. When you can't control who is making updates to the data (or the backup schedule/chain), one trick I have seen is to use log shipping on a delay. So instead of restoring the logs on the replica immediately, you wait 8 hours. This is so that if someone makes a dumb update to the data during the day, there's at least some marginal copy of the data that's not too old and you can get back the rows you affected without much effort. At one shop we called this the "shot yourself in the foot again, eh?" insurance policy. Having to perform a point-in-time restore of an entire database because of one errant update could prove to be quite disastrous in a big system.
  2. Keep your resume up to date. If you run an UPDATE that cripples a customer or destroys your business or causes people a lot of undue work and stress, there's a chance they might start looking for a fall guy. Don't be a fall guy.
  • 1
    @marc_s thank you sir! None of it is from experience, I swear. :-) Feb 12, 2012 at 20:54
  • <hehe> I'm lead to believe that - no doubt! :-9
    – marc_s
    Feb 12, 2012 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.