Access 2003 - Limit on Value of Primary Key Number

[George]

I have an Access 2003 database which I have been using for over 5 years. The
AutoNumber being used as the Primary Key in one of my tables is now 88537 and
is increasing as I add more data. Is there a limit on how high this
AutoNumber can go? Occasionally I delete old information from the database so
I have far less than 88537 records in the table, but I am worried that I will
exceed a limit on the value of the AutoNumber. If there is a danger of
exceeding the limit, is there a way to use the lower numbers which I deleted
in the past? Is there a reference book that can help me deal with this worry?

George

 

[Douglas J. Steele]

Autonumber fields are Long Integers. That means they can have values
between -2,147,483,648 and 2,147,483,647.

Once your Autonumber increments to 2,147,483,647, the next numbers used will
be -2,147,483,648, then -2,147,483,647 and so on until it finally reaches 0.

If you're only at 88,537 after 5 years, I suspect you won't have any issues.
<g>

 

[George]

Thanks Doug. I will sleep a lot better now. There should not be a problem in
my lifetime.

George

 

[Jamie Collins]

Autonumber fields are Long Integers. That means they can have values
between -2,147,483,648 and 2,147,483,647.

Once your Autonumber increments to 2,147,483,647, the next numbers used will
be -2,147,483,648, then -2,147,483,647 and so on until it finally reaches 0.

If you're only at 88,537 after 5 years, I suspect you won't have any issues.
<g>

You've made some reasonable assumptions there, so the following asides
are strictly for pedants <g>...

First, the increment 'step' value can affect the number of possible
autoincrement values e.g. here's an extreme example (ANSI-92 Query
Mode syntax):

CREATE TABLE Test
(
col1 INTEGER IDENTITY(1, 1073741824) UNIQUE,
col2 INTEGER NOT NULL
)
;
INSERT INTO Test (col2) VALUES (1)
;
INSERT INTO Test (col2) VALUES (2)
;
INSERT INTO Test (col2) VALUES (3)
;
INSERT INTO Test (col2) VALUES (4)
;
INSERT INTO Test (col2) VALUES (5)
;

The last insert fails because the autoincrement values are no longer
unique.

Second, an autonumber can be of type GUID (a.k.a. 'unique identifier',
'replication ID', etc) e.g.

CREATE TABLE Test2
(
col1 GUID DEFAULT GenGUID() NOT NULL UNIQUE,
col2 INTEGER NOT NULL
)
;

which has a much greater range of possible values and less likelihood
of clashes.

One question for which I have no answer: for an INTEGER (Long)
*random* autonumber, does the generation algorithm take into account
existing values to avoid clashes or does it merely play the odds?

Jamie.

--

 

[Douglas J. Steele]

My copy of the Microsoft Jet Database Engine Programmer's Guide isn't handy
(which I assume would probably mention it), but as AFAIK, it merely plays
the odds

 

[Jamie Collins]

My copy of the Microsoft Jet Database Engine Programmer's Guide isn't handy
(which I assume would probably mention it), but as AFAIK, it merely plays
the odds

You mean this?

http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx

it doesn't seem to go into such detail

Jamie.

--

 

[Douglas J. Steele]

You mean this?

http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx

No, I meant http://www.amazon.com/exec/obidos/ASIN/1572313420

 

[Jamie Collins]

On 21 Sep, 22:34, "Douglas J. Steele"

No, I mean thttp://www.amazon.com/exec/obidos/ASIN/1572313420

I believe the the link I posted is the online version of the first
four chapters of the paper copy book you linked to. Based on the
online version, I just don't think the book goes into the level of
detail required to answer such questions as random autonumber
algorithm (though the section about the optimizer is quite good). I
would be happy to be proved wrong about this

Jamie.

--