2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dr. Rajesh Rolen
  • 14,029
  • 41
  • 106
  • 178
  • a 6 digit value is not going to be very unique now is it? – Mitch Wheat May 17 '10 at 06:45
  • actually wants a 6 digit numeric value in random and unique for every row in table – Dr. Rajesh Rolen May 17 '10 at 06:47
  • actually it will be a "EXTENSION" to phone number.. so cant take more than that – Dr. Rajesh Rolen May 17 '10 at 06: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 May 17 '10 at 07:53
  • actually we will provide a phone extension number to each user who will register to out site. – Dr. Rajesh Rolen May 17 '10 at 09:42

1 Answers1

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

gbn
  • 422,506
  • 82
  • 585
  • 676