3 ms·
>Modern SQL is supposed to abstract from the physical representation. Tables model relations, and should not expose an implicit order in their rows. However eve
by slg 9y ago
>Modern SQL is supposed to abstract from the physical representation. Tables model relations, and should not expose an implicit order in their rows. However even today SQL Server creates a clustered index by default for primary keys, physically ordering rows in the old tradition.
Which is why the UUID (or GUID in SQL Server speak) can have other drawbacks there in comparison to auto incrementing bigints, namely delays due to the data being ordered randomly on disk. There are obviously ways to counter that like adding extra non-clustered indexes or changing the clustered index to something besides the primary key, but at that point the extra time and overhead might defeat the benefits you gained from going with UUIDs in the first place.
- jeremy_wiebe 9y agoIsn’t that exactly what the NEWSEQUENTIALID function was created for? https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...
- slg 9y agoIn theory yes, it solves some of the problems and introduces others. It isn't usable everywhere NEWID is usable, so if you assign UUIDS as part of a query you are out of luck. It also assumes you are generating all your UUIDs on the same machine without restarting which once again negates some of the benefits of UUIDs.
- tatersolid 9y agoULIDs solve these issues. Basically 48 bits of millisecond timestamp then 80 bits of randomness. The technique has been used in MSSQL apps since about 2003 (although people called them COMBs instead of ULIDs back then) https://github.com/oklog/ulid https://github.com/oklog/ulid
- slg 9y agoNice, I hadn't seen that before. It looks pretty good on first glance.
- qaq 9y agoThe solution to your concern is using " UUID generation algorithms (like Twitter’s “snowflake” or uuid_generate_v1() in the uuid-ossp extension for PostgreSQL) produce monotonically increasing values per machine." and is discussed in the article.
- slg 9y agoWhich is what your sibling comment mentioned. The author does touch on that approach but never touches on the drawbacks, some of which I mentioned in my other comment and some which are directly in conflict with the benefits of UUIDs. My point is merely that there are other details that a MSSQL user should take into consideration before simply following the author's suggestion. A novice MSSQL user might be better off with auto incrementing bigints in a way a novice user of a different RDBMS might not.
- qaq 9y agoI think article was pretty focused on PG. :) unlike MSSQL it does not cluster data on an index (you can run cluster command but it will not be preserved as data is inserted updated) so that concern is fairly specific to MSSQL.