Hi mike,

>http://www.sql-server-performance.com/zn_guid_performance.asp
>
>http://www.informit.com/articles/article.asp?p=25862&rl=1
>
>Interestingly enough, the second article despite proposing something faster than the GUID fails to examine the advantages of having many clients generate their own PK. I always think about the server in terms of a subway. The more people who take their time getting on and off the subway before it comes to my station, the longer I have to wait. The server could be spared the task of generating the key.

I'm not saying that GUIDs do not have advantages. In fact they could be handy esspecially in situations where your data is disconnected from the database (Eg, a representative wants to put data in on his laptop and later synchronize it with a remote server). I'm not al to worried about the extra server load on the server in regards to generating a key, unless we really talk about high volume transactions. When you frequently need a batch of keys you can add a seconds parameter to your SP that indicates you want say 100 keys. It will increment the key with 100 in stead of 1 and the client can use those 100 keys without any additional roundtrips. In fact you can think about several other strategics as well.

I've been working with key generators with SQL server and I must say that in general I don't have any problem with it at all. But I have to admit that my applications are not high volume.
The performance lost on the extra roundtrip might easily be gained in query and/or index performance.

To speak in terms of your subway example, Lets say that fewer people get on and of the train, bat for every FK or PK person it takes 4 times as long. Is that really getting you any benefit ?

Or if a roundtrip is a station and the length of the batch is the number of people going on and of the train. You can reduce the number of stations (roundtrips to the server), but if on each station 4 times as many people get on and off, it is difficult to see the benefit.

Walter,