> > Yet, why do people insist on saying how long they've been in the industry?
>
> I can't tell you that in general terms.
>
> In my case I merely meant that my personal preferences are based on what I have seen done in different places over time. I have seen all sorts of approaches, some good, some bad, some indifferent. I try to base what I do on what I have seen proven to work best in practice in preference to relying on theory, marketing or even product documentation (and no, I am NOT trying to suggest that you, or anyone else in particular, does so).
>
> The only grounds I have for making those decisions, or even voicing my opinion, is my own experience.
>
> Regards
> Andy Kramek
> Microsoft MVP (Visual FoxPro)
>
Tightline Computers Inc, Akron Ohio, USAI would dearly LOVE to get in a seminar situation with you two discussing the relative merits of GUID vs Integer based Primary Key fields. This discussion (and an earlier one) taught me an amazing amount about primary keys.
I thought I might summarize what you have been discussing here and you can correct me if I am wrong.
Mike, you prefer to use a GUID as a primary key as it is globally unique and will be for the forseeable future. You recognize Andy's argument that a primary key needs to be "unique within a table" but counter that a GUID is indeed unique within that table - AND it is unique within that application and even beyond. The downside of a GUID is that it quite a bit larger than a 4 byte integer, but the upside is that you can generate unique primary keys locally.
Andy, you prefer to use an integer based primary key because it is only unique within the table. You state that you do not need something globally unique and the overhead of that 16 byte character string will slow down data transmission over a network or when running a query. 16 bytes is obviously 4 times larger than 4 bytes so it will take 4 times longer to transmit a GUID. While this may not make much difference on small queries, on larger queries or when the data must be transmitted over slow Internet connections, this delay can add up to something noticable.
The randomized GUID is "statistically guaranteed" to be unique. It is essentially a randomized series of 16 bytes that, in all probablity, will not be duplicated for the next 10 billion years or so. (Like the dinosaurs, we will probably be extinct by then, so if at that time a non-unique key is generated, who cares?)
With an integer based primary key, it is entirely possible that you will "run out of primary keys" because it is only a four byte integer. For most applications (even large applications) the likelyhood of running out of numbers in an integer based primary key is so small as to be non-existant. Integer based primary keys are not randomly generated. Indeed they are generated in order so you can take advantage of that incremental order when doing s search.
In my mind the key to your arguments are:
GUID - Upside: They are unique globally, so why not use them? You get to generate PK's locally, you can generate them across tables or even across databases. Downside: They are much bigger and therefore less efficient. They are also random so you cannot use any sort of order in doing a search.
Integer PK's - Upside: They are much smaller and therefore more efficient to transmit and use. They are generated in order, so you can take advantage of that order in a search. Downside: They are not unique globally so if you are attempting to merge two tables (or two databases) you are going to run into problems when the two tables that you are merging both have a recordID = 1.
Do I have the jist of both arguments?
Ken
You shall know the truth - and the truth shall set you free. (John 8:33)