When your customer plans on opening a branch on the moon...
-
One of the reasons we stopped using SQL Server years ago was that our database that uses GUID's as Primary Keys got too slow. Nowadays there is a solution, see: NEWSEQUENTIALID (Transact-SQL) - SQL Server | Microsoft Docs[^] We have switched to PostgreSQL however, and are very pleased with the performance and ease of installation. :-\
I don't like GUIDs as IDs in any case. How often do you have to find something by ID and then type that huge GUID? It also doesn't say anything about when a record was created, at least you can say record 876231 came after 876230 and before 876232, could be a useful piece of information. So I'm not using GUIDs as internal IDs, but I can use them as external secondary IDs, like in URLs and such. Didn't know the NEWSEQUENTIALID :thumbsup:
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
-
So this was an actual conversation during a meeting yesterday. Person A is not technical, person B is somewhat technical. Person A: "The new API could return an ID so we can always find the file using that ID." Person B: "Yeah, maybe we can use a GUID for that, so we're sure the ID is unique." Me: "Yes, and if a GUID isn't good enough, we can always switch to a UUID!" Person B: "What's a UUID?" Me: "Universal, in case globally isn't unique enough." This made person A and B all enthusiastic :laugh: I'm not sure why we don't just use an incremental int, but sure, a GUID/UUID it is :laugh: I may even charge the big bucks to convert GUID to UUID, or maybe I'll just say it's a lot of work, but they're getting a good discount and I'll work the weekend so they'll have it by Monday ;p
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
-
I don't like GUIDs as IDs in any case. How often do you have to find something by ID and then type that huge GUID? It also doesn't say anything about when a record was created, at least you can say record 876231 came after 876230 and before 876232, could be a useful piece of information. So I'm not using GUIDs as internal IDs, but I can use them as external secondary IDs, like in URLs and such. Didn't know the NEWSEQUENTIALID :thumbsup:
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
We don't type in GUID's, it's just to have a unique ID, if you can get away with using simple integers that will speed things up of course but can lead to other problems, e.g. when you have to merge information from other databases. You are correct in saying that a GUID is terrible for information about when a record was created (or sorting purposes!) that's precisely the reason why they came up with NEWSEQUENTIALID to solve this.
-
The problem with GUID is "global" is limited to the machine that created the ID. This is in the definition details for GUIDs.
So far for "Global domination", seems to be an actual theme at the moment :-\
-
Which is hard to explain that random keys are slower than sequential generated. A sequence used as an index does need rebalancing the binary tree very often...?
Well, maybe I'm mixing things up, it was years ago that we did the migration, but I know for sure that PostgreSQL handles GUID's as Primary Keys much better than SQL Server did. SQL Server also demanded a lot more maintenance of the indexes (which we don't even have to do on PostgreSQL)
-
So this was an actual conversation during a meeting yesterday. Person A is not technical, person B is somewhat technical. Person A: "The new API could return an ID so we can always find the file using that ID." Person B: "Yeah, maybe we can use a GUID for that, so we're sure the ID is unique." Me: "Yes, and if a GUID isn't good enough, we can always switch to a UUID!" Person B: "What's a UUID?" Me: "Universal, in case globally isn't unique enough." This made person A and B all enthusiastic :laugh: I'm not sure why we don't just use an incremental int, but sure, a GUID/UUID it is :laugh: I may even charge the big bucks to convert GUID to UUID, or maybe I'll just say it's a lot of work, but they're getting a good discount and I'll work the weekend so they'll have it by Monday ;p
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
see [^] for some interesting details on GUID/UUID.
«The mind is not a vessel to be filled but a fire to be kindled» Plutarch
-
One of the reasons we stopped using SQL Server years ago was that our database that uses GUID's as Primary Keys got too slow. Nowadays there is a solution, see: NEWSEQUENTIALID (Transact-SQL) - SQL Server | Microsoft Docs[^] We have switched to PostgreSQL however, and are very pleased with the performance and ease of installation. :-\
RickZeeland wrote:
Nowadays
By which you mean "since 2005". :laugh:
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Just use a sequential ID and convert it into a GUID with an 8 character hex representation of the ID and the rest random stuff. Or:
Quote:
There a 5 versions of GUIDs defined in RFC 4122, each with different properties.
Ask them to pick a version and take a vacation for a week while they argue it out. :laugh:
Latest Articles:
ASP.NET Core Web API: Plugin Controllers and ServicesMarc Clifton wrote:
Ask them to pick a version
Or better yet, pick a version myself and see how all the managers disagree and want me to use another one of their uninformed choosing :D
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
-
I don't like GUIDs as IDs in any case. How often do you have to find something by ID and then type that huge GUID? It also doesn't say anything about when a record was created, at least you can say record 876231 came after 876230 and before 876232, could be a useful piece of information. So I'm not using GUIDs as internal IDs, but I can use them as external secondary IDs, like in URLs and such. Didn't know the NEWSEQUENTIALID :thumbsup:
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
-
I wonder how a construction mishap could end up with you working at Microsoft ;p I actually had an issue like that once. A service I wrote used an incremental int as ID. It probably generated a couple of 100 records a day, so an int would be large enough for the coming 14000 years at least. So the service worked well for a couple of months and then it started stuttering... Lots of calls would fail, but then a few would succeed and then it went well for a couple of days and then a batch of 100 failed again... It drove me mad, couldn't reproduce it in development, the failed messages were nothing out of the ordinary, a failed message could succeed a second time... And after weeks, maybe months, of searching, I finally found it! I don't remember why, but the auto-increment ID field rolled over to 1! It either used up all of its IDs due to a bug, or someone manually entered a really high ID and the auto-increment took it from there... The reason it sometimes worked is because a lot of IDs were missing, probably because I had to test a lot on production and I deleted those records. I'm pretty sure I was to blame, but I'd never thought about looking at a problem I did not expect to happen for the coming 14000 years. Found it on accident, but was able to fix it soon after that :laugh:
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
Powered up control panel, powered up - one at a time tool controllers and set IP addresses ( they come set to 192.168.0.4 ) powered up 4 at once. NO communication. Spent hours - bad switch? funky cables? ??? finally found 4 or 5 of them still had the chip default MAC address. Wanna know how to really confuse a switch? Had to go buy a null modem cable to program the right MACs. Globally unique id fail.
-
Just use a sequential ID and convert it into a GUID with an 8 character hex representation of the ID and the rest random stuff. Or:
Quote:
There a 5 versions of GUIDs defined in RFC 4122, each with different properties.
Ask them to pick a version and take a vacation for a week while they argue it out. :laugh:
Latest Articles:
ASP.NET Core Web API: Plugin Controllers and ServicesI have to maintain a dedicated microservice that just makes guids. What type? Oh "the most random type!" ``` string s4() { return random.Int.ToString(16)[0]; } string newGuid() { return s4() + s4() + s4() + s4() + s4() + s4() + s4() + "-" + s4() + s4() + s4() + s4() + "-" + s4() + s4() + s4() + s4() + "-" + s4() + s4() + s4() + s4() + s4() + s4() + s4(); } ``` Why? Because "this is the only method our security scanner accepts so it must be the best"
-
The problem with GUID is "global" is limited to the machine that created the ID. This is in the definition details for GUIDs.
That depends on the type. In theory, one of the types (I forget which) includes the computer's mac address (of the primary interface) so some ms docs come with the caviot like > Each GUID generated by using NEWSEQUENTIALID is unique on that computer. GUIDs generated by using NEWSEQUENTIALID are unique across multiple computers only if the source computer has a network card." [sql server NEWSEQUENTIALID docs](https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql?view=sql-server-ver15)
