 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
bill
Guest
|
 Posted: Wed Jul 28, 2004 1:13 am Post subject: Does identity field has the max records limit? |
 |
|
Hello,
I have a question.
I am using MSSQL2000.
My table used the identity field (AutoInc field). (Primary key)
Does that table has the max records limit?
I mean someday that table cannot be inserted new datas.
Thanks.
bill
|
|
| Back to top |
|
 |
bill
Guest
|
| Posted: Wed Jul 28, 2004 4:45 am Post subject: Re: Does identity field has the max records limit? |
|
|
Yes.
Thank you!
bill
"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> ???
news:jr3eg0le4gejg8m8i6hpda3d83q7rs4klp (AT) 4ax (DOT) com ???...
| Quote: | Hello,
I have a question.
I am using MSSQL2000.
My table used the identity field (AutoInc field). (Primary key)
Does that table has the max records limit?
I mean someday that table cannot be inserted new datas.
Atuoinc fields are Integer type fields so the max value is 2,147,483,647
--
Brian Bushay (TeamB)
[email]Bbushay (AT) NMPLS (DOT) com[/email]
|
|
|
| Back to top |
|
|
Arthur Hoornweg
Guest
|
| Posted: Wed Jul 28, 2004 6:46 am Post subject: Re: Does identity field has the max records limit? |
|
|
I can offer two ways to overcome this limit:
(1) You might consider using GUIDS instead of autoincs.
No limits at all!
Create table mytable(
MyID UniqueIdentifier not null primary key default newid(),
.....
);
(2) Alternatively, you can double the range of the autoinc
by beginning the count not at zero but at minus 2 billion,
this will give you a maximum 4 billion records:
CREATE TABLE mytable(
MyID INTEGER IDENTITY (-2000000000,1) PRIMARY KEY,
.....
);
--
Arthur Hoornweg
(please remove the ".net" from my e-mail address)
|
|
| Back to top |
|
|
Viatcheslav V. Vassiliev
Guest
|
| Posted: Wed Jul 28, 2004 11:03 am Post subject: Re: Does identity field has the max records limit? |
|
|
| Quote: | 1)
Theoretical limit 2^128. Really limit is some orders lower to be sure that |
values do not repeat.
3) Use bigint - up to 2^63-1 = 9'223'372'036'854'775'807 rows
CREATE TABLE mytable (ID BIGINT IDENTITY (1, 1) PRIMARY KEY)
//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"Arthur Hoornweg" <arthur.hoornweg (AT) wanadoo (DOT) nl.net> ???????/???????? ?
???????? ?????????: news:41074bb3 (AT) newsgroups (DOT) borland.com...
| Quote: | I can offer two ways to overcome this limit:
(1) You might consider using GUIDS instead of autoincs.
No limits at all!
Create table mytable(
MyID UniqueIdentifier not null primary key default newid(),
.....
);
(2) Alternatively, you can double the range of the autoinc
by beginning the count not at zero but at minus 2 billion,
this will give you a maximum 4 billion records:
CREATE TABLE mytable(
MyID INTEGER IDENTITY (-2000000000,1) PRIMARY KEY,
....
);
--
Arthur Hoornweg
(please remove the ".net" from my e-mail address)
|
|
|
| Back to top |
|
|
Arthur Hoornweg
Guest
|
| Posted: Fri Jul 30, 2004 12:23 pm Post subject: Re: Does identity field has the max records limit? |
|
|
Viatcheslav V. Vassiliev wrote:
| Quote: | Theoretical limit 2^128. Really limit is some orders lower to be sure that
values do not repeat.
|
That may be true, I can't check that. But since Mac address,
date,time and a random number are all part of the algorithm
I'm sure it's pretty safe.
GUIDS as a primary key give you one distinct advantage that
no autoinc integer type can offer: You can seamlessly merge
data from multiple databases all over the world into one
table and the primary key's won't collide. I actually *use*
that feature and it works great!
--
Arthur Hoornweg
(please remove the ".net" from my e-mail address)
|
|
| Back to top |
|
|
Viatcheslav V. Vassiliev
Guest
|
| Posted: Fri Jul 30, 2004 1:43 pm Post subject: Re: Does identity field has the max records limit? |
|
|
SizeOf(Guid) = 16 bytes = 128 bits, so all possible combinations are 2^128.
To be sure that values do not start to repeat (they are random generated),
some orders should be reserved. But anyway 2^100 should be enough for any
table.
//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"Arthur Hoornweg" <arthur.hoornweg (AT) wanadoo (DOT) nl.net> ???????/???????? ?
???????? ?????????: news:410a3db9 (AT) newsgroups (DOT) borland.com...
| Quote: | Viatcheslav V. Vassiliev wrote:
Theoretical limit 2^128. Really limit is some orders lower to be sure
that
values do not repeat.
That may be true, I can't check that. But since Mac address,
date,time and a random number are all part of the algorithm
I'm sure it's pretty safe.
GUIDS as a primary key give you one distinct advantage that
no autoinc integer type can offer: You can seamlessly merge
data from multiple databases all over the world into one
table and the primary key's won't collide. I actually *use*
that feature and it works great!
--
Arthur Hoornweg
(please remove the ".net" from my e-mail address)
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
