Wiki Home

SQL SERVERKey Generation


Namespace: WIN_COM_API
Please describe SQL SERVERKey Generation here.
I recently begain upsizing a VFP application to MS SQL Server. When accessing VFP tables, I have a method that creates new primary keys for all tables. While I have ported this code to SQL Server I wanted to know is creating you own key generation udf/store procedure preferable to using an IDENTITY column in SQL Server?

---

I found an interesting article on IDENTITY columns on SQLTeam.com (no plug intended). As I read through the article and gleaned what an IDENTITY column is, there are several notable ideas with an eye on your question above.

First, it appears on the face of it, you are duplicating code. You have said how you "ported" the VFP code for creating integer primary keys from VFP to SQL Server. The entire notion behind SQL Server IDENTITY columns is SQL Server is now capable of managing keys on some limited basis. I say limited because there appear to be "gaps" in SQL Server's management processes. Two examples were given in the article that I read:

1. SQL Server will NOT manage "holes" in keys. If you delete a record from the table, SQL Server will not "fill in" the missing key when another record is created in the table.

2. SQL Server limits the IDENTITY column management to each table. There is not a global schema-wide management of keys.

Lastly, I do not see a clear advantage of your ported code over the native code in SQL Server. If you intend to continue to use VFP as the GUI front-end for the data and are merely moving the data sink from VFP to SQL Server, then I see no need to duplicate functionality natively present in SQL Server.

However, given the limited nature of the SQL Server IDENTITY column management process, you may find you need more. In that case it is time to think about your key management from SQL Server's viewpoint with VFP as a front-end.

On that note, there is also a newer key type of data type in SQL Server for you to ponder: GUIDs (Global Unique Identifier's). These are 16byte fields based on an API call from Windows. You can read about them on the SQLTeam.com site as well. The simple essence of the matter is, there are advantages and disadvantages to each. Pick your tool with an eye on what you want to accomplish.

1. GUIDs are gaurenteed to be unique across time and space and are VERY helpful for managing data situations where uniqueness is either needed or REQUIRED.

2. GUIDs are memory heavy at 16bytes (vs 4 for integers in the IDENTITY column) and will make for slow indexing and retrieval comparitively speaking. Adding 12 bytes to a 'typcical record (1200 bytes) isn't going to have much impact on performance. Maybe on the index, but even then I would want to see some tests showing how much. My guess it it won't be noticable.

A famous contractor from 1884 used to say, "The key to success is choosing the right tool for the right job." This is true about IDENTITY columns, GUIDs and primary key management in general.

I find application generated GUIDs much easier to use with VFP. Server side generation of anything is fine as long as the client code (like VFP) doesn't need to know what key got generated (and you normally do to assign it to child data.)
( Topic last updated: 2006.05.07 08:31:24 PM )