Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 To: Mike Yearwood
  Where is Mike Yearwood?
 Toronto
 Canada
 Mike Yearwood
 Tags
Subject: RE: Primary Key
Thread ID: 143107 Message ID: 143279 # Views: 48 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Monday, August 27, 2007 1:01:44 PM         
   


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

ENTIRE THREAD

Primary Key Posted by Aaron Hathaway @ 8/24/2007 10:25:32 PM
RE: Primary Key Posted by Borislav Borissov @ 8/24/2007 10:46:52 PM
RE: Primary Key Posted by Aaron Hathaway @ 8/24/2007 11:09:39 PM
RE: Primary Key Posted by Ken Murphy @ 8/25/2007 1:47:10 AM
RE: Primary Key Posted by Barbara Peisch @ 8/25/2007 5:01:14 AM
RE: Primary Key Posted by Mike Yearwood @ 8/26/2007 2:02:59 AM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 1:01:44 PM
RE: Primary Key Posted by Mike Yearwood @ 8/27/2007 3:37:30 PM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 5:57:08 PM
RE: Primary Key Posted by Mike Yearwood @ 8/27/2007 7:55:38 PM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 8:31:58 PM
RE: Primary Key Posted by Mike Yearwood @ 8/28/2007 2:33:15 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 2:48:25 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 3:48:57 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 5:06:25 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 6:03:54 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 9:10:24 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 10:48:19 PM
RE: Primary Key Posted by Andy Kramek @ 8/29/2007 12:41:37 PM
RE: Primary Key Posted by Ken Murphy @ 8/29/2007 12:58:20 PM
RE: Primary Key Posted by Mike Yearwood @ 8/29/2007 7:49:54 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:09:30 PM
RE: Primary Key Posted by Tore Bleken @ 7/25/2016 8:03:30 PM
RE: Primary Key Posted by Hugo Ranea @ 8/29/2007 9:34:10 PM
RE: Primary Key Posted by Daniel Hofford @ 8/29/2007 11:46:07 PM
RE: Primary Key Posted by Mike Yearwood @ 7/24/2016 1:53:22 PM
RE: Primary Key Posted by Benny Thomas @ 7/24/2016 2:30:24 PM
RE: Primary Key Posted by Mike Yearwood @ 7/24/2016 3:20:23 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:16:41 PM
RE: Primary Key Posted by Mike Yearwood @ 7/25/2016 8:49:05 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:04:07 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 6:59:58 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:00:06 PM
RE: Primary Key Posted by Koen Piller @ 7/25/2016 11:37:27 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/26/2016 8:01:52 PM
RE: Primary Key Posted by Mike Yearwood @ 7/26/2016 8:59:17 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/27/2016 7:02:39 PM
RE: Primary Key Posted by Paul Gibson @ 7/27/2016 12:21:25 PM