Post reply

Add a Column on a Production table with 90M rows

  • May 19, 2009 at 5:49 am

    #134428

    Hi All

    I have a production table with 90 Million rows, i need to add a new column to that table, The problem is that the new column must have a default value as 0 and its should have a not null Constraint.

    The problem i am facing is i cant more down time.

    The steps is followed is

    First create the Column

    Alter table TableA Add ColA Bigint

    Then Update the column

    Update TableA set ColA=0

    Then set the constraint

    Alter table TableA ADD Constraint CF_tableA_ColA Default (0) for ColA

    When updating its talking more time.

    Is there any other way of doing this, please let me know your suggestions

    Thanks in advance for all your posts

    Cheers

    🙂

  • May 19, 2009 at 6:28 am

    #996873

    Well, you can save some time by doing it all at once

    Alter table TableA Add ColA Bigint NOT NULL Constraint CF_tableA_ColA Default (0)

    However 90M rows will take some time to do.

    Note also this may well fragment the table significantly if the pages are full, and you'll get a lot of logging.

    Other options would include breaking it up ... for instance

    Alter table TableA Add ColA Bigint

    Then Update the column

    SET ROWCOUNT 1000000 (or other suitable value)

    Update TableA set ColA=0 WHERE ColA IS NULL

    Repeat until no rows are affected

    Then set the constraint

    Alter table TableA ADD Constraint CF_tableA_ColA Default (0) for ColA

    Tim

    .

  • May 19, 2009 at 6:39 am

    #996879

    Thanks Tim, I tried the first ones, it takes more than 15 minutes ( tested on a test database), but this involves table locking , this table is been used by a app which reads updates at least 2 per sec, loop is the best ones, i will put a delay on the loop, so it waits for 5 secs and then updates the next batch, to avoid locking 🙂 ,

    How much time will it take approx to update 90M rows??

  • May 19, 2009 at 6:47 am

    #996887

    Depends on the hardware.

    Adding a null column should be quick as only the schema is marked up.

    Once you start adding the data, the column will get populated and the page splits will occur.

    Should be fairly linear I think so if the first 1M rows take 30 seconds 90M will take 45 minutes (in 30 second chunks).

    Sounds like you've considered the locking issue, but you should also consider that there won't be an index on your new column so you will end up loading the table into data cache which could hit other work on the server.

    Also is the table ends up highly fragmented this may affect other queries using it until it is reindexed.

    Tim

    .

  • May 19, 2009 at 6:52 am

    #996895

    Its a production Server dedicated to SQL, with 4 processor and 6Gb RAM (fairly small), If i put a delay within the chunk update, will there be less locking issues, or it will be the same?

  • May 19, 2009 at 6:55 am

    #996903

    Less, it will gve other processes time to get in and complete.

    It's a bit of a balance how big the chunks should be and how big the gap should be. Since you can change it as you go along, monitor the effect carefully and stop if you need to.

    sp_who2 active

    is as good a way as any for a quick assessment

    Tim

    .

  • May 19, 2009 at 6:57 am

    #996907

    Thanks Tim :-), Will try and let you know 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply