Generate a unique alphanumeric ID

Question

My idea is to generate a unique ID for my table Alphanumeric for My table in SQL server So I use Newid function to do that and then I truncate the result to 8 character. My question is with this code I am sure to have a unique ID? Or maybe not here is the code:

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 

DECLARE @id varchar(10)  
SET @id=CONVERT(varchar(8), @r)  
DECLARE @myid varchar(10) 

SELECT @myid=SUBSTRING(@r,1,2)+'-'+SUBSTRING(@r,3,3)+'-'+SUBSTRING(@r,6,3)  

PRINT 'Value of @myid is: '+ @myid

Answer 1

create or replace FUNCTION "GEN1_9A_Z" ( start_val varchar2)
   return varchar2
-----------------------------------------------------------------------------------------------------------   
-----------------------------------------------------------------------------------------------------------
-- GEN1_9A_Z generate next unique alphanumeric string with size 3 -- (0..9A..Z) --
                -- Return 0 when error or end (ZZZ). --
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-- 001,002,..,009,00A,00B,..,00Z,
-- 010,011,..,019,01A,01B,..,01Z,
-- .............................
-- 090,091,..,099,09A,09B,..,09Z,
-- 0A0,0A1,.................,0AZ,
-- 0B0,0B1,.................,0BZ,
-- .............................
-- 0Z0,0Z1,.................,0ZZ,
-- 100,101,.................,10Z,
-- 110,111,.................,11Z,
-- .............................
-- 990,991,.................,99Z,
-- 9A0,9A1,.................,9AZ,
-- .............................
-- 9Z0,9Z1,.................,9ZZ,
-- A00,A01,.................,A0Z,
-- A10,A11,.................,A1Z,
-- .............................
-- A90,A91,.................,A9Z,
-- AA0,AA1,.................,AAZ,
-- AB0,AB1,.................,ABZ,
-- ..............................
-- AZ0,AZ1,.................,AZZ,
-- B00,B01,.................,B0Z,
-- ..............................
-- ..............................
-- ZZ0,ZZ1,.................,ZZZ ---- END!!!

------------------------- generate 46656 unique alphanumeric. ( 000 - ZZZ ) --------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
is 
   start_value varchar2(3);
   return_val  varchar2(3);
begin

if length(start_val) <= 3 then

  start_value := lpad(upper(start_val),3,'0');

  select 
  (case 
    when p1= 0 and p2 = 0 and p3 = 0 and v3 != '9'                                 then lpad(to_char(start_value+1),3,'0') 
    when p1= 0 and p2 = 0 and p3 = 0 and v3  = '9'                                 then v1||v2||chr(65) 
    when p1= 0 and p2 = 0 and p3 = 1 and ASCII(v3) between 65 and 89               then v1||v2||chr(ASCII(v3)+1)
    when p1= 0 and p2 = 0 and p3 = 1 and v2 != '9' and v3 = 'Z'                    then v1||chr(ASCII(v2)+1)||'0'
    when p1= 0 and p2 = 0 and p3 = 1 and v2  = '9' and v3 = 'Z'                    then v1||chr(65)||'0'
    when p1= 0 and p2 = 1 and p3 = 0 and v3 != '9'                                 then v1||v2||chr(ASCII(v3)+1)
    when p1= 0 and p2 = 1 and p3 = 0 and v3  = '9'                                 then v1||v2||chr(65)
    when p1= 0 and p2 = 1 and p3 = 1 and v3 != 'Z'                                 then v1||v2||chr(ASCII(v3)+1)
    when p1= 0 and p2 = 1 and p3 = 1 and ASCII(v2) between 65 and 89  and v3 = 'Z' then v1||chr(ASCII(v2)+1)||'0'
    when p1= 0 and p2 = 1 and p3 = 1 and v1 != '9' and v2 = 'Z' and v3 = 'Z'       then chr(ASCII(v1)+1)||'0'||'0'
    when p1= 0 and p2 = 1 and p3 = 1 and v1  = '9' and v2 = 'Z' and v3 = 'Z'       then chr(65)||'00'
    when p1= 1 and p2 = 0 and p3 = 0 and v3 != '9'                                 then v1||v2||chr(ASCII(v3)+1)
    when p1= 1 and p2 = 0 and p3 = 0 and v3  = '9'                                 then v1||v2||chr(65)
    when p1= 1 and p2 = 0 and p3 = 1 and v3 != 'Z'                                 then v1||v2||chr(ASCII(v3)+1)
    when p1= 1 and p2 = 0 and p3 = 1 and v2 != '9' and v3 = 'Z'                    then v1||chr(ASCII(v2)+1)||'0'
    when p1= 1 and p2 = 0 and p3 = 1 and v2  = '9' and v3 = 'Z'                    then v1||chr(65)||'0'
    when p1= 1 and p2 = 1 and p3 = 0 and v3 != '9'                                 then v1||v2||chr(ASCII(v3)+1)
    when p1= 1 and p2 = 1 and p3 = 0 and v3  = '9'                                 then v1||v2||chr(65) 
    when p1= 1 and p2 = 1 and p3 = 1 and v3 != 'Z'                                 then v1||v2||chr(ASCII(v3)+1)
    when p1= 1 and p2 = 1 and p3 = 1 and v2 != 'Z' and v3 = 'Z'                    then v1||chr(ASCII(v2)+1)||'0' 
    when p1= 1 and p2 = 1 and p3 = 1 and v1 != 'Z' and v2 = 'Z' and v3 = 'Z'       then chr(ASCII(v1)+1)||'00'  
    else '0' 
  end)
  into return_val
  from 
      (
      select 
       NVL(LENGTH(TRIM(TRANSLATE(substr(start_value,1,1), ' +-.0123456789',' '))),0) p1,
       NVL(LENGTH(TRIM(TRANSLATE(substr(start_value,2,1), ' +-.0123456789',' '))),0) p2,
       NVL(LENGTH(TRIM(TRANSLATE(substr(start_value,3,1), ' +-.0123456789',' '))),0) p3,
       NVL(substr(start_value,1,1),0) v1,
       NVL(substr(start_value,2,1),0) v2,
       NVL(substr(start_value,3,1),0) v3,
       start_value
        from  dual
  );
else 
    return_val := '0';
end if;
return return_val;
end;

Answer 2

I think this is a bad idea, and you'll have problems inserting more than one row at a time and maintaining unique values. But, just for fun, here's some code to increment a string with 8 alphanumeric characters (assuming values should run from 0-9, then A-Z):

DECLARE @s varchar(20)= '00-0Z-0Z-ZZ'; --INPUT
DECLARE @n char(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE @pos tinyint;
SET @s = REPLACE(@s,'-',''); --REMOVE DASHES
SET @pos = LEN(@s);
WHILE @pos > 0
BEGIN
    IF SUBSTRING(@s,@pos,1) = 'Z'
    BEGIN
        SET @s = STUFF(@s,@pos,1,'0');
        SET @pos = @pos - 1;
    END
    ELSE
    BEGIN
        SET @s = STUFF(@s,@pos,1,SUBSTRING(@n,
                                CHARINDEX(SUBSTRING(@s,@pos,1),@n)+1,1))
        SET @pos = 0
    END

END
SET @s =  SUBSTRING(@s,1,2) + '-' 
        + SUBSTRING(@s,3,2) + '-' 
        + SUBSTRING(@s,5,2) + '-' 
        + SUBSTRING(@s,7,2) -- Replace Dashes

SELECT @s --OUTPUT

Answer 3

NEWID() produces a v4 GUID. In that GUID scheme, the first 8 bytes can be any hexadecimal digit 0-F and will be composed entirely of randomly-generated data. That's not guaranteed to be unique; in fact no v4 GUID is guaranteed to be unique, it's just that the random bits (112 of 128) can represent one of 5.19 decillion numbers, so the odds of any two of them matching in the same system is infinitesimal. With only the first 8 bytes, you'll only have 2^32 combinations, which may still seem like a lot (1 in 4 billion) but by the birthday problem, after a scant 77,000 have been generated you have a 50-50 shot at generating a duplicate.

Answer 4

If I understand it correctly, SQL server's NewId generates a GUID (Globally unique identifier), which is a 128-bit value commonly presented as a 32-character hexadecimal string.

Being finite, it certainly can't guarantee uniqueness, as there are only 2^128 possible values. But that's a large enough space that collisions are rare.

If you truncate it to 8 characters (I'm assuming you mean 8 characters in the hexadecimal representation), you reduce the likelyhood of uniqueness greatly, as there are 2^32 possible values.

Uniqueness is certainly not guaranteed.