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

asked Aug 09 '11 at 12:17 PM in Default

Slick84
1.3k ● 71 ● 102 ● 142

(comments are locked)

2 answers:

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

answered Aug 09 '11 at 12:43 PM

Phil Factor
3.2k ● 8 ● 9 ● 14

+1 : if only I could +10 it!

Aug 09 '11 at 12:55 PM Kev Riley ♦♦

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)

Aug 09 '11 at 12:57 PM SirSQL

@SirSQL - having them as a PK is fine just as long as you purposefully make it NOT clustered

Aug 09 '11 at 01:04 PM Kev Riley ♦♦

Heh. I was just reading this earlier...

Aug 09 '11 at 01:39 PM ThomasRushton ♦

@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

Aug 09 '11 at 01:50 PM Oleg

(comments are locked)

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.

answered Aug 09 '11 at 01:09 PM

Håkan Winther
15k ● 29 ● 35 ● 46

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.

Aug 09 '11 at 02:27 PM Phil Factor

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 :(

Aug 09 '11 at 02:58 PM Oleg

You've been peeking into our projects, haven't you, @Oleg? :)

Aug 09 '11 at 03:38 PM KenJ

(comments are locked)