Using GUID Autonumers pros and cons

[Steve Jorgensen]

So far, I have never chosen to use GUIDs as Autonumer keys for the tables in
my projects, but a recent experience has got me to wondering.

I have recently been working on a system that, during the early testing
phases, has a fairly extensive schema, fairly few records, and quite a bit of
UI code to handle things that don't work well via bound forms.

In this application, I have repeatedly had bugs initially go undetected
involving the use of the wrong field because it just happened to have a value
of something like 1 or 2 that just happened to be the right value in the
testing context. It seems to me that this situation could be greatly improved
by using GUIDs, and I'm wondering what the experience of others here has been
regarding the use of Longs vs GUIDs.

As I'm typing this, I realize there's another option that would help which is
using Random rather than Increment for the New Values argument (a less redical
option?), so I guess the question is - Increment, Random, or Replication ID.

Opinions?

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:16********************************@4ax.com:

So far, I have never chosen to use GUIDs as Autonumer keys for the
tables in my projects, but a recent experience has got me to
wondering.
Don't:

http://trigeminal.com/usenet/usenet011.asp?1033

That specifically considers the use of GUIDs in replication (about
the only place where it theoretically makes sense), but all but one
of the pitfalls MichKa describes (#1) apply to non-replicated apps.
I have recently been working on a system that, during the early
testing phases, has a fairly extensive schema, fairly few records,
and quite a bit of UI code to handle things that don't work well
via bound forms.

In this application, I have repeatedly had bugs initially go
undetected involving the use of the wrong field because it just
happened to have a value of something like 1 or 2 that just
happened to be the right value in the testing context. It seems
to me that this situation could be greatly improved by using
GUIDs, and I'm wondering what the experience of others here has
been regarding the use of Longs vs GUIDs.
VBA vs. ADO vs. DAO -- all three handle them differently.
As I'm typing this, I realize there's another option that would
help which is using Random rather than Increment for the New
Values argument (a less redical option?), so I guess the question
is - Increment, Random, or Replication ID.

Don't use ReplicationID in a non-replicated context. Even in a
replicated app, don't use them, because of MichKa's warnings.

As to random, I don't quite see why it matters if they are random or
not. Random AutoNumbers seem to me to have only two uses:

1. in a replicated app insures that the chances of PK collision
between replicas is minuscule (I've never seen a collision).

2. distributes the values in a PK index uniformly over the entire
range of possible values and not sequentially. This could
conceivably be a performance enhancer, as new values will not all be
on the same index data page.

But GUIDs are not usable.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Forrest]

> 1. in a replicated app insures that the chances of PK collision

between replicas is minuscule (I've never seen a collision).
If you're working with small enough datasets to fit into memory, you
can generate random numbers yourself, without needing to be connected
to the database. ( Can be very useful for sales support. ) You can
also generate negative sequential numbers and then let the db reassign
them, but this can be difficult with lots of relational tables.
2. distributes the values in a PK index uniformly over the entire
range of possible values and not sequentially. This could
conceivably be a performance enhancer, as new values will not all be
on the same index data page.
Interesting - I didn't think of that. They can be either useful for
security or a huge pain in the butt, in that they hide the order you
put records into your tables.
But GUIDs are not usable.

Totally agree.

[Steve Jorgensen]

On Thu, 18 Nov 2004 17:24:59 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:16********************************@4ax.com :

So far, I have never chosen to use GUIDs as Autonumer keys for the
tables in my projects, but a recent experience has got me to
wondering.
Don't:

http://trigeminal.com/usenet/usenet011.asp?1033

Very convincing - I'm convinced. It sounds like the idea is that you never
want to use GUIDs except for the unusual cases in which you still never want
to use GUIDs <g>, though Access may use them for you with replication, right?

...

In this application, I have repeatedly had bugs initially go
undetected involving the use of the wrong field because it just
happened to have a value of something like 1 or 2 that just
happened to be the right value in the testing context. It seems
to me that this situation could be greatly improved by using
GUIDs, and I'm wondering what the experience of others here has
been regarding the use of Longs vs GUIDs.

VBA vs. ADO vs. DAO -- all three handle them differently.

As I'm typing this, I realize there's another option that would
help which is using Random rather than Increment for the New
Values argument (a less redical option?), so I guess the question
is - Increment, Random, or Replication ID.

....
As to random, I don't quite see why it matters if they are random or
not. Random AutoNumbers seem to me to have only two uses:

1. in a replicated app insures that the chances of PK collision
between replicas is minuscule (I've never seen a collision).

2. distributes the values in a PK index uniformly over the entire
range of possible values and not sequentially. This could
conceivably be a performance enhancer, as new values will not all be
on the same index data page.

But GUIDs are not usable.

Well, it matters for the reason I stated. I want the code to break the first
time I try to use a value from the same column, not accidentally work in early
testing because the id value happens to be something like 1 or 2, then fail
later when it's harder to track down. It seems like random Autoincrments
would be a big help for that.

[David Schofield]

On Fri, 19 Nov 2004 04:32:43 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:

Well, it matters for the reason I stated. I want the code to break the first
time I try to use a value from the same column, not accidentally work in early
testing because the id value happens to be something like 1 or 2, then fail
later when it's harder to track down. It seems like random Autoincrments
would be a big help for that.

Hi
I'm sure that random autonumbers are exactly what you want in the
present case, but in some cases the idea of unique ids is very
appealling. The easiest way is to use the components of a GUID-like
object as separate fields. The 1996 spec for guids is explained here:
http://kruithof.xs4all.nl/guid-uuid-info.html
David

[Steve Jorgensen]

I made a confusing typo there. "the first time I try to use a value from the
same column" should say "the first time I try to use a value from the wrong
column".

On Fri, 19 Nov 2004 04:32:43 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:

On Thu, 18 Nov 2004 17:24:59 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:16********************************@4ax.co m:

So far, I have never chosen to use GUIDs as Autonumer keys for the
tables in my projects, but a recent experience has got me to
wondering.

Don't:

http://trigeminal.com/usenet/usenet011.asp?1033

Very convincing - I'm convinced. It sounds like the idea is that you never
want to use GUIDs except for the unusual cases in which you still never want
to use GUIDs <g>, though Access may use them for you with replication, right?

..

In this application, I have repeatedly had bugs initially go
undetected involving the use of the wrong field because it just
happened to have a value of something like 1 or 2 that just
happened to be the right value in the testing context. It seems
to me that this situation could be greatly improved by using
GUIDs, and I'm wondering what the experience of others here has
been regarding the use of Longs vs GUIDs.

VBA vs. ADO vs. DAO -- all three handle them differently.

As I'm typing this, I realize there's another option that would
help which is using Random rather than Increment for the New
Values argument (a less redical option?), so I guess the question
is - Increment, Random, or Replication ID.

...

As to random, I don't quite see why it matters if they are random or
not. Random AutoNumbers seem to me to have only two uses:

1. in a replicated app insures that the chances of PK collision
between replicas is minuscule (I've never seen a collision).

2. distributes the values in a PK index uniformly over the entire
range of possible values and not sequentially. This could
conceivably be a performance enhancer, as new values will not all be
on the same index data page.

But GUIDs are not usable.

Well, it matters for the reason I stated. I want the code to break the first
time I try to use a value from the same column, not accidentally work in early
testing because the id value happens to be something like 1 or 2, then fail
later when it's harder to track down. It seems like random Autoincrments
would be a big help for that.

[Steve Jorgensen]

On 19 Nov 2004 03:48:14 -0600, d.***************@blueyonder.co.uk (David
Schofield) wrote:

On Fri, 19 Nov 2004 04:32:43 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:

Well, it matters for the reason I stated. I want the code to break the first
time I try to use a value from the same column, not accidentally work in early
testing because the id value happens to be something like 1 or 2, then fail
later when it's harder to track down. It seems like random Autoincrments
would be a big help for that.

Hi
I'm sure that random autonumbers are exactly what you want in the
present case, but in some cases the idea of unique ids is very
appealling. The easiest way is to use the components of a GUID-like
object as separate fields. The 1996 spec for guids is explained here:
http://kruithof.xs4all.nl/guid-uuid-info.html
David

Thanks, I do get the distinction between the use of a GUID/UUID for
application purposes and the use of a GUID-typed field in a table. I've been
hoping to use UUIDs to identify entities across separate component
applications at some point, such as holding a reference to a Party entity in a
business application that was obtained from a separate contact management
application.

[David W. Fenton]

Va************@gmail.com (Forrest) wrote in
news:9d**************************@posting.google.c om:

1. in a replicated app insures that the chances of PK collision
between replicas is minuscule (I've never seen a collision).

If you're working with small enough datasets to fit into memory,
you can generate random numbers yourself, without needing to be
connected to the database. ( Can be very useful for sales
support. ) You can also generate negative sequential numbers and
then let the db reassign them, but this can be difficult with lots
of relational tables.

Such a method will be computationally more intensive than relying on
Jet's random AutoNumber because you're homegrown method will have to
check if the value already exists, whereas that's built into the Jet
random AutoNumber functionality.

And exactly what random number generator will you use? The VBA one?

2. distributes the values in a PK index uniformly over the entire
range of possible values and not sequentially. This could
conceivably be a performance enhancer, as new values will not all
be on the same index data page.

Interesting - I didn't think of that. They can be either useful
for security or a huge pain in the butt, in that they hide the
order you put records into your tables.

If it's the PK, they shouldn't have any meaning at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:i5********************************@4ax.com:

On Thu, 18 Nov 2004 17:24:59 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:16********************************@4ax.co m:

So far, I have never chosen to use GUIDs as Autonumer keys for
the tables in my projects, but a recent experience has got me to
wondering.

Don't:

http://trigeminal.com/usenet/usenet011.asp?1033

Very convincing - I'm convinced. It sounds like the idea is that
you never want to use GUIDs except for the unusual cases in which
you still never want to use GUIDs <g>, though Access may use them
for you with replication, right?

Jet uses them behind the scenes for identifying replication objects.
But it never gets to the UI level and you never have to deal with
tme in code.

Though I guess you could if you *wanted* to -- but I can't think of
a reason why you'd want to, except if you had a case where
replication had been broken and you had to do the synchronization
"manually." I've done it, actually, but I didn't write any code for
GUIDs. I did use them as criteria in queries (in place of joins
between GUID fields, which are not supported in the QBE UI; dunno if
they are supported in Jet SQL, though -- never tried it).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc