alanspeckman (9/12/2012)
What prompted me to question this was my coworker said it was a best practice when showing me a database design. I remembered Paul's video from an earlier viewing but I didn't remember the details. So, I viewed it again and did the demo myself so I saw his point.Is there a white paper or some reference material that states this as fact? Not that I don't believe you, and given what I saw in Paul's video and other blogs, no, I would not use a GUID as a PK. I just need to help someone understand this at work.
Books Online says the following about NEWSEQUENTIALID, which might make it tempting to use...
Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.
... but look at that again. If you ever need to bounce the machine, "the GUID can start againn from a lower range". That's not a good thing to do with a clustered index which is what most PK's end up being.
Also, something to be aware of... Books Online is actually incorrect about GUIDs being globally unique (and MS has admitted that fact although I'm on the wrong machine right now to be able to provide the link). While the probability of running across duplicate GUIDs across multiple machines is very slim (and I do mean incedibly slim), GUID's in SQL Server are no longer guaranteed to be unique if more than one machine is involved. SQL Server now uses Type 4 GUIDs which are nothing more than pseudo-random numbers and there's no guarantee that two machines can't generate the same number. The old Type 1 GUIDs that SQL Server used to generate were guaranteed to be unique even between machines provided that things like the MAC address (IIRC) for the machine was different.
--Jeff Moden
Change is inevitable... Change for the better is not.