Recently, there was a question in one of SQL Server forum asking on updating all table rows with some Random numbers without duplicates. The person who had asked the question could achieve generating the random numbers in a way that there are some duplicates. However, his intention was updating the existing rows with unique random numbers.
Here is the script shared by Questioner
create table RandomNumberUpdate(Col1 bigint) Go Insert into RandomNumberUpdate Values(1) Go 1000 Select * From RandomNumberUpdate UPDATE RandomNumberUpdate SET Col1 = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000)
Once after the update query executes, we can observe there are many duplicates in the table.You may try it yourself and see the duplicates. Use the below query to check the duplicates.
;With cte as ( Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate )Select* From cte Where rn>1
Here is a different approach to get UNIQUE values without duplicates.
UPDATE RandomNumberUpdate SET Col1 = Abs(Checksum(NewId()))
Query the table to confirm for any duplicates as below and see that there are no duplicates.
;With cte as ( Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate )Select* From cte Where rn>1 --Clean the Code Drop table RandomNumberUpdate
2 thoughts on “Update Table with Random numbers without duplicates in SQL Server”
Reblogged this on Dinesh Ram Kali..