CODEWITHSUNDEEP
sqlsql-server
Nylah
Nylah

Reputation: 27

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

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.

Upvotes: 1

Views: 715

Answers (3)

LukStorms
LukStorms

Reputation: 29667

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

Upvotes: 2

AirtonCarneiro
AirtonCarneiro

Reputation: 132

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.

Upvotes: -2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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);

Upvotes: 2

Related Questions