Click here to Skip to main content
13,828,588 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

1.5K views
1 bookmarked
Posted 8 Jan 2019
Licenced CPOL

Random Value Per Row in SQL

, 8 Jan 2019
Rate this:
Please Sign up or sign in to vote.
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 develop applications using Angular, ASP.Net MVC and SQL hosted on AWS by day, and in my free time I develop apps for Windows Phone and Windows 10 as well as web apps hosted in Azure. I always try to blog about what I've learned.

I also help run the Casco Bay .Net User Group

You may also be interested in...

Comments and Discussions

 
QuestionWhy not generate a GUID or add a function? Pin
Member 1055764210hrs 15mins ago
memberMember 1055764210hrs 15mins ago 
QuestionRandom-ish Pin
Member 1387970810-Jan-19 3:49
memberMember 1387970810-Jan-19 3: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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190114.1 | Last Updated 9 Jan 2019
Article Copyright 2019 by Jeremy Hutchinson
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid