how to add a column to table with billion records without bringing down the application running on it

Assume we have no parallel machine for the application to be pointing at, if the development works is going on Primary DB. You can also answer the query considering parallel machine might be there.

more ▼

asked Mar 13 at 04:05 PM in Default

avatar image


(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

First of all - regardless of the advice you get, test your scenario on a test box. Restore the production database and test it out, without having to throw up out of nervousity :)

Anyway: Add the column as a NULL-able column will have considerably less impact than adding a column which must have an initial value. If you need an initial value, add it first as NULLable, update the existing rows (in batches) and then change to column to NOT NULL. Add a default value for the column already initially, so that new rows get the default value instead of being NULL. That way you can change to NOT NULL later on, when you have updated all rows with a value for the new column.

more ▼

answered Mar 13 at 07:22 PM

avatar image

Magnus Ahlkvist
22.5k 20 43 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 13 at 04:05 PM

Seen: 13 times

Last Updated: Mar 13 at 07:22 PM

Copyright 2018 Redgate Software. Privacy Policy