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.
+1
A:
you can get your answer over here : http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/
more help : http://www.sql-server-helper.com/tips/generate-random-numbers.aspx
Pranay Rana
2010-05-17 06:47:22
will it be unique for every row in table?
Rajesh Rolen- DotNet Developer
2010-05-17 06:52:01
as per the post written it will return unique all time you can also check second link where the inbuilt sql function used to generate random number
Pranay Rana
2010-05-17 06:56:33
+3
A:
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
2010-05-17 06:50:23
SELECT LEFT('000000' + CAST(ABS(CHECKSUM(NEWID())) % 999999 AS varchar(6)), 6) is always giving me 000000, no other results
Rajesh Rolen- DotNet Developer
2010-05-17 07:13:46