When updating INT to BIGINT do rows with nulls matter

Question

When updating INT to BIGINT do rows with nulls matter

migurus

SSC-Addicted

Points: 456
More actions
August 21, 2020 at 7:32 pm

#3782390

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?

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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 3 years, 8 months ago by Lynn Pettis.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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)

migurus SSC-Addicted Points: 456 More actions · Answer 3

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

migurus SSC-Addicted Points: 456 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.

DesNorton SSC-Insane Points: 24278 More actions · Answer 9

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 3 years, 8 months ago by DesNorton.

How to post data/code on a forum to get the best help.