IDENTITY limit

I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I defined a primary field using integer 4 bytes as IDENTITY.
>> What will be the limit of this number?
>> I am not sure the number is big enough in 10 years of time after my
>> application have been running.
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>