Question

I see heated discussion all over the internet about whether a GUID is a poor choice for a primary key. Usually, these conversations all revolve around SQL Server.

A lot of people say that the size difference is an issue. (GUID = 16bytes, BIGINT = 8bytes, INT = 4bytes). Is this due to physical HDD space, or page space (fragmentation?)

For sake of conversation, lets say we have a 'PEOPLE' table:

create table "people"
(
     ID            uniqueidentifier  not null default newid(),
     first_name    varchar(100),
     last_name     varchar(100),
     ssn           varchar(9)    
);

alter table "people"
    add constraint PK_PEOPLE primary key (ID);

How bad of a design is that over using a BIGINT or an INT?

We have a lot of new development going on in a legacy application, and we are tossing GUIDs on every table. We do have to take into account replication (which is why we went with GUIDs as opposed to INTs and 'Hold Keys', as we call them)

Thanks!
Calvin

Answer 1

I would generally prefer GLOBAL AUTOINCREMENT over GUIDs as the latter should perform much worse - they are stored as BINARY(16) which won't perform as fast as 32 or 64 bit numbers on common platforms.

So, I personally agree with the reasoning in the docs. I would add that for many applications, even in a replication setup, INT may still be enough for a PK type if there are "only" some hundred remotes and you expect, say only a few ten thousand rows per table. That will do in many applications where data is added manually.

E.g., a few years ago, we have ported a client-server app with one central database into a SQL Remote setup by simply redefining the PK type (int) to a DEFAULT GLOBAL AUTOINCREMENT with a 200000 partition size. That gives use the opportunity to support some thousand remotes - much more than we have now. And the apps (and all the FKs) hadn't to be changed at all as they still work with INT fields. Needless to say, I was really fond of that easy migration:)

Of course YMMV.

In addition to Breck's response, I even prefer comparing INT/BIGINT values starting with 1 to those starting with higher values. At the moment, I have to check the migration of a system with miminal 8digit PK values, and that is really harder than with smaller values. But it's definitely much easier than comparing GUIDs.

Answer 2

GUIDs may be attractive if you don't want to use DEFAULT GLOBAL AUTOINCREMENT for avoiding primary key collisions in a replicating/synchronizing setup.

IMO the big problem with GUIDs will be administrative... every time anyone has to track down a problem involving data they'll be faced with this giant un-readable un-typable and generally incomprehensible string of crap.

Or dozens, hundreds, thousands of strings of crap.

Think Windows registry. Pity the maintenance programmer.

GLOBAL AUTOINCREMENT values also have their readability problems, but 100000001234 versus 200000000789 is not nearly as bad as {1ec17912-d2b4-48f9-9a47-5da6bbe695fb} versus {8dcb5d9f-5a52-43ef-82c3-2254e4d0c163}.

And that's if you're lucky enough to see the dashes, which isn't always true when you're in a hurry.

GLOBAL AUTOINCREMENT values are also somewhat more difficult to implement than GUIDs which, as you say, can be tossed around like road salt.

The phrase "tossing GUIDs on every table" does make me somewhat nervous. But then again, I always get nervous when I hear "we are unconditionally [making some dramatic change] to every single [thing] in our application". Gives me the screaming abdabs :)

Everything has unintended consequences. My advice, get promoted to management before the GUIDs go into production :)

Answer 3

Some disadvantages of GUIDs which come to my mind:

• It might be a performance question if you have a lot of inserts, because the calculations needed to create a GUID are more complex than a simple increment.

• It mights be also a performance question if you have a lot of transformations between text representation and internal representation of GUIDs during program execution.

• After the insert you can get the value of the autoincrement field of the just inserted row by using "select @@identity", this is not working for the GUID. (Maybe a good question how to get the last inserted GUID?, I will create a thread for it.)

Advantages I see:

• you don't have to think about ranges of values if you have a two-way replication.

• fix size of the GUID in a text form

• user is not expecting any sequence (questions like: where is row id 1024? (gone by a rollback) will not be asked by users)