What will append if the maximum value of an identity column is reached?

Question

Possible Duplicate:
What happens to the primary key Id? when it goes over the limit?

what will append if have an SQL server table with an identity column (says an int) that reaches the maximal capacity of the int ?

Go back to the beginning ?

Assume that the lines grow 100 by 100. Each time I insert 100 new lines, I delete the 100 old ones.

Thanks for your answer Best regards

score 5 · Accepted Answer · answered Oct 06 '11 at 12:51

5

You'll get an arithmetic overflow error when you exceed the max int value.

Try it:

DECLARE @t TABLE (
    id INT IDENTITY (2147483647,1), 
    name VARCHAR(100)
)


INSERT INTO @t (name) VALUES ('Joe')  
INSERT INTO @t (name) VALUES ('Tim')

answered Oct 06 '11 at 12:51

Joe Stefanelli

132,803
19
237
235

And if you use SET ARITHABORT OFF before the INSERT? :-) Mmmh... The same. – xanatos Oct 06 '11 at 13:02

score 1 · Answer 2 · answered Oct 06 '11 at 12:49

1

It won't allow you to insert more rows.

answered Oct 06 '11 at 12:49

Icarus

63,293
14
100
115

score 1 · Answer 3 · answered Oct 06 '11 at 13:29

Something I wasn't aware of was the identity functions (@@identity, SCOPE_IDENTITY and IDENT_CURRENT) return a decimal(38,0) value regardless of what your local identity field is defined as.

As others have indicated, the error message will be the same nature Arithmetic overflow error converting IDENTITY to data type X

And while you asked the question about SQL Server, my horror story of MySQL 4.trash is a legacy app at an old job had an identity defined on tinyint. When that overflowed, it didn't bomb out, just kept inserting rows with the same id (I know, the PK should have prevented it but it was a really poorly design db)

@Joe Stefanelli already provided a framework for generating errors but for my own education, I blew it out to cover bigints and decimal.

SET NOCOUNT ON

IF EXISTS (select 1 from sys.tables T WHERE T.name = 'Tim' AND SCHEMA_NAME(t.schema_id) = 'dbo')
BEGIN
    DROP TABLE dbo.Tim
END

IF EXISTS (select 1 from sys.tables T WHERE T.name = 'Tim_decimal' AND SCHEMA_NAME(t.schema_id) = 'dbo')
BEGIN
    DROP TABLE dbo.Tim_decimal
END

IF EXISTS (select 1 from sys.tables T WHERE T.name = 'Tim_bigint' AND SCHEMA_NAME(t.schema_id) = 'dbo')
BEGIN
    DROP TABLE dbo.Tim_bigint
END    
-- http://msdn.microsoft.com/en-us/library/ms187342.aspx

CREATE TABLE 
    dbo.Tim
(
    tim_id int identity(2147483646 , 1) NOT NULL PRIMARY KEY
,   val int 
)

BEGIN TRY
    -- consumes the first value
    INSERT INTO
        dbo.Tim
    SELECT
        0 AS number

    SELECT SCOPE_IDENTITY() AS last_int_identity

    -- this insert brings us to the edge

    INSERT INTO
        dbo.Tim
    SELECT
        1 AS number

    SELECT SCOPE_IDENTITY() AS last_int_identity

    -- This one goes kaboom
    --Msg 8115, Level 16, State 1, Line 27
    --Arithmetic overflow error converting IDENTITY to data type int.
    INSERT INTO
        dbo.Tim
    SELECT
        -1 AS number
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
    ,   ERROR_SEVERITY() AS ErrorSeverity
    ,   ERROR_STATE() AS ErrorState
    ,   ERROR_PROCEDURE() AS ErrorProcedure
    ,   ERROR_LINE() AS ErrorLine
    ,   ERROR_MESSAGE() AS ErrorMessage
END CATCH

bigint version

----------------------------------------------
-- Try again with big ints
----------------------------------------------
SET NOCOUNT ON

CREATE TABLE 
    dbo.Tim_bigint
(
    tim_id bigint identity(9223372036854775806, 1) NOT NULL PRIMARY KEY
,   val int 
)

BEGIN TRY
    -- consumes the first value
    INSERT INTO
        dbo.Tim_bigint
    SELECT
        0 AS number

    SELECT SCOPE_IDENTITY() AS last_bigint_identity

    -- this insert brings us to the edge

    INSERT INTO
        dbo.Tim_bigint
    SELECT
        1 AS number

    SELECT SCOPE_IDENTITY() AS last_bigint_identity

    -- This one goes kaboom
    --Msg 8115, Level 16, State 1, Line 27
    --Arithmetic overflow error converting IDENTITY to data type bigint.
    INSERT INTO
        dbo.Tim_bigint
    SELECT
        -1 AS number
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
    ,   ERROR_SEVERITY() AS ErrorSeverity
    ,   ERROR_STATE() AS ErrorState
    ,   ERROR_PROCEDURE() AS ErrorProcedure
    ,   ERROR_LINE() AS ErrorLine
    ,   ERROR_MESSAGE() AS ErrorMessage
END CATCH

Decimal version

----------------------------------------------
-- Let's really max this out
----------------------------------------------
SET NOCOUNT ON

CREATE TABLE 
    dbo.Tim_decimal
(
    -- 10^38 -1
    -- 10^37 = 10000000000000000000000000000000000000
    -- 10^38 = 100000000000000000000000000000000000000
    tim_id decimal(38,0) identity(99999999999999999999999999999999999998, 1) NOT NULL PRIMARY KEY
,   val int 
)

BEGIN TRY
    -- consumes the first value
    INSERT INTO
        dbo.Tim_decimal
    SELECT
        0 AS number

    SELECT SCOPE_IDENTITY() AS last_decimal_identity


    -- this insert brings us to the edge

    INSERT INTO
        dbo.Tim_decimal
    SELECT
        1 AS number

    SELECT SCOPE_IDENTITY() AS last_decimal_identity

    -- This one goes kaboom
    --Msg 8115, Level 16, State 1, Line 27
    --Arithmetic overflow error converting IDENTITY to data type decimal.
    INSERT INTO
        dbo.Tim_decimal
    SELECT
        -1 AS number
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
    ,   ERROR_SEVERITY() AS ErrorSeverity
    ,   ERROR_STATE() AS ErrorState
    ,   ERROR_PROCEDURE() AS ErrorProcedure
    ,   ERROR_LINE() AS ErrorLine
    ,   ERROR_MESSAGE() AS ErrorMessage
END CATCH

What will append if the maximum value of an identity column is reached?

3 Answers3

Linked