
Erland Sommarskog 72,236 Reputation points MVP
20201211T22: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 5byte 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 nonissue. The data type for the sequence in your case should probably be
decimal(16,0)
.
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.