Arithmetic overflow error converting IDENTITY to data type bigint

[syncfox]

Hello SQL experts,

I have a legacy application developed when SQL server did not have native support for sequences. So what is does is creates table and uses its primary key as a sequence value generated. How it does is - inserts and deletes a dummy row and instantly access @@identity value as a sequence value.

As state in the title, I am getting - Arithmetic overflow error converting IDENTITY to data type bigint (even when I manually trying to cast or convert @@identity as bigint).

Please help:

CREATE procedure GetCounter @tableName varchar(50) OUTPUT AS
BEGIN
    DECLARE @insertSQL NVARCHAR(100)
    DECLARE @deleteSQL NVARCHAR(100)
    set nocount on
    SET @insertSQL = N'INSERT INTO TempSeq' + @tableName + ' VALUES (''a'')'
    SET @deleteSQL = N'DELETE FROM TempSeq' + @tableName + ' WITH (READPAST) WHERE seqDummy = ''a'''
    EXECUTE sp_executesql @insertSQL
    EXECUTE sp_executesql @deleteSQL
    SELECT CAST(@@IDENTITY AS BIGINT)
END

Raised the identity for testing like this:

DBCC CHECKIDENT("TempSeqID", RESEED, 9223372036854775805)

Also, I have tried SCOPE_IDENTITY() instead of @@IDENTITY but facing the same error..

And accessing it as below:

USE [rcu]
GO

DECLARE @return_value int,
@dNextIndex bigint

EXEC @return_value = [STGSQL].[GetCounter]
@tableName = N'ID',
@dNextIndex = @dNextIndex OUTPUT

SELECT @dNextIndex as N'@dNextIndex'
SELECT 'Return Value' = @return_value

GO

Throws error:

Arithmetic overflow error converting IDENTITY to data type bigint.

Arithmetic overflow occurred.

[#2]

You're reseeding the table to value : 9,223,372,036,854,775,805
Max BIGINT value is: 9,223,372,036,854,775,807

If you run your repro twice, you'll get the Arithmetic overflow error. I'm not even sure why you're doing that to begin with. I think you're intention is to rerun the same test over and over, so please remove the 92233... from the DBCC CHECKIDENT() .

[#3]

@gyvkoff We are adding Java BigInteger support and hence we need underlying database to support this. Oracle supports this and has no issue. I assuming SQL Server's BIGINT is equivalent to Java's BigInteger data type.

[#4]

@syncfox Unfortunately they do not correlate. SQL Server's BIGINT is a 64-bit signed integer, the equivalent is LONG in Java. If you need to support Java's BigInteger, then you'll need to use varbinary(n) or string like varchar(max).