Changing a column from int to bigint, without downtime
Tags: datacompression, performance, tsql, sqlserver
Published: 2020-01-01 13:45:25

---
title: Changing a column from int to bigint, without downtime
published: true
date: 2019-12-11 13:00:41 UTC
tags: DataCompression, Performance, TSQL, SQLserver
canonical_url: https://am2.co/2019/12/changing-a-column-from-int-to-bigint-without-downtime/
---

[![](http://am2.co/wp-content/uploads/AlterOnline2.png)](https://www.youtube.com/watch?v=YHKGUu2gaOg)  
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:

```sql
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:

```sql
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](https://am2.co/category/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](https://am2.co/2016/02/data-compression-how-row-works/), but [page compression](https://am2.co/2016/02/data-compression-how-page-works/) would work, too:

```sql
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:

```sql
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 `int`s stay as `int`s, 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:

```sql
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):

```sql
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`.

```sql
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!

```sql
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:

```sql
SELECT * FROM dbo.BigTable_new;
```

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

```sql
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:

```sql
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:

```sql
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.

```sql
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.

```sql
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](http://sqlworkbooks.com/)|[twitter](https://twitter.com/Kendra_Little)) has [a great post](https://littlekendra.com/2017/01/19/why-you-should-switch-in-staging-tables-instead-of-renaming/) 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.

```sql
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:

```sql
DROP TABLE dbo.BigTable_old;
```

fin.

The post [Changing a column from int to bigint, without downtime](https://am2.co/2019/12/changing-a-column-from-int-to-bigint-without-downtime/) appeared first on [Andy M Mallon - AM²](https://am2.co).