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

is `000000001` an acceptable value? or is `100000000` the smallest allowable 'random' value? — Marc B, Sep 02 '11 at 14:37
Does the num column always have six zeros as the last six digits? Do you have to retain the values that are currently unique? — Jaydee, Sep 02 '11 at 14:38
To clarify - custom id's have been covered many times and the answer is invariably **this is a bad idea** — JNK, Sep 02 '11 at 14:44
possible duplicate of [How do I generate random number for each row in a TSQL Select?](http://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select) — Alex K., Sep 02 '11 at 14:45
@Jaydee, yes, i need to retain the values that are unique and the last six digits does not have to be zeros — gina, Sep 02 '11 at 16:32

Qqbt · Answer 1 · 2011-09-02T16:10:48.630

3

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

edited Sep 02 '11 at 16:10

answered Sep 02 '11 at 14:55

Qqbt

802
2
8
33

How do you do this if the table already exists? There is no DDL for this, and you know that if you do it through Management Studio, it drops and re-creates the table, right? This might be okay in some cases but it can be disastrous if the table is large. – Aaron Bertrand Sep 02 '11 at 15:06
@Aaron, We dont know why he created a table without an identity field in the first place, but its better to recreate the table with an IDENTITY field (which is always unique) than trying to create customly implemented uniqueness.Not having a unique IDENTITY field and trying to calculate a unique number into a column has its performance issues and not always 100% guaranteed IMHO – Qqbt Sep 02 '11 at 16:04
That may be true but I think it is dangerous to suggest this approach without the warning that doing it through Management Studio can take your production application offline. – Aaron Bertrand Sep 02 '11 at 16:06
@Aaron, you're right, but since gina told us that the table is about 800 rows long i think its not going to be an issue, anyway ill do an update on the answer – Qqbt Sep 02 '11 at 16:08
My point is that someone else coming along later might look at the answer and think it's a good idea for them, too. They might miss details about Gina's specific situation with this one table. – Aaron Bertrand Sep 02 '11 at 16:10

score 1 · Answer 2 · answered Sep 02 '11 at 14:48

1

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;

answered Sep 02 '11 at 14:48

Aaron Bertrand

272,866
37
466
490

i just tried that and got this error msg : A severe error occurred on the current command. The results, if any, should be discarded. – gina Sep 02 '11 at 14:59
What is `SELECT @@VERSION;`, how many rows in the table, are there indexes on any of the columns... – Aaron Bertrand Sep 02 '11 at 15:01
there are over 800 rows and there is an index on the id col. thanks – gina Sep 02 '11 at 15:27
And what does `SELECT @@VERSION;` yield? – Aaron Bertrand Sep 02 '11 at 15:29
it yields: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7600: ) – gina Sep 02 '11 at 16:29
You're running the original release. A lot of these "server error" problems have been solved since RTM, you should install Service Pack 1. http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20302 – Aaron Bertrand Sep 02 '11 at 16:31

score 0 · Answer 3 · edited Sep 02 '11 at 15:03

0

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

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

edited Sep 02 '11 at 15:03

marc_s

732,580
175
1,330
1,459

answered Sep 02 '11 at 14:47

Whiler

7,998
4
32
56

Generate unique 9 digits values

3 Answers3

Linked