You know, Larry, this would make a good presentation topic at AFUG. When can I sign you up!?

>I have read this entire thread and found it to be a very helpful discussion. I have advocated both SYS(2015) (in the past) and now GUID's. I have also used Auto-incrementing integers as keys as well as some variations on all of the theme's above. For what it's worth, here are some points I have learned:
>
>I tend to prefer GUIDs as PK's and I agree about the pitfalls of both SYS(2015) and Auto-incrementing Integers for PK's.
>
>My own learning, experience and reflection has brought up some other points:
>
>IMHO, there are two types of Keys in a database system:
>
>a. System-level Keys - ONLY the system (programs) will ever see or use these keys.
>b. User-level Keys - The end-users will see these (i.e. Product ID, Customer ID, etc)
>
>Because a GUID is universally unique, I know that each record in my database system is literally "fingerprinted". The key is now "auditable" and "traceable" back to a source.
>
>I will NEVER show System-level keys to an end-user. They have no need to see them.
>
>Because I DO have User-level keys (like ID's and auto-incrementing integers), I get the value of those for presentation purposes. Logically, they are similar to the GUID keys and COULD be used in place of them (logically speaking). So, in my queries against data, I will always return both the record GUID and User-level key fields together. This gives me human readable and friendly keys to see as well as system keys for the software to reference and use.
>
>I have see how GUID's are:
>
>a. Machine readable, machine friendly and logic friendly
>b. Difficult for human's to read/use and not human friendly
>
>This leads to me to having BOTH types of keys on each table. Memory is typically plentiful on systems I write, so I am not very concerned with having multiple keys on each table, each record. Moreover, IF I have slow queries it is typically a larger problem than my use of keys (i.e. perhaps I have queries needing optimization or other attention, such as too much data in a table, or improper normalization or de-normalization).
>
>Because of 1 and 2 above, I generally opt for:
>
>a. One GUID field as my record level PK - EVERY table has one.
>b. One field is my human-friendly ID field (i.e. Auto-increment is great here)
>
>
>SQL Server as Backend to VFP
>
>SQL Server has a UniqueIdentifier data type, which IS a GUID! The purpose is for a PK field. The data is compressed to 16-bytes rather than 36 characters. This makes it quite comfortable and useful with SQL Server. The issue I have encountered is how this translates in terms of data type over to VFP.
>
>VFP does NOT have a GUID based data type for me to use. The translation made by ODBC from SQL to VFP is to turn the GUID into a 38-character string (including curly-braces around the GUID). What I then find (in my situation) is my main storage of data is in SQL, I issue queries and get result sets where my GUIDs are 38 character string fields in the cursors.
>
>I find that when I send this BACK to the SQL backend, the ODBC translation does a perfect job of turning the string back in to a 16-byte GUID for storing in SQL. It's not a perfect solution, but it is the one presented by Microsoft where VFP is the front-end to SQL Server.
>
>
>Auto-Incrementing Issues
>
>I agree with the others here where Auto-incrementing can be problematic when crashes happen. Using GUID's in tandem with Auto-incrementing IDs is a great solution. It means that if I have a crash and need to recover, all is still ok and not lost. My GUIDs (once created) are FREE of dependencies. Auto-incrementing is tied to some reference that must be updated as each new record is created.
>
>Oh yes -- another rule:
>
>I NEVER use Auto-incrementing IDs as Foreign Keys! -- NEVER! The GUID is highly sufficient for logical relations. If I DO use Auto-increments as FK's they are merely "along for the ride" and for viewing and referencing only (human eyes, NOT machine).
>
>Moreover, because the GUID is the PK, it is the only field set for PK in the eyes of the DB system (either SQL Server or VFP). So, the "uniqueness" of the field cannot be broken on an Auto-increment. If things get "out-of-sync" ... who cares? My GUID's are pulling the load on the uniqueness need, so my Auto-incrementing ID fields are free to "hiccup" now and again (if at all).
>
>
>Final Note - Combining GUID + Other
>
>I have seen notes in here for combining things and have done so in certain instances. By combining, I am referring to GUID + Record Number or SYS(2015) + Record Number or some other combination of data (i.e. Machine ID, User ID, MAC Address and so on).
>
>In the right situation, these can be helpful tools. IMHO, however, there is one place I will not use a combined key and for a reason: I will not use a GUID + Other combination. The reason has to do with having a "pure" GUID unique data type on my SQL Server backend. I need the key to remain pure so the ODBC layer can make the translation with issue.
>
>Moreover, I find that by having a companion/tandem Auto-incrementing ID field to accompany the GUID field in each table, my need for "combined" keys evaporates. All I really have to do is to always ensure my queries include both fields, so my result sets have both for me to read (ID Field) and the system code to use (GUID).
>
>
>Conclusion
>
>I like GUIDs in tandem with Auto-incrementing IDs where SQL Server is the backend and VFP the front. I keep a GUID and an ID (Auto-increment field) on EACH table/record in my database systems. I like the universal uniqueness of GUIDs and the human-friendliness of Auto-incrementing ID fields working together. It provides me the machine/system level power and logic I am looking for and the human-friendly factor I also need (for both myself as a programmer and my end users as well).