Changing Data Types

Question

Changing Data Types

Steve Jones - SSC Editor

SSC Guru

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

#4745165

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

Viewing 5 posts - 1 through 5 (of 5 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)

David.Poole SSC Guru Points: 76362 More actions · Answer 3

I am using Snowflake, which has several timestamp data types. It's a legacy codebase, so usage across the codebase is a bit random.

TIMESTAMP_LTZ stores UTC, but any interaction will be as if you were in your local time zone.
TIMESTAMP_TZ stores UTC but with a timezone offset
TIMSTAMP_NTZ stores the date and time without any timezone information.

There are also places where the FLOAT, DOUBLE & REAL datatypes are used. This is where I'm expecting pain points. The use of these is something my predecessors got away with, until they didn't.

I've got it on my list to standardise this.

Snowflake can ingest JSON data into a variant column. On the plus side, the column compresses incredibly well. The many downsides are

Operations on the data in that column are non-sargable
Snowflake is expensive for cracking open JSON.
Variant columns are not cached

We are going to migrate away from the JSON -> variant column ingestion approach in favour of pre-processing the JSON outside of Snowflake. This represents a huge change, so a lot of planning is going to go into it.

On SQL Server 2012, an ORM-inflicted design used BIGINT for any integer. I switched these to the appropriate integer type as this was metadata only. The nature of the change did not save any space. Migrating the data to a new table with the appropriate types did give us space savings.

Increasing to a higher integer type is a bigger issue, and anything that requires migrating from floating-point types needs a lot of care.

LinkedIn Profile

dplaut 49149 SSC Eights! Points: 904 More actions · Answer 4

We import a lot of data from other systems. I run into the situation of "just because they used [type x] doesn't mean that we have to." Especially bigint. If that field will never need bigint, I'll change the field type in a table that someone else created. Sometimes tinyint is all that is needed.