| First there is the question of whether one should have surrogate keys at all, some oppose surrogate keys because:
They make tables more difficult to reason about.
They encourage structures that involve large amounts of joins. Others embrace surrogate keys because:
They make joins between tables easier (usually one field rather than a compound key where one might accidentally not include all the join predicates).
They save space since an integer is almost always smaller than a compound key. So if you are in the camp that favors surrogate keys, then consider these points: Some quick downsides for UUIDs:
If your table has a clustered index, UUIDs are generally not created in a regular order, and so you'll be constantly inserting into a part of your index rather than the end. Some databases allow sequential UUID generation to help mitigate this.
UUIDs are usually 16 bytes, but an int is usually 1-8 bytes. Also keep in mind you pay this price multiple times since the primary key (generally) becomes the foreign key in other tables. Some upsides for UUIDs:
If you have a process that has no master, being able to tie disparate things together with a shared UUID is very useful sometimes. Often developers use UUIDs/GUIDs because:
Their app wants to perform a 'Create' operation and they want to generate the key in advance. This can usually be mitigated by creating a routine that generates a range of reserved surrogate integer keys, and gives a starting value to the application layer. Then the application layer can use those values with knowledge they won't already exist. For instance, the app layer say, "I'm going to make 100 customers", and the response comes back "10232". Meaning that 10132-10232 is reserved for that process to use for customer primary keys. This allows larger (chunkier) requests which can be interrelated without constantly making requests to the Database layer. One could extent this to be a true cache for entity reserved numbers that gets dolled out as needed by the app layer. |