When updating INT to BIGINT do rows with nulls matter

  • Table has 30 million rows, 11 GB in size, I need to alter one column from INT into BIGINT. I need to keep downtime to 10 min max.

    Presently there are some values in that column, mostly zeroes, these values are no longer needed.

    If I update the column to be all nulls before altering will my alter statement run fast? in other words, does  the content of the column affect the speed of alter operation?

  • If the data is no longer needed, then create the new column with a new name, drop the old column, then rename the new column to name of the old column.

    Oh, when you create the new column declare as allowing nulls or provide a default constraint.

     

    • This reply was modified 1 year, 5 months ago by  Lynn Pettis.
  • migurus wrote:

    Table has 30 million rows, 11 GB in size, I need to alter one column from INT into BIGINT. I need to keep downtime to 10 min max.

    Presently there are some values in that column, mostly zeroes, these values are no longer needed.

    If I update the column to be all nulls before altering will my alter statement run fast? in other words, does  the content of the column affect the speed of alter operation?

    Changing the 0's to NULLs isn't going to help at all and may screw up code that uses the 0's as criteria not to mention future code could be a little more complex or even Non-SARGable if you change the 0's to NULLs.

    Also, changing the datatype to a larger datatype may result in massive fragmentation due to the expansion of the individual rows even though the data itself doesn't change.

    My recommendation would be to copy a half million rows from that table to a test table, setup all the same indexes, and test for these and other aspects so you know for sure.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • As I have said - old values no longer needed, this recommendation is not really applicable

  • My question still stands - does column content (some values vs. all nulls) matter when altering column type from INT to BIGINT?

  • migurus wrote:

    As I have said - old values no longer needed, this recommendation is not really applicable

    Actually, if you go back and read, I'm talking about the mess you might make if you change 0's to NULLs because you might not actually know if the old values are actually used or not.  Any values left in the original int column will also be present in the column after you update it to big int, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • migurus wrote:

    My question still stands - does column content (some values vs. all nulls) matter when altering column type from INT to BIGINT?

    My answer still stands... it won't bother your change from INT to BIGINT but it may bother the hell out of some code you have if you change the 0's to NULLs. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • p,s,  Just because I'm giving you an answer you don't want to hear or maybe don't understand the ramification of, you should look into what I've said because it's going to be a real embarrassment for you if you break something that you didn't expect to break, especially on a 30 million row table that you might have to restore the database to get a copy so you can fix things if my cautionary tale turns out to be true.

    If it were me, I would NOT change the 0's to NULLs during this evolution on a bet.

    I'll also state it would take you just minutes to do a small test for this conversion (which you should) on a couple of hundred thousand rows in a test table to make damned sure because even monster forum Ninjas have been known to be incorrect.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Also, I answered saying create a NEW column with a new name using the BIGINT data type with nullable column or a default value, drop the old column, then rename the new column to the old column name.

     

  • If you use the SSMS table designer to change the field from INT to BIGINT, then generate the change script, you will see that under the hood, the table is actually dropped and recreated.  So NULL or 0 will have no impact.

    NOTE:  Doing an in place update will take a long time, and it will blow out your log files.  Depending on the size of your log files, you may even run out of drive space, and do a full roll back.  There is no guarantee that you can pull it off in under 10 mins.

     

    • This reply was modified 1 year, 4 months ago by  DesNorton.

Viewing 10 posts - 1 through 9 (of 9 total)

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