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
4 additional answers
Sort by: Most helpful
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.
Why don't you just use a sequence of type bigint?
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.
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.
How to generate a unique 20 digit identifier in T-SQL
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?
Sign in to comment
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.