What Will Append If The Maximum Value Of An Identity Column Is Reached?

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 reac

Solution 1:

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

Try it:

DECLARE@tTABLE (
    id INTIDENTITY (2147483647,1), 
    name VARCHAR(100)
)


INSERTINTO@t (name) VALUES ('Joe')  
INSERTINTO@t (name) VALUES ('Tim')

Solution 2:

It won't allow you to insert more rows.

Solution 3:

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 (select1from sys.tables T WHERE T.name ='Tim'AND SCHEMA_NAME(t.schema_id) ='dbo')
BEGINDROPTABLE dbo.Tim
END

IF EXISTS (select1from sys.tables T WHERE T.name ='Tim_decimal'AND SCHEMA_NAME(t.schema_id) ='dbo')
BEGINDROPTABLE dbo.Tim_decimal
END

IF EXISTS (select1from sys.tables T WHERE T.name ='Tim_bigint'AND SCHEMA_NAME(t.schema_id) ='dbo')
BEGINDROPTABLE dbo.Tim_bigint
END-- http://msdn.microsoft.com/en-us/library/ms187342.aspxCREATETABLE 
    dbo.Tim
(
    tim_id intidentity(2147483646 , 1) NOTNULLPRIMARY KEY
,   val int 
)

BEGIN TRY
    -- consumes the first valueINSERTINTO
        dbo.Tim
    SELECT0AS number

    SELECT SCOPE_IDENTITY() AS last_int_identity

    -- this insert brings us to the edgeINSERTINTO
        dbo.Tim
    SELECT1AS 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.INSERTINTO
        dbo.Tim
    SELECT-1AS 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 ONCREATETABLE 
    dbo.Tim_bigint
(
    tim_id bigintidentity(9223372036854775806, 1) NOTNULLPRIMARY KEY
,   val int 
)

BEGIN TRY
    -- consumes the first valueINSERTINTO
        dbo.Tim_bigint
    SELECT0AS number

    SELECT SCOPE_IDENTITY() AS last_bigint_identity

    -- this insert brings us to the edgeINSERTINTO
        dbo.Tim_bigint
    SELECT1AS 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.INSERTINTO
        dbo.Tim_bigint
    SELECT-1AS 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 ONCREATETABLE 
    dbo.Tim_decimal
(
    -- 10^38 -1-- 10^37 = 10000000000000000000000000000000000000-- 10^38 = 100000000000000000000000000000000000000
    tim_id decimal(38,0) identity(99999999999999999999999999999999999998, 1) NOTNULLPRIMARY KEY
,   val int 
)

BEGIN TRY
    -- consumes the first valueINSERTINTO
        dbo.Tim_decimal
    SELECT0AS number

    SELECT SCOPE_IDENTITY() AS last_decimal_identity


    -- this insert brings us to the edgeINSERTINTO
        dbo.Tim_decimal
    SELECT1AS 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.INSERTINTO
        dbo.Tim_decimal
    SELECT-1AS 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