How to make unique random alphanumeric sequence in SQL Server

Question

I want to make unique random alphanumeric sequence to be the primary key for a database table.

Each char in the sequence is either a letter (a-z) or number (0-9)

Examples for what I want :

kl7jd6fgw
zjba3s0tr
a9dkfdue3

I want to make a function that could handle that task!

Answer 1

You can use an uniqueidentifier. This can be generated with the NEWID() function:

SELECT NEWID()

will return something like:

BE228C22-C18A-4B4A-9AD5-1232462F7BA9

Answer 2

It is a very bad idea to use random strings as a primary key.
It will effect performance as well as storage size, and you will be much better of using an int or a bigint with an identity property.

However, generating a random string in SQL maybe useful for other things, and this is why I offer this solution:

Create a table to hold permitted char values.
In my example the permitted chars are 0-9 and A-Z.

CREATE TABLE Chars (C char(1))

DECLARE @i as int = 0
WHILE @i < 10
BEGIN
  INSERT INTO Chars (C) VALUES (CAST(@i as Char(1)))
  SET @i = @i+1
END

SET @i = 65
WHILE @i < 91
BEGIN
  INSERT INTO Chars (C) VALUES (CHAR(@i))
  SET @i = @i+1
END

Then use this simple select statement to generate a random string from this table:

SELECT TOP 10 C AS [text()]
FROM Chars
ORDER BY NEWID()
FOR XML PATH('')

The advantages:

• You can easily control the allowed characters.
• The generation of a new string is a simple select statement and not manipulation on strings.

The disadvantages:

• This select results with an ugly name (i.e XML_F52E2B61-18A1-11d1-B105-00805F49916B). This is easily solved by setting the result into a local variable.

• Characters will only appear once in every string. This can easily be solved by adding union:

example:

SELECT TOP 10 C AS [text()]
  FROM (
    SELECT * FROM Chars
    UNION ALL SELECT * FROM Chars
  ) InnerSelect
  ORDER BY NEWID()
  FOR XML PATH('')

Another option is to use STUFF function instead of As [Text()] to eliminate those pesky XML tags:

SELECT STUFF((
 SELECT TOP 100 ''+ C 
 FROM Chars
 ORDER BY NEWID()
 FOR XML PATH('')
), 1, 1, '') As RandomString;

This option doesn't have the disadvantage of the ugly column name, and can have an alias directly. Execution plan is a little different but it should not suffer a lot of performance lose.

Play with it yourself in this Sql Fiddle

If there are any more advantages / disadvantages you think of please leave a comment. Thanks.

Answer 3

NewID() Function will generate unique numbers.So i have incremented them with loop and picked up the combination of alpha numeric characters using Charindex and Left functions

;with list as 
    (
        select 1 as id,newid() as val
             union all
        select id + 1,NEWID()
        from    list   
        where   id + 1 < 100
    ) 
    select ID,left(val, charindex('-', val) - 2) from list
    option (maxrecursion 0)

Answer 4

The drawback of NEWID() for this request is it limits the character pool to 0-9 and A-F. To define your own character pool, you have to role a custom solution.

This solution adapted from Generating random strings with T-SQL

--Define list of characters to use in random string
DECLARE @CharPool VARCHAR(255)
SET @CharPool = '0123456789abcdefghijkmnopqrstuvwxyz'

--Store length of CharPool for use later
DECLARE @PoolLength TINYINT
SET @PoolLength = LEN(@CharPool) --36

--Define random string length
DECLARE @StringLength TINYINT
SET @StringLength = 9

--Declare target parameter for random string
DECLARE @RandomString VARCHAR(255)
SET @RandomString = ''

--Loop control variable
DECLARE @LoopCount TINYINT
SET @LoopCount = 0

--For each char in string, choose random char from char pool
WHILE(@LoopCount < @StringLength)
BEGIN
    SELECT @RandomString += SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength), 1)
    SELECT @LoopCount += 1
END

SELECT @RandomString

http://sqlfiddle.com/#!6/9eecb/4354

I must reiterate, however, that I agree with the others: this is a horrible idea.