What will be added if the maximum value of the identification column is reached?

Question

Possible duplicate:
What happens with the primary key identifier? when will it exceed the limit?

what will be added if there is an SQL server table with an identifier column (says int) that reaches the maximum int capacity?

Go back to the beginning?

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

Thanks for your reply. Best regards.

Answer 1

If the max int value is exceeded, you will get an arithmetic overflow error.

Try:

DECLARE @t TABLE ( id INT IDENTITY (2147483647,1), name VARCHAR(100) ) INSERT INTO @t (name) VALUES ('Joe') INSERT INTO @t (name) VALUES ('Tim') 

Answer 2

This will not allow you to insert more rows.

Answer 3

Something I didn’t know about was the identity functions ( @@ identity , SCOPE_IDENTITY and IDENT_CURRENT ) returns a decimal value (38.0) regardless of how your local identification field is defined.

As others have pointed out, the error message will have the same character Arithmetic overflow error converting IDENTITY to data type X

And while you were asking a question about SQL Server, my MySQL 4.trash horror story is an outdated application at an old job that has an identifier defined on tinyint. When it was full, it didn’t bomb, it just kept inserting lines with the same identifier (I know the PC should have prevented it, but it was a really bad db design)

@Joe Stefanelli already provided a framework for generating errors, but for my own education I blew it 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 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