Adding Columns to a SQL Server Table for Complete and Utter Retards

June 14, 2005

Let's say you have a SQL Server database table with a couple million records in it. Now let's say for some reason you want to add a column to it. Maybe the client wanted to ask another insipid question of their users, maybe management decreed that more tracking be done, maybe something got a new bell and/or whistle. Doesn't matter why, you've just got to add that column.

If you're like me, your first inclination would be to simply fire up Enterprise Manager, select Design Table, and add the column. For added bonus points you might slide that new column into a more logical place than the tail-end of the table.

And if you do that, like me, you'll be going in to work at 9:00 at night to fix the table you just fouled up, because when EM tries to add that column in the middle of the table it's going to lock out writing any data. That lock also appears to survive rebooting the machine.

The fix in this case is exactly the same as what you (and I) should have done in the first place. Here's how you keep yourself from driving back to the office at 8:30 PM:

  1. In EM, right-click the table you want to add to, and choose "All tasks" -> "Create Script".
  2. In the second tab (I can't remember its name right now) make sure only "Generate CREATE statement" is checked. Save the SQL file to someplace convenient.
  3. Open Query Analyzer and paste the CREATE statement into the window. Add your new column wherever you feel it appropriate. Don't run it yet.
  4. Back in EM, rename the table. Adding "_old" to the end usually works well.
  5. Now run the script in Query Analyzer. Your total downtime should have been about 2 seconds.
  6. Move triggers (if you have them) from old table to new by doing the right-click, edit, delete, execute dance.
  7. Close QA and go back to EM.
  8. Make sure that any automatic processes are placing data in the table.
  9. Run a DTS export from YourTable_old to YourTable. Check the transform, but it should just say <ignore> next to the new column.
  10. Let the import run. For a couple million records you'll have plenty of time for coffee.
  11. Verify that the data imported to the new table properly.

If you do that to start with you'll save yourself a lot of trouble. If you do that second you'll at least look like you know how to fix a major cock-up. I think option A is the better one though.

◀ June 13, 2005June 16, 2005 ▶