How are these unique keys generated ?

Question

How are these unique keys generated ?

mar.ko

Default port

Points: 1451
More actions
February 24, 2015 at 6:49 am

#398067

2c92a0f93b5aafef013b670d44184e86
Sales Force and other applications are using these huge, character-based strings to act as a primary key instead of an Autonumber.
First of all, WHY are they using the above in lieu of a much more efficient long integer......
Secondly, how do they generate the above i.e. what algorithm ?
I know for certain you must insure case-sensitivity is set on as they are likely to have another key ending in : 184E86

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

they look like guid/NEWID to me, that have dashes stripped and then lowercased. exactly 32 characters, same as a GUID without dashes.

/*--results

PlainOldGuid StrippedGuid

------------------------------------ -------------

58E2F3FD-465A-462C-ABE8-520D73C3AB28 40e4976418864a7e834cd6b83b8a611a

E9F8A787-5B16-48FE-AFAB-5C579DAE4BEA eb0446686f08462e89e9a8267510e6dd

A140253F-E595-4631-8965-5C297BAB15C7 65cefb674b5645cab68f0455b6b1c57d

0BA8C9F1-F243-4616-9BEF-ECE5A4A3D81F 0a059d13ede54b7c900f0781e321228f

915AE13D-A7FE-4DAD-B21A-58F5F36F2F8E e05dd9e1d1a64eee8dddb9aed12253d1

2DEDEA84-EB6D-4312-9EB6-BD194F9EB1E0 792518691a174099a35dffcb7dcb418f

A205908E-3C2F-4C2C-AB56-219FA14B80A2 cda8d8a9e7d54df9817c8637897d8dbc

96B022D7-4659-467C-8CF6-2FAEE700C1B4 45ceeacd8f624b2a9b86527b283d0744

78747558-D27E-46B8-B215-0534D6DC4CBC 5bbccb1132f347feb5dc0501bddeed07

201B349F-7F11-43A7-81A4-1433E4DE073B 7ba002533f3c424baad99d335462e527

*/

select top 10

newid() As PlainOldGuid,

LOWER(replace(convert(varchar(40),newid()),'-','')) As StrippedGuid

from sys.columns

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mar.ko Default port Points: 1451 More actions · Answer 2

Thanks.....

How does the newid() function work ?

How does it know to create a unique value for a particular table ?

Why use a GUID vs. Autonumber/Sequence ?

Lowell SSC Guru Points: 323518 More actions · Answer 3

mar.ko (2/24/2015)
Thanks.....
How does the newid() function work ?
How does it know to create a unique value for a particular table ?
Why use a GUID vs. Autonumber/Sequence ?

well GUID is Globally Unique IDentifier, so supposedly, if all the machines in the world all spit out a million GUIDS each, there should theoretically be no duplication.

every programming language that i'm aware of has the ability to generate unique guid values, they used to be based on network card mac addresses long ago, but that changed a large number of years back for a better algorythm.

as far as guid vs autonumber, if you ever have to merge to data sources, the guids will not collide, where if i generate a table from 1 to N, and you do also, you'd have to make a constraint that was source + Autonumber in order to merge them, instead of just by the GUID values, which should never collide with duplicates.

you can read up on why they would not collide on wiki, which can explain it better than my understanding of it:

http://en.wikipedia.org/wiki/Globally_unique_identifier

...total number of unique such GUIDs is 2 to the power of 122 (approximately 5.3×10 to the power of 36). ...
Assuming uniform probability for simplicity, the probability of one duplicate would be about 50% if every person on earth as of 2014 owned 600 million GUIDs.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

GUIDs are very unlikely to collide, but there have been reports of this. It's not something to worry about in practice, but be aware.

These certainly are important for things like replication, or as Ken mentioned, merging data. Far too easy for other systems to generate auto-numbers that collide. Also, with GUIDs, you can generate these on the client.

If you are doing something like Salesforce, across lots of clients, GUIDs give you a large space of possibilities. You wouldn't want to autonumber each client, because that data could eventually be merged (companies purchase each other, sell information, etc.), and untangling things is a nightmare.

mar.ko Default port Points: 1451 More actions · Answer 5

re: "GUIDs give you a large space of possibilities."

I understand, but I would never use an Autonumber/Sequence as a key reference to a product or client, etc.

Instead, I would have generated a unique sequence number that would never be re-used.

Then, any tables with foreign keys would have those values.

That approach would only consume 4 bytes as opposed to 20 or 32 bytes.

Gotta be a huge difference in performance when joining millions of records, no ?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

mar.ko (2/24/2015)
re: "GUIDs give you a large space of possibilities."
I understand, but I would never use an Autonumber/Sequence as a key reference to a product or client, etc.
Instead, I would have generated a unique sequence number that would never be re-used.
Then, any tables with foreign keys would have those values.
That approach would only consume 4 bytes as opposed to 20 or 32 bytes.
Gotta be a huge difference in performance when joining millions of records, no ?

How do you make a unique sequence across systems?

There is a performance difference, and if you are dealing with a single database, I agree with you. However, when you deal with distributed systems, it's not a simple process.

mar.ko Default port Points: 1451 More actions · Answer 7

re: "with distributed systems"

And I now I finally "see it".

Thanks Steve..... and everyone else for responding.....

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

You are welcome. Glad it makes sense.

If you have more questions, feel free to ask.

Note that for the most part, I agree with you. All of the SSC systems here use Identity, which has served us well for over 14 years. If I had to merge with RG data, however, it might be a problem and I might reconsider.