Identity column: what happens when it runs out?

I have a table containing an identity column (bigint) as its primary key.
The table will have very frequent insertions/deletions from it.
Many items will be added, but they aren't really expected to be there for
long. I can never expect that the table is empty, however.
So, eventually, the new ID's that get added will increment up to the maximum
bigint value. What happens then? Does it automatically wrap and start
over?
If it starts over, how does it handle any values that may still exist?
How should I handle this?
Thanks!
--
Adam Clauss
cabadam@.tamu.eduYou'll receive an overflow error if IDENTITY reaches the upper bound of the
datatype. Is that likely with a BIGINT? Assuming you start at zero then even
if you generated 1 billion rows per second, 24x7 it would still take nearly
300 years before you hit the ceiling. Your hardware will fall apart rather
sooner! If you're still not convinced then there's always NUMERIC instead.
David Portas
SQL Server MVP
--|||Hmm.. ok, I hadn't realized that bigint ran quite THAT big...
Thanks!
--
Adam Clauss
cabadam@.tamu.edu
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Y7qdnZR3Gc1nVG_cRVn-oA@.giganews.com...
> You'll receive an overflow error if IDENTITY reaches the upper bound of
> the datatype. Is that likely with a BIGINT? Assuming you start at zero
> then even if you generated 1 billion rows per second, 24x7 it would still
> take nearly 300 years before you hit the ceiling. Your hardware will fall
> apart rather sooner! If you're still not convinced then there's always
> NUMERIC instead.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have a table containing an identity column (bigint) as its primary key.
> The table will have very frequent insertions/deletions from it.
> Many items will be added, but they aren't really expected to be there for
> long. I can never expect that the table is empty, however.
Do you really need an IDENTITY column?

> So, eventually, the new ID's that get added will increment up to the
maximum
> bigint value. What happens then? Does it automatically wrap and start
> over?
No, you will get an overflow error.

> How should I handle this?
Not having an IDENTITY column?
A|||In that case maybe you don't need it. INTEGER is half the size of BIGINT and
still can store values from -2,147,483,648 to 2,147,483,647.
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OdRGx9MAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> Do you really need an IDENTITY column?
What else would I use? No other column would necessarily be unique.
Adam Clauss
cabadam@.tamu.edu|||> > Do you really need an IDENTITY column?
> What else would I use? No other column would necessarily be unique.
Oh great, another "I don't have a key so I'll make one up"... are you saying
that you could have multiple rows with the exact same data in every column?
What exactly are you trying to model?|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OgwuDLPAFHA.2804@.TK2MSFTNGP15.phx.gbl...
> Oh great, another "I don't have a key so I'll make one up"
You made MVP with that kind of an attitude? If this is the help you are
offering, I don't want any.
... are you saying
> that you could have multiple rows with the exact same data in every
> column?
That would be what I just said. Like I said though, if that's the kind of
help you are offering, thanks, but no thanks.
Adam Clauss
cabadam@.tamu.edu|||> That would be what I just said. Like I said though, if that's the kind of
> help you are offering, thanks, but no thanks.
Great, have fun. Maybe you'll get lucky and Celko won't stumble across this
one.|||If you are concerned about about running out of IDENTITY values with a
"numeric/int" based datatype, I would suggest you to look at the
UNIQUEIDENTIFIER datatype.
I am a VERY VERY VERY firm believer in the UNIQUEIDENTIFIER datatype (aka
the GUID [Globally Unique Identifier] in .NET and elsewhere). I resisted it
at first... but came around to understand it and what it could do for my
applications...
It will take a bit change on your part to convert to the GUID way of
thinking, but it might be worth it for you. I know it was for me but well
worth it!!!
Chris
"Adam Clauss" <cabadam@.nospam.tamu.edu> wrote in message
news:uKir3hMAFHA.2600@.TK2MSFTNGP09.phx.gbl...
>I have a table containing an identity column (bigint) as its primary key.
>The table will have very frequent insertions/deletions from it.
> Many items will be added, but they aren't really expected to be there for
> long. I can never expect that the table is empty, however.
> So, eventually, the new ID's that get added will increment up to the
> maximum bigint value. What happens then? Does it automatically wrap and
> start over?
> If it starts over, how does it handle any values that may still exist?
> How should I handle this?
> Thanks!
> --
> Adam Clauss
> cabadam@.tamu.edu
>