Generating alphanumeric codes from integer

Question

I need to generate unique, alphanumeric, 5-characters long codes from an auto-incremented column (IDENTITY). I wrote the following code to test a formula :

DECLARE @chars char(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

SELECT      TOP 10000000 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS num
        , SUBSTRING(@chars, CAST((CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS decimal(14, 7))/ POWER(36, 5) * 722446103248156) % 36 AS int) + 1, 1)
            + SUBSTRING(@chars, CAST((CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS decimal(14, 7)) / POWER(36, 4) * 655736841904959) % 36 AS int) + 1, 1)
            + SUBSTRING(@chars, CAST((CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS decimal(14, 7)) / POWER(36, 3) * 396425544379216) % 36 AS int) + 1, 1)
            + SUBSTRING(@chars, CAST((CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS decimal(14, 7)) / POWER(36, 2) * 965809684959924) % 36 AS int) + 1, 1)
            + SUBSTRING(@chars, CAST((CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS decimal(14, 7)) / 36 * 635869414647653) % 36 AS int), 1) AS code
INTO        dbo.test
FROM        master.dbo.spt_values AS a
CROSS JOIN  master.dbo.spt_values AS b;

Then to check the result, I run :

ALTER TABLE dbo.test ALTER COLUMN num int NOT NULL;
ALTER TABLE dbo.test ADD CONSTRAINT PK_test PRIMARY KEY(num) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_test__code ON dbo.test(code)  WITH (DATA_COMPRESSION = PAGE);

And the following query returns many rows :

SELECT  code
    , COUNT(*)
FROM    dbo.test
GROUP   BY code HAVING COUNT(*) > 1
ORDER   BY COUNT(*) DESC

Is there a way I could make these unique ?

Answer 1

You could use the property NEWID(), for example:

SELECT LEFT(NEWID(),5);

It will generate the following random code:

D4DA6

If you want to include this column in your table, just use:

ALTER TABLE dbo.Test
ADD Code varchar(6) NOT NULL DEFAULT LEFT(NEWID(),5)