Row Compression for Faster Data Type Changes

Things don’t always go as planned. This quote can apply to many topics, including sizing up your data types. When you started out with a new table, there were no concerns with SMALLINT—until you realized you needed INT. Or maybe you were rolling along fine with INT until you needed to up your game to BIGINT.

If you don’t have much data in your tables and you need to change data types, you may not have much to be concerned about when it comes to the impact of such a change. But problems can arise when you have a table full of data and want to shave off some time to make a data type change as smooth as possible.

Regardless of the reasoning and size, there’s a trick that you can use to make these changes less painful: row compression. Let’s take a look at the difference between switching from INT to BIGINT when using row compression and when not using row compression.

In the Big Leagues

Let’s start out with a table full of INT data.

CREATE TABLE CompressionTest
(
	ID INT IDENTITY(1,1),
	RandomNumber INT
);
GO

INSERT INTO CompressionTest (RandomNumber)
SELECT FLOOR(RAND()*(2000000000)+1);
GO 10000

Let’s run the following so we can track some stats:

SET STATISTICS IO, TIME ON;

Now, if we make the change from INT to BIGINT without any type of compression, we’ll see these stats:

ALTER TABLE CompressionTest ALTER COLUMN RandomNumber BIGINT;

Can we improve that? Let’s change back to INT and put row compression in place:

ALTER TABLE CompressionTest ALTER COLUMN RandomNumber INT;

ALTER TABLE CompressionTest REBUILD WITH (DATA_COMPRESSION = ROW);

And now, with row compression in place, let’s try that INT to BIGINT change again:

That’s a pretty significant difference.

Row Compression Helps

The example above doesn’t deal with too much data, but the difference with row compression is clear. If you have an upcoming data type change to make, do some testing with row compression to minimize any downtime.

Thanks for reading!