This article shows the pros and cons of using the
GUID types on primary keys. Hope it helps you decide which suites your project best.
Using INT on primary keys
- Small amount of storage size (and integer is 4 bytes).
- Increased readability, practical use in testing, and easy to remember.
- Chronology of data; if two records are in ascending order, we can deduce that the second record was inserted after the first one.
- Support for functions that return the last primary key generated (
- Difficulty in the case of merging tables (the need to make remapping because the primary keys may be duplicated).
- Hard to work with distributed tables.
- Primary key in the form of
BIGINT is LUID, local unique identifier, which is only used locally in a table.
- After a large number of operations (insert, delete), the counter for primary key can be reset, bringing the problem of chronology to 1.
Using GUID on primary keys
- Easy to merge tables.
- Easy to work with distributed tables.
- The primary key is uniquely identified in the entire system (the number of machines or tables doesn't matter).
- Don't have problems when inserting a large number of operations.
- Bigger storage size (16 bytes - four times the size of an integer).
- Don't have the chronology assumption.
- Hard to remember and use in testing.
- Don't benefit from a mechanism to obtain the last generated primary key (MS SQL Server).
I had a hard time deciding which solution suites best for my project, but I finally used
GUIDs because of the advantages mentioned. But this happened only once, the rest of my projects have databases with
INT primary keys. Hope someone finds this useful. Special thanks to Iancu Caputa for helping with this article.