James,

>>>Another thing: using integers as primary keys gives complex queries an inherent speed boost. This alone is enough argument to use surrogates.
>>
>>After an example presented by Daniel Rouleau in another thread, I have changed my position to use surrogate keys most of the time, not all. He presented what I consider a good example of when to use intelligent keys.
>>
>>And one problem with integers, at least in SQL Server 7, is that you only have around 2 billion. This works fine for most situations (at least mine), but what happens after that? I think this changes in SQL Server 200.
>
>SQL Server 2000 does fix that by expanding the length of integer fields. But I am not sure if the same rule applies in SQL Server. The index engine in SQL Server is different. It makes sense that the smallest footprint would be the fastest, but have any test been done like thod Mac Rubel did?

Another problem with autoincrementing integer keys is that when having two or more servers replicating data, your key assigning mechanism also should be distributed. In this case its better to use GUID which ensure (to a certain degree) that unique keys are generated system wide.

When using GUID (16 bytes of character) you inmediately lose much of the advantage of small integer keys (4 bytes). In this case many intelligent keys might perform better.

Another problem is that when using autoincrementing keys, the index of the PK becomes 'unbalanced' because the nodes are always appended at the lower right node in the B-tree. This requires 're-indexing' of the primary key for best performance. Again you'll likely to have less trouble with this when using intelligent keys.

As I said before, surrogate keys generally do require more joins in queries. In itself it pulls down performance because of the extra joins needed to be processed. In DBMSs using a high isolation level (not applicable to VFP, In SQL server: SET TRANSACTION ISOLATION LEVEL command) (for example SERIALIZABLE which applies sharedlocks during a query) it causes more concurrency problems because during every read or write the chances a process has to wait for locks to be released held by another process is likely to occur more frequent.

Walter,