Arithmetic overflow error converting IDENTITY to data type bigint

Question

Arithmetic overflow error converting IDENTITY to data type bigint

narwhale

Old Hand

Points: 332
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 10 months ago by narwhale.

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

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

ScottPletcher SSC Guru Points: 101192 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 Old Hand Points: 332 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 Old Hand Points: 332 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 10 months ago by narwhale.

Jonathan AC Roberts SSCoach Points: 18407 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 Old Hand Points: 332 More actions · Answer 5

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

ScottPletcher SSC Guru Points: 101192 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 Old Hand Points: 332 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: 18407 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 Old Hand Points: 332 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 10 months ago by narwhale.

ScottPletcher SSC Guru Points: 101192 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: 27079 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: 101192 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".

Arithmetic overflow error converting IDENTITY to data type bigint

Cookies on SQLServerCentral