Arithmetic overflow on large table

Question

I have a table with 5 billions of rows in SQL Server 2014 (Developer Edition, x64, Windows 10 Pro x64):

CREATE TABLE TestTable
(
  ID BIGINT IDENTITY(1,1),
  PARENT_ID BIGINT NOT NULL,
  CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ID)
);

CREATE NONCLUSTERED INDEX IX_TestTable_ParentId
ON TestTable (PARENT_ID);

I'm trying to apply the following patch:

-- Create non-nullable column with default (should be online operation in Enterprise/Developer edition)
ALTER TABLE TestTable
ADD ORDINAL TINYINT NOT NULL CONSTRAINT DF_TestTable_Ordinal DEFAULT 0;
GO

-- Populate column value for existing data
BEGIN

  SET NOCOUNT ON;

  DECLARE @BATCH_SIZE BIGINT = 1000000;
  DECLARE @COUNTER BIGINT = 0;

  DECLARE @ROW_ID BIGINT;
  DECLARE @ORDINAL BIGINT;

  DECLARE ROWS_C CURSOR
    LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
  FOR 
    SELECT
      ID AS ID,
      ROW_NUMBER() OVER (PARTITION BY PARENT_ID ORDER BY ID ASC) AS ORDINAL
    FROM
      TestTable;

  OPEN ROWS_C;

  FETCH NEXT FROM ROWS_C
  INTO @ROW_ID, @ORDINAL;

  BEGIN TRANSACTION;

  WHILE @@FETCH_STATUS = 0
  BEGIN

    UPDATE TestTable
    SET
      ORDINAL = CAST(@ORDINAL AS TINYINT)
    WHERE
      ID = @ROW_ID;

    FETCH NEXT FROM ROWS_C
    INTO @ROW_ID, @ORDINAL;

    SET @COUNTER = @COUNTER + 1;

    IF @COUNTER = @BATCH_SIZE
    BEGIN
      COMMIT TRANSACTION;
      SET @COUNTER = 0;
      BEGIN TRANSACTION;
    END;

  END;

  COMMIT TRANSACTION;

  CLOSE ROWS_C;
  DEALLOCATE ROWS_C;

  SET NOCOUNT OFF;

END;
GO

-- Drop default constraint from the column
ALTER TABLE TestTable
DROP CONSTRAINT DF_TestTable_Ordinal;
GO

-- Drop IX_TestTable_ParentId index
DROP INDEX IX_TestTable_ParentId
ON TestTable;
GO

-- Create IX_TestTable_ParentId_Ordinal index
CREATE UNIQUE INDEX IX_TestTable_ParentId_Ordinal
ON TestTable (PARENT_ID, ORDINAL);
GO

The aim of patch is to add a column, called ORDINAL, which is an ordinal number of the record within the same parent (defined by PARENT_ID). The patch is run using SQLCMD.

The patch is done is this way for a set of reasons:

• Table is too large to run a single UPDATE statement on it (takes enormous amount of time and space in transaction log/tempdb).
• Batch updates using a single UPDATE statement with TOP n rows are not simple to implement (if we update table in, say, 1m rows batches, 1000001st row may belong to the same PARENT_ID as 1000000th which will lead to wrong ordinal number assigned to 1000001st record). In other words, SELECT statement run in cursor should be run once (without paging) or more complicated operations (joins/conditions) should be applied.
• Adding NULL column and changing it to NOT NULL later is not a good solution since I use SNAPSHOT isolation (full table update will be performed on altering column to be NOT NULL).

The patch works perfect on a small database with a few millions of rows, but, when applied to the one with billions of rows, I get:

Msg 3606, Level 16, State 2, Server XXX, Line 22
Arithmetic overflow occurred.

My first guess was ORDINAL value is too big to fit into TINYINT column, but this is not the case. I created a test database with similar structure and populated with data (more than 255 rows per parent). The error message I get is still arithmetic exception, but with different message code and different wording (explicitly saying it can't fit data into TINYINT).

Currently I have a couple of suspicions, but I haven't managed to find anything that could help me:

• CURSOR is not able to handle more than MAX(INT32) rows.
• SQLCMD imposed limitations.

Do you have any ideas on what could the problem be?

Answer 1

How about using a While loop but making sure that you keep the same parent_ids together:

DECLARE @SegmentSize BIGINT = 1000000
DECLARE @CurrentSegment BigInt = 0

WHILE 1 = 1
BEGIN

    ;With UpdateData  As
    (
        SELECT  ID AS ID,
                ROW_NUMBER() OVER (PARTITION BY PARENT_ID ORDER BY ID ASC) AS ORDINAL
        FROM TestData
        WHERE ID > @CurrentSegment AND ID <= (@CurrentSegment + @SegmentSize)
    )
    UPDATE TestData 
        SET Ordinal = UpdateDate.Ordinal
    FROM TestData
    INNER JOIN UpdateData ON TestData.Id = UpdateData.Id    

    IF @@ROWCOUNT = 0
    BEGIN
        BREAK
    END

    SET @CurrentSegment = @CuurentSegment + @SegmentSize
END 

EDIT - Amended to segment on Parent_Id as per request. This should be reasonably quick as Parent_id is indexed (added Option(Recompile) to ensure that actual value is used for the lookup. Because you are not updating the whole table this will limit the Transaction Log growth!

DECLARE @SegmentSize BIGINT = 1000000
DECLARE @CurrentSegment BigInt = 0

WHILE 1 = 1
BEGIN

    ;With UpdateData  As
    (
        SELECT  ID AS ID,
                ROW_NUMBER() OVER (PARTITION BY PARENT_ID ORDER BY ID ASC) AS ORDINAL
        FROM TestData
        WHERE Parent_ID > @CurrentSegment AND
              Parent_ID <= (@CurrentSegment + @SegmentSize)
    )
    UPDATE TestData 
        SET Ordinal = UpdateDate.Ordinal
    FROM TestData
    INNER JOIN UpdateData ON TestData.Id = UpdateData.Id
    OPTION (RECOMPILE)  

    IF @@ROWCOUNT = 0
    BEGIN
        BREAK
    END

    SET @CurrentSegment = @CuurentSegment + @SegmentSize
END