> 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.comPresident: Toronto Ontario FoxPro User's Group