Random Numbers

This is going to be a very short post, but I keep seeing it pop up in forums so thought I would put my two cents in.

When trying to generate a random number say between 1 and 5, I’m noticing a lot of people using the follow notation:

   1: CAST(5 * RAND() + 1 as INT)

This method will work if you are looking to obtain a single random number. If however you are selecting from a table and you want to generate a random number for each row the above notation will produce the same number for ever row.

Ok so you might say that all you need to do is change the seed of the RAND() function to get a different value for each row. True however the best solution for this I’m seen so far is a follows:

   1: CAST(5 * RAND(CHECKSUM(NEWID())) + 1 as INT)

Now this does work but seems to be over kill. So…

I came across a method a few years ago on how to generate “true” random numbers in T-SQL and it looks something like this:

   1: ABS(CHECKSUM(NEWID())) % 5 + 1

Now you will note that when using this against a set of data each row will get a random number. Below is an example using all the notations as to help illustrate my point:

   1: SELECT TOP 10

   2:   ABS(CHECKSUM(NEWID())) % 5 + 1 as [True Random],

   3:   CAST(5 * RAND() + 1 as INT) as [RAND Random],

   4:   CAST(5 * RAND(CHECKSUM(NEWID())) + 1 as INT)

   5: FROM sysObjects

 

Please let me know if you have any other solutions, I’d be interested to get peoples options on this?