Quickly Generate A Random Number Between 1 and 9

Quickly Generate A Random Number Between 1 and 9

By Strictly-Software

As you should know by now the RAND() function in SQL has the downside that it will return the same value if the same seed is provided to it.

E.G if I call this code multiple times I get the same number for each result:

SELECT RAND(1) AS [RandomNumber]

This always returns 0.713591993212924 for me.

Therefore when people use RAND() they often end up coming up with various ways to generate a unique seed value as well.

One of the quickest ways I have found to return a random number is by using the NEWID() function which generates a random GUID.

If I wanted to return a random record from a recordset I would use the NEWID() function in my ORDER BY statement. 

I use this a lot in my Jobboard code when I want to return a random banner from a banner group like so:

SELECT TOP 1 BannerPK, BannerURL, RedirectURL
FROM   BANNERS
ORDER BY NEWID()

However if I wanted to return a single number between 1 and 9 I could use the following code.

This basically converts the GUID to a VARBINARY value before converting it to a VARCHAR and then taking off the the first character with a LEFT statement.

I have found that this gives a larger proportion of 1's than any other number but it is useful if you want a quick way in SQL to generate a single number (or more if you extend the numbers returned with a string function) e.g LEFT(val,2) or use a MID or RIGHT. Then this code could do the trick.

DECLARE @RANDNO INT
SELECT @RANDOMNO = CAST(LEFT(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS VARCHAR),1) AS INT)

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.