Identity Reseed SQL Server 2005

155 pts.
Tags:
SQL Server 2005
SQL Server 2005 (32-bit)
I have the following table create table SubscriptionTrans ( transid bigint identity(9223372036854775805,1), transdate datetime ) Run the below insert script 3 times. insert into SubscriptionTrans select getdate() You'll get below error Msg 8115, Level 16, State 1, Line 1 Arithmetic overflow error converting IDENTITY to data type bigint. Arithmetic overflow occurred. Is there a way to reseed the next identity value by modifying the datatype or any better solution to reseed the next value. I dont want to reset the value back to 1. Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

The max number allowed for a bigint column is 9,223,372,036,854,775,807, which you are reaching with the third insert.

One option could be changing the datatype of the column, as you suggested.

<pre>ALTER TABLE SubscriptionTrans
ALTER COLUMN transid NUMERIC(25)</pre>

But even the numeric and decimal datatypes have a limit in the amount of digits they can store (which is 38).

———————-

Greetings, Jsql;

The value range of a bigint in SQL Server 2008 is:

bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes

Your third insert exceeded the maximum storage value.

Ref: http://msdn.microsoft.com/en-us/library/ms187745.aspx

-Dmenke38

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dmenke38
    Jsql, The bigint datatype is the largest available in Transact-SQL. This discussion in Wikipedia may give you insight, but it is not likely you will be able to automatically increment beyond the max value except to store it as a string and increment it with your own algorithm. http://en.wikipedia.org/wiki/Bigint
    185 pointsBadges:
    report
  • Jsql
    Thanks for your suggestions Dmenke38 and Carlosdl !!
    155 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following