A couple of years ago I wrote a post on how to generate a random number in SQL Server.
It’s ok and it works, it’s also a method that I used for quite a while.
Why not use RAND() I’ve heard people say, well the issue with RAND() is that it will generate the same number of every row, lets look at the result from this rather arbitrary query.
SELECT database_id, RAND() AS RandomNumber
FROM sys.databases
You see the problem? Well in the post that I mentioned, I adressed this issue and offered a solution using CHECKSUM() and NEWID().
That’s ok but can be a bit cumbersome. I recently stumbled across a function that I hadn’t realised was there before (and of course this is where you all tell me, we’ve known about that all the time), CRYPT_GEN_RANDOM().
That function will create a random (and supposedly cryptographically strong) binary. You do need to pass in how long you want that binary to be in bytes. So lets see how that looks if we replace RAND() in our above example with CRYPT_GEN_RANDOM(1).
SELECT database_id, CRYPT_GEN_RANDOM(1) AS RandomNumber
FROM sys.databases
SELECT database_id, CAST(CRYPT_GEN_RANDOM(1) AS INT) AS RandomNumber
FROM sys.databases
Obviously you can change the length of the binary that you create if you want to increase the maximum number that you generate.
This is my new go to random number generator, hopefully you’ll find it a good way to create a random number of each row in your result set. The only question that I have is why didn’t I know about this function sooner?!