x

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

Slick84 gravatar image

Slick84
1.3k 75 102 142

(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

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

+1 : 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

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x729
x290
x126
x20
x8

asked: Aug 09, 2011 at 12:17 PM

Seen: 1987 times

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