Timeline for Why should I map external IDs to internal IDs in my API?
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 31, 2022 at 6:28 | audit | First answers | |||
| Sep 4, 2022 at 20:19 | |||||
| Aug 27, 2022 at 14:36 | comment | added | mmmmmm | Not to me I only use a clustered index if there was a reason - so in general I would not set one - integer ids and GUIDS would never be on one | |
| Aug 27, 2022 at 13:55 | comment | added | Allon Guralnek | @mmmmmm: If the GUID is the entirety of the primary key, then that's most database's default. And without know the performance implication, it seems like the logical thing to do. | |
| Aug 26, 2022 at 21:37 | comment | added | mmmmmm | @AllonGuralnek Surely if you have GUID as part of they key you do not design the db to have cluster indices beginning with the GUID, juts make it a plain index. Clustering is only useful if there is an order you want to query or sort by e.g. date of entry. | |
| Aug 26, 2022 at 12:33 | comment | added | Christophe | @AllonGuralnek I updated the paragraph on performance to mention your argument (with credit ;-) ) , because not everybody goes through all the comments. | |
| Aug 26, 2022 at 12:32 | history | edited | Christophe | CC BY-SA 4.0 | added 639 characters in body |
| Aug 26, 2022 at 12:21 | comment | added | Christophe | @AllonGuralnek Thank you for this very relevant contribution! Indeed, many vendors provide clustered indexes that would create more overhead with GUID which can be in random order and require some reorganisation at each insert. So we'd definitively have a performance effect. The question remains nevertheless why an external key with the GUID would still be needed: the primary integer keyword be sufficient for the identity. So there's probably as well one of the other elements at play. | |
| Aug 26, 2022 at 11:08 | comment | added | Allon Guralnek | If your database uses a clustering index (aka index-organized tables), then using a GUID as its key would be detrimental to performance. Sequential GUID allocation can alleviate this, but not completely, and it requires your database (or some other central authority) to generate all GUIDs, which removes one advantage of them. Therefore, the most common practice is to use an internal sequential ID as the clustering index and the GUID as the primary key. That ensures most database insertions happen at the end of the index rather than in the middle where it would cause page splits. | |
| Aug 24, 2022 at 12:20 | vote | accept | David Klempfner | ||
| Aug 24, 2022 at 8:10 | history | answered | Christophe | CC BY-SA 4.0 |