Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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".

share|improve this question
1  
You need to be more specific. How are you identifying your "handful of rows"? –  Aaron Bertrand Feb 11 '12 at 2:04
2  
...a LOT more specific. –  JNK Feb 11 '12 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 '12 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 '12 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? –  Aaron Bertrand Feb 11 '12 at 13:23
show 8 more comments

1 Answer

up vote 7 down vote accepted

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:

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

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:

--UPDATE t
    --SET
SELECT 
    x = REPLACE(x, 'foo', 'bar'),
    y = x + ' line 2'
FROM dbo.table AS t
WHERE ID = 1;

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:

BEGIN TRANSACTION;

/* query goes here */

-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

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.
share|improve this answer
    
+1 excellent advice - as always! –  marc_s Feb 12 '12 at 20:53
1  
@marc_s thank you sir! None of it is from experience, I swear. :-) –  Aaron Bertrand Feb 12 '12 at 20:54
    
<hehe> I'm lead to believe that - no doubt! :-9 –  marc_s Feb 12 '12 at 20:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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