What is the most efficient way to generate 8 character random alphanumeric string in TSQL?

Question

I want to generate a unique 8 character random alphanumeric string in TSQL, for a mail confirm link. We are currently using a GUID but it is not very user friendly.

It only really has to obey a few rules:

• be 8 Characters long
• be unique
• be generated efficiently

Answer 1

SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 7) 

Answer 2

Here are 2 ways of solving it

Method 1: This will generate 8 random characters and store them in varchar @r, in order to prevent all the individual characters from being identical, I added a seed to RAND().

DECLARE @r varchar(8)  SELECT @r = coalesce(@r, '') +CHAR( CASE WHEN r between 0 and 9 THEN 48 WHEN r between 10 and 35 THEN 55 ELSE 61 END + r) FROM master..spt_values CROSS JOIN (SELECT CAST(RAND(ABS(CHECKSUM(NEWID()))) *61 as int) r) a WHERE type = 'P' AND number < 8 

Method 2: This method is using a tally table, each character will never occur more than once. The rows used to generate these characters are randomized by ordering on newid()

DECLARE @r varchar(8)  SELECT @r = coalesce(@r, '') + n FROM (SELECT top 8  CHAR(number) n FROM master..spt_values WHERE type = 'P' AND  (number between ascii(0) and ascii(9) or number between ascii('A') and ascii('Z') or number between ascii('a') and ascii('z')) ORDER BY newid()) a 

In order to ensure uniqueness for each varchar(8) you can store the results in a table and compare with result in that table. You can also make the varchar longer and just hope for the best