Generate unique 9 digits values

Question

I have a table as shown below:

Group           
---------------------------     
Id  Num         Name    Age
---------------------------
1   424000000   Damine  22
2   324000000   Arshley 18
3   276000000   Tita    20
4   424000000   Helen   21
5   424000000   Mary    19
6   324000000   Kathe   20
7   324000000   Mark    18
8   276000000   Phill   22

i want to make the Num col unique so i need to generate 9 digit numeric random values for the duplicates.

please help, Thanks

Answer 1

Not tested... but something like that...

SELECT 
    CONVERT(VARCHAR(9), RIGHT(NEWID(), 9)) AS [MyID] 
WHERE 
    LEFT([MyID], 1) NOT LIKE '0';

Answer 2

you set the num column as an identity field with a seed like 100000000 or just set the num field to an identity and print its value with an overloaded tostring method

Update: doing this (adding an identity column) through Management Studio will drop and recreate the table, which is not recommended on a very very large table

Answer 3

WITH u AS
(
 SELECT *, new_num = ROW_NUMBER() 
  OVER (PARTITION BY Num ORDER BY Id)
  FROM dbo.Group
)
UPDATE u SET Num += new_num - 1
FROM u
WHERE new_num > 1;