Why using GUIDs as Primary Keys on a Clustered Index (Table) is a bad idea

Here is a great blog post by Kimberly Tripp on why using GUIDs as a PRIMARY KEY on a CLUSTERED index is a bad idea: GUIDs as PRIMARY KEYs and/or the clustering key

Summarized, the 2 big issues with using GUIDs as PK’s is:

1. Fragmentation of the clustered index is costly
2. The data type used is unnecessarily wide. (4x to be exact)

If you have to use a GUID as the PK, use NEWSEQUENTUALID() as opposed to NEWID(). It is not infallible in it’s ability to generate sequential keys, but it will reduce the fragmentation some.

Here is an example of how you can use a surrogate identity INT for the CLUSTERED index and use the GUID as the PRIMARY KEY on a NONCLUSTERED index and save most of the performance hit:

First lets create out table:

Then let’s insert a 1000 rows, generating a GUID for each row as the PK:

GUIDasPKonNCindexRows

Next let’s confirm that the indexes are correct:

GUIDasPKonNCindex

Views – 706