Arithmetic overflow error converting IDENTITY to data type bigint

Question

Arithmetic overflow error converting IDENTITY to data type bigint

narwhale

SSC Veteran

Points: 272
More actions
August 19, 2024 at 2:12 pm

#4447303
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
```
- This topic was modified 3 months ago by narwhale.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Theoretically @@IDENTITY value could be too large for a bigint. Hard to imagine actually using enough values to go past 19 digits, but it's theoretically possible.

Btw, you should get @@IDENTITY after the INSERT only, in case a trigger or some other such thing could affect it during the DELETE, i.e.:

EXECUTE sp_executesql @insertSQL

SELECT CAST(@@IDENTITY AS BIGINT)

EXECUTE sp_executesql @deleteSQL

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

narwhale SSC Veteran Points: 272 More actions · Answer 2

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.

narwhale SSC Veteran Points: 272 More actions · Answer 3

Yes, it can go very large. Just for testing big integers we have manually raised the identity value using:

DBCC CHECKIDENT("TempSeqID", RESEED, 9223372036854775805)

This reply was modified 3 months ago by narwhale.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

It is preferable to use SCOPE_IDENTITY() instead of @@IDENTITY

SCOPE_IDENTITY() returns the last identity value generated within the same scope (i.e. within the stored procedure)

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'')'
    SELECT CAST(SCOPE_IDENTITY() AS BIGINT)
    SET @deleteSQL = N'DELETE FROM TempSeq' + @tableName + ' WITH (READPAST) WHERE seqDummy = ''a'''
    EXECUTE sp_executesql @insertSQL
    EXECUTE sp_executesql @deleteSQL
END

narwhale SSC Veteran Points: 272 More actions · Answer 5

Thanks. I have already tried using SCOPE_IDENTITY() as well but facing the same error.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

So you deliberately set the value so that it would get too large for a bigint ... and that seems to be your error.

Not sure why you felt the need to go that high. Just go to a lower value and you should be ok, right?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

narwhale SSC Veteran Points: 272 More actions · Answer 7

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.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

narwhale wrote:

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.

SQL Server BigInt has a maximum of 9,223,372,036,854,775,807

Java's BigInteger is an arbitrary-precision integer. It can represent integers of practically unlimited size, constrained only by available memory.

@@INDENTITY and SCOPE_IDENTITY are type numeric(38,0)

narwhale SSC Veteran Points: 272 More actions · Answer 9

So there is no way of getting this working in SQL server if BIGINT is essentially equivalent to Java's LONG data type with max value 9223372036854775807 ..

This reply was modified 3 months ago by narwhale.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 10

Correct. You'd have to use decimal(38, 0) or some other larger value type rather than bigint.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Michael L John One Orange Chip Points: 27055 More actions · Answer 11

I am really curious what the use case is for being able to store values this large???

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ScottPletcher SSC Guru Points: 101098 More actions · Answer 12

I agree. Why the "need" to jump to such a huge number?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".