>>>>Hi Naomi,
>>>>
>>>>>>>>Hi all,
>>>>>>>>
>>>>>>>>I just attended a SQL Saturday in Dallas this past Saturday and I got the impression GUID's were not such a great idea for PK's. I would like to understand why that is, and what is the best choice for tables with lots of records.
>>>>>>>>Thanks
>>>>>>>>Tim
>>>>>>>
>>>>>>>Identity int (or bigint) fields are generally recommended. If you insist on using GUID, use NewSequentialID() function to generate them.
>>>>>>
>>>>>>Naomi,
>>>>>>
>>>>>>Can you give me some idea as to why Guid's are no longer the recommended way? There was a day, when GUID's was the recommended key since they were always unique accross all boundary's. An int may be smaller in the db but how on earth would I merge two tables together with an int for the key? I am not advocating either, just trying to get in the know.
>>>>>>
>>>>>>What does NewSequentialID() funchtion do that is different?
>>>>>>Thanks a bunch
>>>>>>Tim
>>>>>
>>>>>Take a look at this blog by George Mastros http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/best-practice-don-t-not-cluster-on-uniqu I've asked him to expand it.
>>>>
>>>>Excellent post and it answers my questions. Sounds like I need to modify some things as I have used the UniqueID in all my tables except smaller lookup tables. If I use the NewSequentialID then I will also have to modify some things to get the value back after inserts for my child tables. I appreciate your answers and pointing me to that post.
>>>>Tim
>>>
>>>You can generate the GUIDS completely locally for the parent and the children and not have to get the keys from the backend.
>>
>>
>>That is what I do now, but if I understand correctly the guids are not inserted at the end of the table and causes possible page splits. The NewSequentialID assures they are sequential and get added to the end of the table. Don't they have to be added at the table for that to work?
>>Tim
>
>NewSequentialID calls uuidcreatesequential in Windows. So you can call that yourself, meaning there would be no page split when a single workstation generates keys. Each workstation's keys would be sequential and page splits would be less frequent than with a completely random GUID. The trick? Is there enough of a saving generating the keys locally versus remotely?
>
>You could also use the output clause on the insert command.
>
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rpc/rpc/uuidcreatesequential.asp

Thanks MIke,

I am leaning toward trying to just not have clustered index on the primary key guid if that is possible. Based on what I read it doesn't provide any advantage for primary keys. So if that is true, why are they a clustered index by default? Assuming the primary key is a surogate key and not meaningful that is and mine are never meaningful.
Tim