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


> However, the downside is that the data must be physically stored in the same order as the index. Therefore you can only have one clustered index per table (a table can only be physically sorted in one way at any time) and you must keep the records synchnronized with the index.
>
> When adding a record to a table with a non-clustered index, the new key is inserted in its appropriate place and the new record is appended to the end of the table and you are done. However with a clustered index the new index key is inserted, but then the new record must be inserted into the correct location in the table. This involves copying out and deleting all data that will follow the new record, then adding the new record (to what is now the end of the table) and then adding back the original records.
>
> Obviously this is tremendously intensive operation and is not something you want to be doing on a 10 gazillion row table every few seconds. This is why auto-generated sequential values are the preferred choice for primary keys and why you normally make the primary key index a clustered one.

In some shops. Others are using newer PKs including SequentialIDs.

Besides lots of shops continue to overuse stored procedures, just because many people do something doesn't make it right.

> > Re-joining databases, I have to agree. There is no way to know where the original record came from unless you store that information some where. (I recently joined two databases and did it the same way that you suggest - I created a new Int based PK instead of using a GUID and included a reference table with three fields: NewID, OldTable & OldPK.)
>
> Exactly! So presenting this as a reason for using GUIDs as PKs is specious.

So adding a GUID and a integer PK is better for network traffic?

> > Re where to create your PK, there have been times when I have thought "wouldn't it be great if I could generate this PK at the client" but then again, as you say, the only real way to find out if that PK is unique is to actually check and see if it is indeed unique. An Autoinc field is a LOT less work than generating a PK and then testing it to see if it is unique - especially if checking the uniqueness of the PK depends on if you are currently connected to the Internet. For those apps, I finally decided to assign a temporary local PK (with negative numbers) and when I send those records to the server, I apply the new "real" PK.
>
> Again, I have to agree. The real issue with creaing PKs at the client rests on the concept of "responsibilities". The key question is whose responsibility is it to ensure that a PK is unique? The client? The Middle Tier? The Database? Wherever the responsibility lies, so should the task. In the case of a PK, for precisely the reasons you gave, it simply has to be the Database.

The database has the responsibility to store the data. Therefore all users should enter data directly into the tables?

There is a drawback to having the PK generated on the server as well as having the server ensure uniqueness. It takes time to do both jobs and it takes less time to do one job. The PK generated by the army of client CPUs can free the already overly busy server for other tasks.


Mike Yearwood
www.foxridgesoftware.com
President: Toronto Ontario FoxPro User's Group

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