Mike
> I've been troubled by suggestions that a GUID is not appropriate because it is somehow not just unique within a table. That is irrelevant as long as any particular key occurs only once within a particular table.
I don't think that I have ever seen a GUID described as "not appropriate". The word I have usually seen used is is "unnecessary", the rationale being that the rules defining a GUID are not congruent with those for creating a primary key.
However there are reasons why a GUID may not be the
best choice for a primary key in a table:
In VFP, specifically, GUIDs are not supported as a data type and so there is no real optimization for them in the indexes. Generally speaking long character strings (which is how VFP handles GUIDs) create larger indexes and are slower than integers, whether the index is primary or not. For this reason alone my personal preference is to avoid them as PKs in VFP, especially in those cases where the tables get large (over 1,000,000 records) - but that is just my preference.
More significantly, in databases that support clustered indexes (like SQL Server) there are real secondary issues surrounding the use of GUIDs as primary keys. This is because GUIDs are not necessarily index-sequential (the rules for GUIDs do not specify that they should be!) and so, unlike a sequential key, there is no guarantee that new records will only be added to the end of the table. Not only is this generally inefficient, but it can cause noticeable performance degradation on the server - even when the tables are not excessively large.
In these cases it is generally better to use an automatic integer as a PK within the table and if a GUID is required (e.g. for data merges - where the sequential issue can really hit you!) to add the GUID as an indexed column but not use it as either a clustered column, or the primary key for the table. After all, by definition a GUID is always a candidate key so its index does not even need to enforce uniqueness.
Regards
Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA