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

more ▼

asked Aug 09, 2011 at 12:17 PM in Default

avatar image

1.3k 75 104 147

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Aug 09, 2011 at 12:43 PM

avatar image

Phil Factor
4.2k 8 27 21

  • : if only I could +10 it!

Aug 09, 2011 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, 2011 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, 2011 at 01:04 PM Kev Riley ♦♦

Heh. I was just reading this earlier...

Aug 09, 2011 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, 2011 at 01:50 PM Oleg
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 09, 2011 at 01:09 PM

avatar image

Håkan Winther
16.6k 38 46 58

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, 2011 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, 2011 at 02:58 PM Oleg

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

Aug 09, 2011 at 03:38 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 09, 2011 at 12:17 PM

Seen: 2415 times

Last Updated: Aug 09, 2011 at 12:17 PM

Copyright 2018 Redgate Software. Privacy Policy