Geek asks and answers

Change data type from int to bigint for tables containing billions of rows

I have a couple of tables with millions, and in some tables, rows, with one column as int, now I go to bigint. I tried changing the data type using SSMS, and it failed after a couple of hours when the transaction log is full.

Another approach I took was to create a new column and start updating the values ​​from the old column to the new column, setting the property ROWCOUNTto 100000, but it works very slowly and requires full server memory. With this approach, it can take several days, and it will not be acceptable in production.

What is a quick \ best way to change data type? The source column is not an identifier column and is duplicated, and null is allowed. Does the table have a pointer to other columns, disabling the index will speed up the process? Will Begin Tran and Commit help be added?

+3
source share
2 answers

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

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

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

script, WAITFOR . , WAITFOR " ", . WAITFOR . ( DMV, WAITFOR , , , .)

, .

Rob

ALTER COLUMN.

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;
+7

alter table <table> alter column <column> bigint null . - ,

, , , , -

0

More articles:


All Articles