Changing Datatype from int to bigint for tables containing billions of rows

sql-server, type-conversion

Solution

I ran a test for the ALTER COLUMN that shows the actual time required to make the change. The results show that the ALTER COLUMN is not instantaneous, and the time required grows linearly.

RecordCt    Elapsed Mcs
----------- -----------
      10000      184019
     100000     1814181
    1000000    18410841

My recommendation would be to batch it as you suggested. Create a new column, and pre-populate the column over time using a combination of ROWCOUNT and WAITFOR.

Code your script so that the WAITFOR value is read from a table. That way you can modify the WAITFOR value on-the-fly as your production server starts to bog down. You can shorten the WAITFOR during off-peak hours. (You can even use DMVs to make your WAITFOR value automatic, but this is certainly more complex.)

This is a complex update that will require planning and a lot of babysitting.

Rob

Here is the ALTER COLUMN test code.

USE tempdb;
SET NOCOUNT ON;
GO
IF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.TestTable'))
    DROP TABLE dbo.TestTable;
GO
CREATE TABLE dbo.TestTable (
    ColID   int              IDENTITY,
    ColTest int              NULL,
    ColGuid uniqueidentifier DEFAULT NEWSEQUENTIALID()
);
GO

INSERT INTO dbo.TestTable DEFAULT VALUES;
GO 10000

UPDATE dbo.TestTable SET ColTest = ColID;
GO

DECLARE @t1 time(7) = SYSDATETIME();
DECLARE @t2 time(7);

ALTER TABLE dbo.TestTable ALTER COLUMN ColTest bigint NULL;

SET @t2 = SYSDATETIME();

SELECT
    MAX(ColID)              AS RecordCt,
    DATEDIFF(mcs, @t1, @t2) AS [Elapsed Mcs]
FROM dbo.TestTable;

Problem

I have couple tables with millions, and in some table billions, of rows, with one column as int now I am changing to bigint. I tried changing datatype using SSMS and it failed after a couple of hours as transaction log full. Another approach I took is to create a new column and started updating value from old column to new column in batches, by setting `ROWCOUNT` property to 100000, it works but it very slow and it claims full server memory. With this approach, it may take a couple of days to complete, and it won't be acceptable in production. What is the fast\best way to change datatype? The source column is not identity column and duplicate, and null is allowed. The table has an index on other columns, shall disabling index will speed up the process? Will adding Begin Tran and Commit help?

Original source