Autoincrement versus GUID

[MP]

context: vb6/ ado / .mdb format / jet 4.0 (not using Access - ADO only)

- creating tables via ADO (don't have access)
- all tables have a primary key (PK)
- many of the PK will become FK(Foreign Key) in other table(s)
- record entries will be made via ADO

I am soliciting opinions on the pros and cons of using AUTOINCREMENT
versus code generated GUID or LONG value
(as far as my limited understanding goes, if I enter a record, and need to
also make other linked entries in other tables, I need to retrieve the PK
which was just entered, save it to a variable, then make the succeeding
entries in the other tables using that variable for the FK field...is that
right?
I don't know much about stored procedures, but I read that in order to
retrieve the PK of a newly entered record, some rigamerole of a stored
proceedure and @@identity must be involved to get the PK of a newly entered
record...
which seems more complicated than just generating a guid, saving to a
variable, then using that variable for all the entries in all the tables
that need to be linked with that PK/FK...but that's just my inexperienced
thoughts on the matter)

opinions found on google:
1)
1/18/2000 Berni
"We have solved that problem in another way:
We use GUID for the ID-Field although they are bigger!
That has several advantages:
- We generate the number at first, so we dont need to retrieve it from the
server - it is faster, no stored proc, ..
- we are able to join separate generated tables - never double ID's .
- we are SQL-Provider independent (Oracle, SQL-Server or Access have all
different ways to use Autoincrement fields)
"

2)
Jeff Hedrick 1-18-2001
(microsoft.public.vb.database.ado)
I have found autoincrement columns to be problematic. While great for
conversion and population, some providers including SQL Svr seem to loose
their "seed" during operational problems. Also, determining the value after
an insertion is often impossible because of the global nature of this
variable in the data server
One thing I have noticed is that if the "counter" column is indexed, that
a query limited to returning one row in a DESC (descending) manner seems to
execute very rapidly. So, you can easily determine the last value in the
Table. Again, high volume multi-user environments can be a problem because
of the 2-stage nature.
Finally, a counter maintained in a separate table seems to be best. For
one thing, you can update it pessimistically. This value can easily be
determined as per above if corruption is suspected. (i.e. an insertion
encounters a problem) As a side advantage, you can seed your counter(s)
without having to seed the data server.

in reference to opinion 2 above, where he says "Finally, a counter
maintained in a separate table seems to be best"
I'm not sure what he means by that...
does that mean one creates (for example): tblCounter with a fldCurrentIndex
then every time a record needs to be added to another table, one has to get
the max value from tblCounter.fldCurrentIndex, add one to it, add that to
the next record in tblCounter and then use that value for your other
'actual' table entries???

other opinions?

tia
Mark

[Allen Browne]

The random GUID is primarily there for replication. If you are not using a
replicated database at disparate sites, you don't need it.

The AutoNumber value will be available in the recordset you added the record
to immediately after you add it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MP" <no****@Thanks.com> wrote in message
news:Qt*****************@tornado.rdc-kc.rr.com...

context: vb6/ ado / .mdb format / jet 4.0 (not using Access - ADO only)

- creating tables via ADO (don't have access)
- all tables have a primary key (PK)
- many of the PK will become FK(Foreign Key) in other table(s)
- record entries will be made via ADO

I am soliciting opinions on the pros and cons of using AUTOINCREMENT
versus code generated GUID or LONG value
(as far as my limited understanding goes, if I enter a record, and need to
also make other linked entries in other tables, I need to retrieve the PK
which was just entered, save it to a variable, then make the succeeding
entries in the other tables using that variable for the FK field...is that
right?
I don't know much about stored procedures, but I read that in order to
retrieve the PK of a newly entered record, some rigamerole of a stored
proceedure and @@identity must be involved to get the PK of a newly
entered
record...
which seems more complicated than just generating a guid, saving to a
variable, then using that variable for all the entries in all the tables
that need to be linked with that PK/FK...but that's just my inexperienced
thoughts on the matter)

opinions found on google:
1)
1/18/2000 Berni
"We have solved that problem in another way:
We use GUID for the ID-Field although they are bigger!
That has several advantages:
- We generate the number at first, so we dont need to retrieve it from the
server - it is faster, no stored proc, ..
- we are able to join separate generated tables - never double ID's .
- we are SQL-Provider independent (Oracle, SQL-Server or Access have all
different ways to use Autoincrement fields)
"

2)
Jeff Hedrick 1-18-2001
(microsoft.public.vb.database.ado)
I have found autoincrement columns to be problematic. While great for
conversion and population, some providers including SQL Svr seem to loose
their "seed" during operational problems. Also, determining the value
after
an insertion is often impossible because of the global nature of this
variable in the data server
One thing I have noticed is that if the "counter" column is indexed, that
a query limited to returning one row in a DESC (descending) manner seems
to
execute very rapidly. So, you can easily determine the last value in the
Table. Again, high volume multi-user environments can be a problem
because
of the 2-stage nature.
Finally, a counter maintained in a separate table seems to be best. For
one thing, you can update it pessimistically. This value can easily be
determined as per above if corruption is suspected. (i.e. an insertion
encounters a problem) As a side advantage, you can seed your counter(s)
without having to seed the data server.

in reference to opinion 2 above, where he says "Finally, a counter
maintained in a separate table seems to be best"
I'm not sure what he means by that...
does that mean one creates (for example): tblCounter with a
fldCurrentIndex
then every time a record needs to be added to another table, one has to
get
the max value from tblCounter.fldCurrentIndex, add one to it, add that to
the next record in tblCounter and then use that value for your other
'actual' table entries???

other opinions?

tia
Mark

[MP]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

The random GUID is primarily there for replication. If you are not using a
replicated database at disparate sites, you don't need it.

The AutoNumber value will be available in the recordset you added the record to immediately after you add it.

Thanks Allen,

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au:

The random GUID is primarily there for replication. If you are not
using a replicated database at disparate sites, you don't need it.

You don't need it at all. There is no reason to use the GUID
Autonumber in any Access application, as it leads to myriad problems
and accomplishes nothing significant.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/