what type of database record id to use: long or guid?

Question

In recent years I was using MSSQL databases, and all unique records in tables has the ID column type of bigint (long). It is autoincrementing and generally - works fine.

Currently I am observing people prefer to use GUIDs for record's identity.

Does it make sense to swap bigint to guid for unique record id?

I think it doesn't make sense as generating bigint as well as sorting would be always faster than guid, but... some troubles come when using two (or more) separated instances of application and database and keep them in sync, so you have to manage id pools between sql servers (for example: sql1 uses id's from 100 to 200, sql2 uses id's from 201 to 300) - this is a thin ice. With guid id, you don't care about id pools.

What is your advice for my mirrored application (and db): stay with traditional ID's or move to GUIDs?

Thanks in advance for your reply!

Answer 1

guids have the

Advantages:

• Being able to create them offline from the database without worrying about collisions.
• You're never going to run out of them

Disadvantages:

• Sequential inserts can perform poorly (especially on clustered indexes).
  • Sequential Guids fix this
• Take up more space per row
• creating one cleanly isn't cheap
  • but if the clients are generating them this is actually no problem

The column should still have a unique constraint (either as the PK or as a separate constraint if it is part of some other relationship) since there is nothing stopping someone supplying the GUID by hand and accidentally/deliberately violating uniqueness.

If the space doesn't bother you and your performance if not significantly impacted they make a lot of problems go away. The decision is inevitably specific to the individual needs of the application.

Answer 2

I use GUIDs in any scenario that involves either replication or client-side ID generation. It's just so much easier to manage identity in either of those situations with a GUID.

For two-tier scenarios like a web application talking directly to the database, or for servers that don't need to replicate (or perhaps only need to replicate in one direction, pub/sub style) then I think an auto-incrementing ID column is just fine.

As for whether to stay with autoincs or move to GUIDs ... it's one thing to advocate GUIDs in a green-field application where you can make all these decisions up front. It's another to advise someone to migrate an existing database. It might be more pain than it's worth.