what would be the maximum seed value for a bigint column in a table in SQL Server 2000

Question

what would be the maximum seed value for a bigint column in a table in SQL Server 2000

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
July 5, 2008 at 1:05 am

#72476

Hi,
please let me know what will be the maximum seed value for a table which is of bigint datatype for a field.
I would also helpfull if i get to know what to do if we exceed the seed value for a bigint field in a table
Regards
Dakshina

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

From BOL

bigint
Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

dakshinamurthy (7/5/2008)
I would also helpfull if i get to know what to do if we exceed the seed value for a bigint field in a table

If you exceed the maximum seed value of BigInt, you've done something really, really wrong because that's a really, really big number. If you used 1 number ever MICRO second (1000 numbers per MILLI second), it would still take over a quarter million YEARS to use all of the numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Jeff Moden (7/5/2008)
dakshinamurthy (7/5/2008)
I would also helpfull if i get to know what to do if we exceed the seed value for a bigint field in a table
If you exceed the maximum seed value of BigInt, you've done something really, really wrong because that's a really, really big number. If you used 1 number ever MICRO second (1000 numbers per MILLI second), it would still take over a quarter million YEARS to use all of the numbers.

Heh. Not if they set the seed to 9,223,372,036,854,775,000. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

rbarryyoung (7/5/2008)
Jeff Moden (7/5/2008)
dakshinamurthy (7/5/2008)
I would also helpfull if i get to know what to do if we exceed the seed value for a bigint field in a table
If you exceed the maximum seed value of BigInt, you've done something really, really wrong because that's a really, really big number. If you used 1 number ever MICRO second (1000 numbers per MILLI second), it would still take over a quarter million YEARS to use all of the numbers.
Heh. Not if they set the seed to 9,223,372,036,854,775,000. 🙂

Heh... now THAT would be really, really wrong! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

I just had to find out ...

create table tempdb..test ( col1 bigint identity(1,1))

go

set identity_insert tempdb..test on

go

insert into tempdb..test (col1) values (9223372036854775807)

--9,223,372,036,854,775,807

go

select * from tempdb..test

go

set identity_insert tempdb..test off

go

insert into tempdb..test default values

go

drop table tempdb..test

go

and the results:

(1 row(s) affected)

col1

--------------------

9223372036854775807

(1 row(s) affected)

Msg 8115, Level 16, State 1, Line 1

Arithmetic overflow error converting IDENTITY to data type bigint.

Arithmetic overflow occurred.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Heh... translation... BOOOOOM! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)