Converting INT to Unsigned INT

Question

I have a large table in prod with INT datatype and it is both primary key and identity column(1,1).The number of rows is 2147479257. During the daily job run the ETL failed because of arithmetic overflow error,as it cant fit any more rows in to the destination table.

Can you please how can i change the column to unsigned int.

What about changing it to bigint? – Sam M Jul 06 '15 at 23:09 — Sam M, Jul 06 '15 at 23:09

score 0 · Accepted Answer · answered Jul 06 '15 at 23:06

0

Change the datatype to bigint.

bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

answered Jul 06 '15 at 23:06

Greg

3,861
3
23
58

score 0 · Answer 2 · answered Jul 07 '15 at 02:43

You've overflown an int, which means you have approximately 2B rows in the table. By switching to an unsigned int, you hope to gain another 2 billion rows of addressable space. The problem with this approach is that SQL Server does not support an unsigned int as a data type.

Your gut reaction might be to reach for Greg's approach of changing to a bigint data type. The challenge with this approach is that while your processing is dead in the water, you take the quick fix and change to bigint, any other consumer of that table is now going to fail. I went through this in 2011, by the way. We fixed the database only to have all the reporting and .NET applications fail. At that job, it'd have been far less catastrophic to have queued up the processing for a N days while we gave the appearance of normalcy than to remove all doubt by having every external facing application fail.

With the general implementation of an identity column, you can get an easy another 2B without making a single code change - simply set the identity value to the lower bound and you've bought yourself sufficient time to plan the migration to bigint. The command for this is dbcc checkident

You'll also likely want to ensure the identity column is specified as a unique value. People often set the identity column as a primary key but otherwise, you'd run code similar to the following.

SET NOCOUNT ON;

IF EXISTS
(
    SELECT * FROM sys.tables AS T INNER JOIN sys.schemas AS S ON S.schema_id = T.schema_id WHERE S.name = 'dbo' AND T.name = 'IntOverfloweth'
)
BEGIN

    DROP TABLE 
        dbo.IntOverfloweth;
END

CREATE TABLE 
    dbo.IntOverfloweth
(
    IntOverflowethID int IDENTITY(2147483647,1) NOT NULL
,   SomeValue varchar(30)
);

INSERT INTO
    dbo.IntOverfloweth
(SomeValue)
OUTPUT
    Inserted.*
VALUES
('Before');


BEGIN TRY

    INSERT INTO
        dbo.IntOverfloweth
    (SomeValue)
    OUTPUT
        Inserted.*
    VALUES
    ('Overflow');

END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH

-- Push the pointer back around to the begining 
DBCC CHECKIDENT('dbo.IntOverfloweth', RESEED, -2147483648);

-- Ensure uniqueness
CREATE UNIQUE INDEX UQ_IntOverfloweth
    ON dbo.IntOverfloweth
(
    IntOverflowethID
);

INSERT INTO
    dbo.IntOverfloweth
(SomeValue)
OUTPUT
    Inserted.*
VALUES
('Does not Overflow');

Hi Billinkc , thanks for your help,however my column is primary key with identity — Deepak, Jul 07 '15 at 04:01
@deepak This approach holds true if the identity column is also the primary key. The unique index creation applies to the rare situation where identity is not the pk. — billinkc, Jul 07 '15 at 12:55

Converting INT to Unsigned INT

2 Answers2