How to get numeric random uniqueid in SQL Server

Question

I am using SQL Server 2005, please tell me how can I get 6 digits (unique numeric which should be random) value for every row in table.

I have a field in table in which I need 6 digit numeric value (random and unique).

Please help me to do this.

actually wants a 6 digit numeric value in random and unique for every row in table — Dr. Rajesh Rolen
– Dr. Rajesh Rolen, Commented May 17, 2010 at 6:47
actually it will be a "EXTENSION" to phone number.. so cant take more than that — Dr. Rajesh Rolen
– Dr. Rajesh Rolen, Commented May 17, 2010 at 6:48
Why are you generating a unique, random number for an extension? This seems like an unrealistic thing to be doing, even assuming you are using this purely as test data. Without knowing more about the system you're developing I can't say for sure, but I'd want to be absolutely sure that both random and unique are required — ZombieSheep
– ZombieSheep, Commented May 17, 2010 at 7:53
actually we will provide a phone extension number to each user who will register to out site. — Dr. Rajesh Rolen
– Dr. Rajesh Rolen, Commented May 17, 2010 at 9:42

gbn · Accepted Answer · 2010-05-17 07:48:41Z

6

SELECT ABS(CHECKSUM(NEWID())) % 999999

for a phone number:

SELECT RIGHT('000000' + CAST(ABS(CHECKSUM(NEWID())) % 999999 AS varchar(6)), 6)

NEWID is about as random as you can get in SQL Server.

However, if you want unique, you may as well start at 000000 and go to 999999. Any random generator will hit the birthday problem.

You can have unique or random that are reliable, but not both reliably

answered May 17, 2010 at 6:50

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

@Mitch Wheat: I was extending my answer as you commented

SELECT LEFT('000000' + CAST(ABS(CHECKSUM(NEWID())) % 999999 AS varchar(6)), 6) is always giving me 000000, no other results

@Rajesh Rolen- DotNet Developer: my mistake, should be RIGHT. Sorry.