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

Question

Hi

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

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.

Answer 2

GUIDs have issues with performance and concurrency when page splits occur. INTs can run page fill at 100% - only added at one end, GUIDS add everywhere so you probably have to run a lower fill - which wastes space throughout the index.

GUIDS can be allocated in the application, so the App can know the ID of the record it will have created, which can be handy; but, technically, it is possible for duplicate GUIDs to be generated (long odds, but at least put a Unique Index on GUID columns)

I agree for merging databases its easier. But for me a straight INT is better, and then live with the hassle of sorting out how to merge DBs when/if it is actually needed.

Answer 3

If your data move around often, then GUID is the best one for the Key of the table. If you really care about the performance, just stick to int or bigint

If you want to leverage both of above, use int or bigint as the key of the table and each row can have a rowguid column so that the data can also be moved around easily without losing integrity.

Answer 4

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 5

If the ids are going to be displayed in the querystring, use Guids, otherwise use long as a rule.