Table size growth when converting columns – VARCHAR to INT

How many times have you seen VARCHAR columns holding INT (or NULL) values only ?

I recently had to deal with some columns like that, and decided it would be good to change them to INT. So I went and changed the columns, but after checking the table size, I noticed, table actually increased in size – well that was unexpected to me. I’ve expected table to keep its size, as in my case, column was VARCHAR(10) and all values were more than 4 chars each – between 7 and 9.

Let me walk you through this.

And now, when checking the size it says:

Ok, so that’s 496 KB. Now if I change the column to be INT, let’s see how much space table will take:

Ok, that’s interesting, now data size almost doubled in size 984 KB, imagine what this can do to a table with 100 million rows, and if you are converting more than one column – your table will grow a lot in size, index fragmentation will occur and you may end up with a file growth you didn’t actually expected/planned for – the list might go on from here.

And there’s the fragmentation.

It seems, internally, sql server adds another column to the table, then converts the data, and drops the column, which is exactly what it does:

Following this finding, it might be a good call to rebuild the clustered index after a column is converted, and of course, NEVER agree to this in the first place.

As a side note, this doesn’t apply when converting from CHAR to INT.

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+

Leave a Comment