Click here to Skip to main content
14,608,080 members

Random Value Per Row in SQL

Rate this:
5.00 (1 vote)
Please Sign up or sign in to vote.
5.00 (1 vote)
8 Jan 2019CPOL
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.. update. MyTable. set.

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.

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.

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.

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.

License

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

Share

About the Author

Jeremy Hutchinson
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
MemberMember 1055764215-Jan-19 6:37 
QuestionRandom-ish Pin
Member 1387970810-Jan-19 2:49
MemberMember 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.

Technical Blog
Posted 8 Jan 2019

Tagged as

Stats

3.4K views
1 bookmarked