asked
Why should I NOT use GUID's as PK?
I was wondering if there are any particular disadvantages like e.g. massive page splits, severe index fragmentation, etc if I use GUID's on my database as the PK value across a majority of my tables. Thanks, S
Don't even think about it. Kimberley Tripp's blog post is a good place to start in order to understand the reasons why. [GUIDs as PRIMARY KEYs and/or the clustering key][1] [1]:
http://sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx
@SirSQL - having them as a PK is fine just as long as you purposefully make it NOT clustered
@Slick84 A quick answer is because - you might introduce high fragmentation and page splits during inserts in case if you use newid() as default or worse still let the front ends to feed the values. This can be somewhat mitigated by using newsequentialid() as default. I have a presentation I gave at our office a while ago showing different scenarios and consequences of using uniqueidentifiers, send me a PM on the main site so I can send you the document if you want - uniqueidentifier is pretty wide data type, it needs 16 bytes of storage, so your table must have some indexes then their respective records will get to keep the wide values as a reference to the actual data record
You should avoid GUID as PK, but as always "IT DEPENDS". If the table is expected to contain hundreds of millions of records, then you need to tune every single column,index, t-sql statement etc. (every byte counts). But if you expect the table to be tiny (or static), feel free to use GUID (with NEWSEQUENTIALID()) in your PK. You can also create a PK as a nonclustered index, but it all depends on your design and what you are expecting to handle. Tuning is key to high performing databases and testing is key to avoid unpleasant surprises.
My inclination with this question is to give a very black and white answer. The places where a GUID is a good solution are so incredibly rare that it is surely not worth filling people's brains with the idea. I've never come across a really good reason to prefer a GUID, but I'd be happy to accept the idea that it might happen.
