3 ms·
The portions of a UUID/GUID have meaning, there isn't just one version, though the display as a string is relatively consistent. The sorting often will sort on
by tracker1 8y ago
The portions of a UUID/GUID have meaning, there isn't just one version, though the display as a string is relatively consistent. The sorting often will sort on the timestamp portion of UUID v1, there are variants of a GUID (COMB or Combined Guid or Sequencial UUID, etc) that are meant where a portion of the values are sequencial like v1, and the rest is mostly random like v4.
SQL is optimized for the portion that is timestamp based when using v1 algorithms, because it results in better indexing performance. It's also why there's a sequencial id option with SQL server.
Other systems/databases will sort the raw bytes differently, other languages/platforms will use different arrangements. IIRC MongoDB's JS API actually has like 3-4 different serialization options because of these inconsistencies between languages/platforms.
Edit:
https://codingsight.com/combguid-generation-of-sql-server-friendly-guid-values-in-net-applications/ https://codingsight.com/combguid-generation-of-sql-server-fr...
NOTE: sequencial id's are not quite the same, they're part of the auto insert so that they are ensured sequencial... this is the best indexing option for SQL Server, though a COMB is probably easiest for applications. Other dbms will structure them differently, so best do figure out your serialization strategy based on your db.
- Tornhoof 8y agoHere is a more recent post with benchmarks of different GUID algorithms in SQL Server, the author has a different outcome. In his benchmarks about index fragmentation, COMBGuid is not really much better than normal GUIDs. http://microsoftprogrammers.jebarson.com/benchmarking-index-fragmentation-with-popular-sequential-guid-algorithms/ http://microsoftprogrammers.jebarson.com/benchmarking-index-...
- tracker1 8y agoIt does look like the sequencial ids (not the combs) does about 50% better overall, and the comb from nhibernate does about 10% better than just random uuids .. this is in terms of paging and indexes.. total time to run the test isn't bad, but that's just the insert time. If you're generating from your application layer and storing in SQL Server, looks like the best option is UuidCreateSequential.