Quickly Randomise a number in SQL Server

How to get a true random number in SQL Server

We all know that the RAND() function in SQL Server doesn't generate a true random number and people have come up with many ways to randomise the "seed" that is passed into the RAND() function to make it more "random".

However a quick way in SQL 2005 + to get a random number or randomise a recordset is to use the NEWID() function which generates a GUID.

To randomise a recordset e.g to show a random sub-select of 5 out of 100 records you would just do this:

SELECT TOP 10 *
FROM   MyTable
ORDER BY NewID()

You will see that on each execution a different recordset is returned.

How To Generate a single Random Number between 0 and 9 with NEWID()

If you want a single number e.g to do a simple test like@

IF @No < 5 
  Do X 
Else
  Do Y

Then you could use the NEWID() function in conjunction with some casting and the varbinary function.

For example this code below will return a random number between 0 and 9.

DECLARE @RandomNumber INT

-- roll a dice to get a number between 0 and 9

SELECT @RandomNumber = CAST(RIGHT(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as VARCHAR),1) as INT)

I noticed that if you used a LEFT([code],1) instead of RIGHT([code],1) you would get a large sample of 1's in your result set which would skew the results whereas as the RIGHT function gives a more random sample.

Obviously you could change the second parameter of the RIGHT function to get longer digits e.g use RIGHT([CODE],2) to get a random number between 0 and 20.


There you go a simple way to randomise either a recordset or get a single random number.