I need to be able to generate non-repetitive 8 character random alphanumeric for 2.5 million records

Question

I need to be able to apply unique 8 character strings per row on a table that has almost 2.5 million records.

I have tried this:

UPDATE MyTable 
    SET [UniqueID]=SUBSTRING(CONVERT(varchar(255), NEWID()), 1, 8)

Which works, but when I check the uniqueness of the ID's, I receive duplicates

SELECT [UniqueID], COUNT([UniqueID]) 
FROM NicoleW_CQ_2019_Audi_CR_Always_On_2019_T1_EM
GROUP BY [UniqueID]
HAVING COUNT([UniqueID]) > 1

I really would just like to update the table, as above, with just a simple line of code, if possible.

The idea is [here](https://stackoverflow.com/a/28171796/11683), the SQL implementation is [here](https://stackoverflow.com/q/33092823/11683). — GSerg, Mar 16 '19 at 13:00
Does it really have to be random? Or is just them being unique more important? And what characters are allowed in the 8 characters? A-Z, a-z, 0-9, other? — LukStorms, Mar 16 '19 at 13:04
[generate seemingly random unique numeric ID in SQL Server](https://stackoverflow.com/questions/26967215/generate-seemingly-random-unique-numeric-id-in-sql-server) — Vladimir Baranov, Mar 16 '19 at 13:24
You may also find useful [SQL convert number to string representation of any base (binary, hexadecimal, …, tricontahexadecimal)](https://stackoverflow.com/q/33092823/4116017) — Vladimir Baranov, Mar 16 '19 at 13:31

score 2 · Answer 1 · answered Mar 16 '19 at 12:59

2

Can you just use numbers and assign a randomish value?

with toupdate as (
      select t.*,
             row_number() over (order by newid()) as random_enough
      from mytable t
     )
update toupdate
    set UniqueID = right(concat('00000000', random_enough), 8);

answered Mar 16 '19 at 12:59

Gordon Linoff

1,242,037
58
646
786

How did I get so old with never having updated directly through a cte? Very nice! – Clay Mar 16 '19 at 20:45

LukStorms · Accepted Answer · 2019-03-16T20:07:42.520

Here's a way that uses a temporary table to assure the uniqueness

Create and fill a #temporary table with unique random 8 character codes.

The SQL below uses a FOR XML trick to generate the codes in BASE62 : [A-Za-z0-9]

Examples : 8Phs7ZYl, ugCKtPqT, U9soG39q

A GUID only uses the characters [0-9A-F].
For 8 characters that can generate 16^8 = 4294967296 combinations.
While with BASE62 there are 62^8 = 2.183401056e014 combinations.
So the odds that a duplicate is generated are significantly lower with BASE62.

The temp table should have an equal of larger amount of records than the destination table.
This example only generates 100000 codes. But you get the idea.

IF OBJECT_ID('tempdb..#tmpRandoms') IS NOT NULL DROP TABLE #tmpRandoms;
CREATE TABLE #tmpRandoms (
    ID INT PRIMARY KEY IDENTITY(1,1), 
    [UniqueID] varchar(8),
    CONSTRAINT UC_tmpRandoms_UniqueID UNIQUE ([UniqueID])
);

WITH DIGITS AS
(  
    select n 
    from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
),
NUMS AS
(
    select (d5.n*10000 + d4.n*1000 + d3.n*100 + d2.n * 10 + d1.n) as n
    from DIGITS d1
    cross join DIGITS d2
    cross join DIGITS d3
    cross join DIGITS d4
    cross join DIGITS d5
)
INSERT INTO #tmpRandoms ([UniqueID])
SELECT DISTINCT LEFT(REPLACE(REPLACE((select CAST(NEWID() as varbinary(16)), n FOR XML PATH(''), BINARY BASE64),'+',''),'/',''), 8) AS [UniqueID]
FROM NUMS;

Then update your table with it

WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RN, [UniqueID]
    FROM YourTable
)
UPDATE t 
SET t.[UniqueID] = tmp.[UniqueID]
FROM CTE t
JOIN #tmpRandoms tmp ON tmp.ID = t.RN;

A test on rextester here

score -2 · Answer 3 · answered Mar 16 '19 at 13:06

-2

See: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a289ed64-2038-415e-9f5d-ae84e50fe702/generate-random-string-of-length-5-az09?forum=transactsql

Alter: DECLARE @s char(5) and SELECT TOP (5) c1 to fix length you want.

answered Mar 16 '19 at 13:06

AirtonCarneiro

132
1
8

I need to be able to generate non-repetitive 8 character random alphanumeric for 2.5 million records

3 Answers3