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.

Answer 1

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