question

Slick84 avatar image
Slick84 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
sqltsqladministrationfragmentationguid
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered
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
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SirSQL - having them as a PK is fine just as long as you purposefully make it NOT clustered
3 Likes 3 ·
@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
2 Likes 2 ·
+1 : if only I could +10 it!
0 Likes 0 ·
If you must use GUIDs just create them with a unique index and use something else for your PK/Cluster index (like an int or even bigint)
0 Likes 0 ·
Heh. I was just reading this earlier...
0 Likes 0 ·
Show more comments
Håkan Winther avatar image
Håkan Winther answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Replicating environments where the actual replication process is custom baked coupled with unwillingness to consider using and maintaining different seeds for narrow integer (or big integer) keys does the trick :(
0 Likes 0 ·
You've been peeking into our projects, haven't you, @Oleg? :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.