Changing a column from int to bigint, without downtime

Andy

5 years ago

Changing a column from int to bigint has gotten a lot easier since I started working on SQL Server back at the turn of the century. SQL Server 2016 introduced the ability to do ALTER TABLE...ALTER COLUMN as an online operation using the WITH (ONLINE=ON) syntax in Enterprise Edition. This wonderful syntax now allows you to alter a column from int to bigint without causing major blocking. The int to bigint conversion is one of the most popular data type changes I see–A developer inevitably creates the table thinking they will never have more than 2 billion rows… then some years or months later 2 billion becomes a reality.

The DBA is left with the task of implementing that data type change, and now that it has almost 2 billion rows, it’s a well-established table and uptime during the change is a major consideration.

Let’s change some data types

We’ll create a “big” table to test with

I’m going to insert about 5 million rows in my table. On my laptop, that’s “big enough” to make the various scripts slow enough to measure, but fast enough to be reasonable for a demo. (If you want to exhaust the int limit for your testing, just change the last line in this code from GO 5 to GO 2000.) On my laptop, this takes 90 seconds to run:

DROP TABLE IF EXISTS dbo.BigTable;
GO
CREATE TABLE dbo.BigTable (
    ID          int identity(1,1),
    AnotherID   int,
    Filler      nvarchar(1000),
    CONSTRAINT  PK_BigTable PRIMARY KEY CLUSTERED (ID)
    );
GO
INSERT INTO dbo.BigTable (AnotherID, Filler)
SELECT o.object_id, REPLICATE('z',1000)
FROM sys.objects o, sys.objects o1, sys.objects o2;
GO 5

I did this in a brand-new database on SQL Server 2019, and it generates a table with 4,851,495 rows, and takes up 9.29GB.

Let’s try that online alter

Thanks to the ALTER TABLE...ALTER COLUMN...WITH (ONLINE=ON) syntax. I should be able to change the AnotherID column to be a bigint without any fuss:

ALTER TABLE dbo.BigTable ALTER COLUMN AnotherID bigint WITH(ONLINE=ON);

That took about 2 minutes to run. It also caused my transaction log to grow to nearly 13GB. If other transactions were running during the ALTER, the transaction log would have grown to the cumulative total of both my alter, and everything else that ran during the alter. The alter is pretty easy, and it’s online, so the transaction log bloat is a pretty decent cost to pay. Except…let’s do the math: 13GB (transaction log usage) ÷ 4,851,495 rows (in my sample table) × 2,147,483,647 rows (the int limit) = 5.6 TB of transaction log growth. Yuk. That’s a lot of extra disk space for the convenience.

Is there a “Go Fast” button?

Data compression can help us tons here. Let’s set up the same exact test case, except this time, we’ll specify the clustered index to use index compression. I’m going to use row compression, but page compression would work, too:

DROP TABLE IF EXISTS dbo.BigTable;
GO
CREATE TABLE dbo.BigTable (
    ID          int identity(1,1),
    AnotherID   int,
    Filler      nvarchar(1000),
    CONSTRAINT  PK_BigTable PRIMARY KEY CLUSTERED (ID) WITH (DATA_COMPRESSION = ROW) --now with compression
    );
GO
INSERT INTO dbo.BigTable (AnotherID, Filler)
SELECT o.object_id, REPLICATE('z',1000)
FROM sys.objects o, sys.objects o1, sys.objects o2;
GO 5

And now, I am going to do the exact same ALTER that previously took 2 minutes and 13GB of transaction log:

ALTER TABLE dbo.BigTable ALTER COLUMN AnotherID bigint WITH(ONLINE=ON);

This time, it took only 4 milliseconds, and generated a mere 8 bytes transaction log. Data Compression is pretty awesome, isn’t it? Part of the data compression magic is to essentially treat fixed-length numbers as variable-length to save on storage–so the ints stay as ints, and the ALTER is just a metadata change.

The perceptive reader has probably noticed that my sample table has two int columns: ID & AnotherID. I’ve just been messing with AnotherID so far. Let’s also alter that ID column now:

ALTER TABLE dbo.BigTable ALTER COLUMN ID bigint WITH(ONLINE=ON);

Unfortunately, SQL Server won’t let me do that. I get this error:

Msg 5074, Level 16, State 1, Line 1
The object ‘PK_BigTable’ is dependent on column ‘ID’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ID failed because one or more objects access this column.

The Primary Key blocks the alter

The primary key constraint is dependent on the ID column, so SQL Server blocks the column alter due to the potential cascading side effect on the referencing objects.

If we can’t alter the column with the PK in place, we could drop the PK, do the alter, and put the PK back. Since it’s a Clustered PK, that would mean re-writing the table as a heap. Any non-clustered indexes would need to be re-written to reference the Row ID (RID), since the non-clustered index could no longer reference the clustering key. That’s gross. Then, after we do the alter, adding the PK back again would need to re-write the entire table again to reverse the RID-clustering key shenanigans. That’s doubly gross. And, of course, while all this is happening, that index is going to be missing for anyone else who wants to query this table, and the missing constraint means bad data could sneak in. That’s gross, too.

We’ll have to migrate to a new table instead

Dropping & re-creating that clustered PK just isn’t going to be a good option. Instead, let’s create a new table and migrate to it. We’ll create a new table, shuffle data into it, then swap that table into place. We’ll do it in a way that the table remains available & online the entire time (minus some very brief blocking at the very end).

Let’s reset our BigTable to our baseline. Note, I’m going to keep the row compression (because I love compression):

DROP TABLE IF EXISTS dbo.BigTable;
GO
CREATE TABLE dbo.BigTable (
    ID          int identity(1,1),
    AnotherID   int,
    Filler      nvarchar(1000),
    CONSTRAINT  PK_BigTable PRIMARY KEY CLUSTERED (ID) WITH (DATA_COMPRESSION = ROW) --now with compression
    );
GO
INSERT INTO dbo.BigTable (AnotherID, Filler)
SELECT o.object_id, REPLICATE('z',1000)
FROM sys.objects o, sys.objects o1, sys.objects o2;
GO 5

First, we’ll create the new table. The only material differences are that int columns are now both bigint, and I’ve seeded the IDENTITY to start at the next number beyond the int limit. Note that the constraint names need to be unique, too, so we’ll append both the the table & PK name with _new.

CREATE TABLE dbo.BigTable_new (
    ID          bigint identity(2147483648,1), --bigint and a big seed 
    AnotherID   int,
    Filler      nvarchar(1000),
    CONSTRAINT PK_BigTable_new PRIMARY KEY CLUSTERED (ID) WITH (DATA_COMPRESSION = ROW)
    );

We can’t stop users from continuing to insert/update/delete on dbo.BigTable, so we’ll use a trigger to keep the new table in sync as the data changes. We’ll create one trigger that fires on insert, update, and delete. My trigger consists of just two statements.

A delete (this has to be first)

On insert, there’s nothing in the _new table, so this delete is a no-op
On delete, this will do the delete from the _new table. If the rows haven’t yet been migrated from old to new, there’s nothing to delete, so this is a no-op
On update, this will delete the rows from the _new table (and we’ll re-insert them in the next statement). If the rows haven’t yet been migrated from old to new, there’s nothing to delete, so this is a no-op

An insert (this has to be second)

On insert, this will insert the identical row into the _new table
On delete, there’s nothing to insert to the _new table, so this insert is a no-op
On update, this will insert the proper row to the _new table (we previously deleted the old version, if it existed). If the rows hadn’t previously been migrated from old to new, we just migrated the rows!

CREATE OR ALTER TRIGGER dbo.SyncBigTables  
     ON dbo.BigTable  
     AFTER INSERT, UPDATE, DELETE   
AS  
SET NOCOUNT ON;

DELETE n
FROM dbo.BigTable_new AS n
JOIN deleted d ON d.ID = n.id; --join on that PK

SET IDENTITY_INSERT dbo.BigTable_new ON;
INSERT INTO dbo.BigTable_new (ID, AnotherID, Filler)
SELECT i.ID, i.AnotherID, i.Filler
FROM inserted AS i;
SET IDENTITY_INSERT dbo.BigTable_new OFF;
GO

Let’s test out this trigger!

Table is empty; we haven’t migrated anything:

SELECT * FROM dbo.BigTable_new;

Deletes on the old table still work; nothing to delete in new table:

SELECT * FROM dbo.BigTable WHERE ID = 1;
SELECT * FROM dbo.BigTable_new WHERE ID = 1;

DELETE dbo.BigTable WHERE ID = 1;

SELECT * FROM dbo.BigTable WHERE ID = 1;
SELECT * FROM dbo.BigTable_new WHERE ID = 1;

Updates will magically migrate rows over to the new table as they change:

SELECT * FROM dbo.BigTable WHERE ID = 2;
SELECT * FROM dbo.BigTable_new WHERE ID = 2;

UPDATE dbo.BigTable SET Filler = 'updated' WHERE ID = 2;

SELECT * FROM dbo.BigTable WHERE ID = 2;
SELECT * FROM dbo.BigTable_new WHERE ID = 2;

Inserts on the old table get inserted nicely on the new table:

DECLARE @ID bigint;
INSERT INTO dbo.BigTable (Filler)
VALUES ('Brand New Row');

SELECT @ID = SCOPE_IDENTITY();

SELECT * FROM dbo.BigTable WHERE ID = @ID;
SELECT * FROM dbo.BigTable_new WHERE ID = @ID;

Let’s migrate data

Changes to the old table are slowly keeping the new one updated (and are also slowly migrating new data to the new table). We need to migrate the rest of the rows over to the new table. Here’s the algorithm to migrate data:

We’ll batch our inserts into the _new table. Batching minimizes the transaction log bloat, and also allows for work to be partially committed & resumed later
If a row already exists in the _new table, we can skip it–the trigger is keeping it updated.
We’re going to ascend up the ID column. Ever-increasing keys are wonderful for this. (If you don’t have an identity column, the leading column of the clustered index is the best column to use in most cases).
We’ll keep track of where we are as we ascend, so we can easily resume our work if we have to stop.
We will cache the maximum value of the ID column now. We will only need to migrate the rows below the current maximum, because new rows will be migrated by our trigger. Ascending from MIN to MAX is better than constantly checking @@ROWCOUNT–if you have large enough gaps in your identity column, you could hit a range with zero rows and terminate prematurely.

We’ll use dbo.WhereAmI to track our progress as we ascend the identity column.

CREATE TABLE dbo.WhereAmI (
    TableName   nvarchar(128),
    LastID      bigint,
    MaxID       bigint
    CONSTRAINT PK_WhereAmI PRIMARY KEY CLUSTERED (TableName)
    );

INSERT INTO dbo.WhereAmI (TableName, LastID, MaxID)
SELECT 'BigTable', MIN(ID), MAX(ID) FROM dbo.BigTable;
GO

Time for our migration script to move the data. When you run this, you can stop & restart it as often as you need (or as often as SSMS crashes). If you stop it, you’ll only have to roll back the last batch.

SET NOCOUNT ON;

DECLARE @BatchSize smallint = 1000;
DECLARE @LastID bigint;
DECLARE @MaxID bigint;


SELECT @LastID = LastID,
       @MaxID  = MaxID
FROM dbo.WhereAmI
WHERE TableName = 'BigTable';

WHILE @LastID < @MaxID
BEGIN
    SET IDENTITY_INSERT dbo.BigTable_new ON;
    INSERT INTO dbo.BigTable_new (ID, AnotherID, Filler)
    SELECT o.ID, o.AnotherID, o.Filler
    FROM dbo.BigTable AS o
    WHERE o.ID >= @LastID
    AND o.ID < @LastID + @BatchSize  --Yeah, we could do a TOP(@BatchSize), too.
    AND NOT EXISTS (SELECT 1 FROM dbo.BigTable_new AS n WHERE n.ID = o.ID);
    SET IDENTITY_INSERT dbo.BigTable_new OFF;
    
    SET @LastID =  @LastID + @BatchSize;
    UPDATE w
    SET LastID =  @LastID
    FROM dbo.WhereAmI AS w
    WHERE w.TableName = 'BigTable';

END;
GO

At this point, your data is totally in sync. We’ve moved all the existing rows, and as new data continues to flow into dbo.BigTable, the trigger keeps the two tables completely in sync.

The last step is the one that is slightly disruptive. The change itself is very quick–however, because it requires a schema lock to rename the table, you could cause blocking on a very busy table. The up side is that there’s no pressure to rush into the next step–we can leave things in place here for days (or even weeks) and do the final step when we have a maintenance window.

Now to rename the table. I suggest doing this in a transaction to ensure that the table is never “missing” for other users (we’ll block those users instead of them getting errors), and it will also ensure that you roll back both rename operations if the second one fails.

Note that I’m using sp_rename here. Kendra Little (blog|twitter) has a great post about using SWITCH PARTITION instead of sp_rename. Unfortunately, the data type mismatch on the tables we’re switching makes that method problematic. You should still check out Kendra’s article because it’s a great technique, even if I’m not using it here.

BEGIN TRAN
    BEGIN TRY
        EXEC sp_rename 'dbo.BigTable','BigTable_old';
        EXEC sp_rename 'dbo.BigTable_new','BigTable';
        EXEC sp_rename 'PK_BigTable','PK_BigTable_old';
        EXEC sp_rename 'PK_BigTable_new','PK_BigTable';
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW 50000, 'Oops Something didn''t work. Rolling back.',1;
    END CATCH
COMMIT;

At this point, your new table is live, and everything is migrated! Yay!

The last step will be to drop the original, _old, table:

DROP TABLE dbo.BigTable_old;

fin.

Additional Reading

Sometimes you CAN upsize a column in-place by Aaron Bertrand