-
Erland Sommarskog 72,236 Reputation points MVP2020-12-11T22:53:00.067+00:00 You say 20 digits, but it appears that you want 16 digits plus UKWS at the end. And what you generate is a 5-byte random number, so you will only get 12 full digits. The first three will always be a 0, and the fourth will most also be 0.
Here is a better expression that gives you full 16 random digits:
SELECT CAST( CAST(CRYPT_GEN_RANDOM(8) AS bigint) % CAST(1E16 AS bigint) AS CHAR(16)) + 'UKWS'You need to do some math to find out what the likelihood for duplicates are with 10 million rows. My gut feeling is that it may be OK, but maybe not. Viorel's test showed that 12 digits was not good enough.
If you go by a sequence as suggested by Tom, it becomes a non-issue. The data type for the sequence in your case should probably be
decimal(16,0).4 additional answers
Sort by: Most helpful
-
Viorel 88,321 Reputation points2020-12-11T20:22:59.087+00:00 It is not clear if you are interested in generating unique values or in validation of your method.
If you did not check it yet, I made a test that inserts 10,000,000 values to a table according to shown approach. The test found 51 collisions; I hope that it was accurate. Therefore the method does not seem fully reliable.
-
Anonymous 61 Reputation points2020-12-12T18:35:14.3+00:00 Hi @Viorel thanks for checking this. How reliable do you think is CRYPT_GEN_RANDOM(9) to generate 20 digit unique identifier? Basically the table is already defined and I cannot change it. I need the import id to be unique and within 20 digit in length when importing the records onto a CRM using SSIS. Thanks.
Sign in to comment
Tom Phillips 17,611 Reputation points2020-12-11T18:35:25.83+00:00 Why don't you just use a sequence of type bigint?
David Browne - msft 3,756 Reputation points2020-12-11T23:05:17.783+00:00 For generated random 64bit (8 byte) values, the chance of a collision within the first 10,000,000 values is less than 1/100,000.
See eg https://preshing.com/20110504/hash-collision-probabilities for an overview of the math and this handy table:
This is well below the threshold you really need to worry about, as an occasional collision would generate an isolated Primary Key violation failure.
-
Erland Sommarskog 72,236 Reputation points MVP
2020-12-12T13:10:50.667+00:00 Ruhul was working with 16 digit numbers, and using the formula that David linked to, I get:
DECLARE @k float = 10E6, @N float = 1E16 SELECT 1E0 - exp(-@k*(@k-1)/(2*@N))which gives a result 0,005. So there is a small risk for a collision, so you may want to have a TRY-CATCH with a retry.
Now, in his original post, Ruhul only had a 40-bit number, and the probability for all 10 million numbers being unique is small:
DECLARE @k float = 10E6, @N float = power(2E0, 40) SELECT exp(-@k*(@k-1)/(2*@N))This returns 1.8E-20. That is, with that solution you are bound to get collision, but if you change the number generation as I suggested in my post, you will be safe, if you add retry.
Note: I knew how to compute the exact probability with the long multiplication, but I was not aware of the approximation.
Sign in to comment
Anonymous 61 Reputation points2020-12-12T13:00:26.15+00:00 Hi, thank you all for your answers. I think I am one step closer in resolving this. I can use CRYPT_GEN_RANDOM(9) to generate unique 20 digit for the import id. It doesn't need 'UKWS' at the end but it must be unique for a given transaction id. The import id column is a varchar column of 20 character length. It's already predefined so I cannot alter the datatype or the length.
Now I need to populate the ImportID. But where the transaction id is the same it needs to have the same import id. So for example, where transactionid is 4001 it needs to have the same import id for all rows whrere the transactionid is 4001. Please adivse how I can achieve this using T-SQL query. Thanks.
Sign in to answer-
Erland Sommarskog 72,236 Reputation points MVP
2020-12-12T13:14:34.923+00:00 That's a different question, I and I would suggest that you start a new thread. Please include CREATE TABLE statements for your tables, both source and target, so we get a better understanding of your problem.
Sign in to comment -
-
How to generate a unique 20 digit identifier in T-SQL
Anonymous
61
Reputation points
Hi,
I am using the following T-SQL code to generate unique 20 digit number. This Unique 20 digit number will be used as import id when importing records.
SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),16), ' ', 0) + 'UKWS'
Is there any way I can test that this will always generate a unique import id?
Let's say that I will import 10 million records in next 5 or 10 years. Is there any chance that above T-SQL will generate duplicate import ids?
Thanks,
Accepted answer
In case of hexadecimal values, I think that you can use ‘convert(varchar(20), CRYPT_GEN_RANDOM(10), 2)’. But because duplicates are theoretically possible (with very low probability, though), you can introduce an additional explicit check, or a constraints, a unique index, to detect such rare situations and generate another ID.