>>>>>>>I'd take an non-clustered sequential integer or bigint as the PK index. If I was able to rewrite all applications I created, I'd do that. Clustered index could be bery usefull on certain queries, but I'd be very carefull when applying that.
>>>>>>
>>>>>>Even integers can be generated twice, so I never understood the argument against GUIDs might be generated twice. The primary key option would prevent the duplicate key in both cases, and you'd have to generate another one and re-attempt the insert. A difference which makes no difference is no difference.
>>>>>
>>>>>Agreed. My main argument against a GUID is that it takes up quite a bit more space. Its less efficient. I'd use a GUID when records are created from disconnected systems.
>>>>
>>>>Well. GUIDs would help during company mergers. The NewSequentialID() function in SQL Server produces a GUID that is serialized to fill the index data pages, making inserts very fast, and more efficient that regular GUIDs. It takes up more space, than an int, but potentially far less than primary key on several data columns. :)
>>>
>>>Depending on how its stored, it at least takes 16 bytes to store and if you choose characters at least double of that. Again, not advocating compound keys in any way, but you can have 4 x integer columns for the same amount of space. Personally, space and performance (the more space an index uses, the more performance is impacted on larger number of rows) is more important to me.
>>>
>>>I'd avoid GUIDs when not having to deal with distributed data sources and I'd probably will go a long, long way to avoid using them as a PK (as opposed to an AK) at all cost.
>>>
>>>Walter,
>>
>>I also have to admit to a change I needed to make. I used to accept the default of clustering on the primary key. It seems the only need for a clustered key is when you absolutely need a set of records returned in a cluster, such as all the lineitems for a given invoice. The invoice foreign key should be clustered on the line items table, not the primary key on either invoice nor lineitems. Right?
>
>That is better if you're doing a lot of (demanding) queries on them... correct. There might be nuances to this rule, but in general I'd say that is correct.

Excellent Walter. Thanks! :)