Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

Random Value Per Row in SQL

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
8 Jan 2019CPOL 11.5K   1   2
Update table rows with varying random values in SQL

Occasionally, you need to update a table with a random value per row. And thanks to some optimizations SQL Server does, it’s not exactly straight forward.

If you just try to update with a random value like this, every row ends up with the same ‘random’ value.

SQL
update MyTable
   set SomeValue = rand()

This is because SQL Server only runs the rand() once because it doesn’t depend on any value from the row. My next thought was to see the rand() with a value from each row.

SQL
update MyTable
   set SomeValue = rand(Id) --Where Id is in Int

This wasn’t as random as I had hoped. Since my Id column was an identity column the ‘random’ numbers were almost sequential as well. For example, I got the following ‘random’ numbers for the following Id values:

Id Rand(Id)
101 0.715436657367485
102 0.715455290338744
103 0.715473923310002
104 0.715473923310002

So, I needed to come up with a way to get the seed value to vary for each row. So I decided to get the MD5 hash of the Id column.

SQL
update MyTable
   set SomeValue = rand(HASHBYTES('md5', convert(varchar, Id)))

That results in these values:

Id rand(HASHBYTES(‘md5’, convert(varchar, Id)))
101 0.954016112182829
102 0.249482833129777
103 0.863832691946289
104 0.751055796147016

And that was random enough for my needs.

This article was originally posted at https://hutchcodes.net/2019/01/random-value-per-row-sql

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
United States United States
I’m a Software Engineer at Microsoft working on the Azure Portal. Before that I spent about 20 years developed various business applications at a number of different companies. I have a passion for writing clean, scalable code and sharing what I’ve learned with others.

I also help run the Casco Bay .Net User Group

Comments and Discussions

 
QuestionWhy not generate a GUID or add a function? Pin
Member 1055764215-Jan-19 6:37
Member 1055764215-Jan-19 6:37 
I haven't needed to do this for a while, but I'm sure a wrote a C# function in a dll that I added to SQL Server and used that to generate a unique reference. It actually picked up what the local was and the time which seemed to work.
QuestionRandom-ish Pin
Member 1387970810-Jan-19 2:49
Member 1387970810-Jan-19 2:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.