Changing Data Types

Question

Changing Data Types

Steve Jones - SSC Editor

SSC Guru

Points: 741475
More actions
March 18, 2026 at 12:00 am

#4745165

Comments posted to this topic are about the item Changing Data Types

Viewing 3 posts - 1 through 3 (of 3 total)

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

m60freeman SSC-Addicted Points: 480 More actions · Answer 1

I've seen this most often in Microsoft code-first projects where either no data modelling was done before development started, or no DBA reviewed the design before the first version of the application was deployed to production.

At that point, management often decides it is too risky or involves too much development work to change the data type, unless the performance impact of having the improper data type is sufficiently painful.

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

We made some datatype changes in older tables that were created by people that probably should not have been allowed near a computer. These were the type of people that would quote Knuth's parable about how "Pre-optimization is the root of all evil" and use it as an excuse for never taking the time for proper design and original implementation.

Such changes on a couple of billion row tables would have caused log file explosions. So... we did the ol' "swap'n'drop" method kind of like SSMS does it. Prior to 2016, it was an easy thing because TF 1117 wasn't burned into TEMPDB on a permanent basis and so we could make it so only one of the TempDB files would grow when doing even a minimally logged insert into a table with SET IDENTITY INSERT ON, which forces a full table sort in TEMPDB. The total "down time" for the table rename was measured in milliseconds.

Nowadays, we have to change out the IDENTITY column for a regular INT or BIGINT column and set it up for a default to use a new SEQUENCE instead. The good thing there is, no more huge sort in TEMPDB.

In either case, though, you need to have the free space to at least temporarily hold a copy of the table. That can be a bit of a pain when someone decides that a 147 column table is appropriate to hold a billion rows. In those cases, it's also a good time to consider some form of partitioning.

As a bit of a sidebar, I do wish that Microsoft would allow for a deliberate use of a no-log option.

--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)