Generating Random Numbers in SQL

Today is Groundhog Day in North America which means we can plan out our weather for the next six weeks. If the groundhog comes out and sees its shadow we’ll have six more weeks of winter but if not we’ll have an early spring. While some people say there’s some science behind it, the whole ceremony of it seems a bit…random. On the topic of randomness, let’s look at generating random numbers in SQL.

Random numbers in SQL are something I don’t use too often but when I do it seems to be something I have to go back and look up. Maybe blogging about it will make it stick in my mind a bit more.

RAND Function

The first way to generate a random number is by using the SQL Server RAND function. This will return a float value. For example:

If you wanted to use RAND to get a number between 1 and 10 instead of a float value, you could use this type of query:

SELECT FLOOR(RAND()*(10))+1;
GO

We can test to see how evenly distributed the numbers will be. Let’s try running this and see how random our numbers are:

CREATE TABLE #RandomCheck (RandNum SMALLINT);

DECLARE @Num AS SMALLINT;
DECLARE @TestCount AS SMALLINT = 10000;

WHILE @TestCount > 0
BEGIN
	SET @Num = (SELECT FLOOR(RAND() * (10)) + 1);

	INSERT INTO #RandomCheck (RandNum)
	VALUES (@Num);

	SET @TestCount -= 1;
END

SELECT RandNum, count(*) AS 'Count'
FROM #RandomCheck
GROUP BY RandNum
ORDER BY RandNum;

They end up being pretty evenly distributed.

NEWID Function

Another option I’ve seen used to get random numbers is the NEWID() function. If we still need a random number between 1 and 10:

SELECT (ABS(CHECKSUM(NEWID())) % 10) + 1;
GO

ABS() is used to get the absolute number. Without it, our range in this example would actually be between -8 and 10. If we substitute this query into our test script from earlier, we’ll see similar results:

Our numbers were pretty evenly distributed using this method as well.

This is the way I’ve gone with for random numbers. If you’re trying to do something like assign random numbers to rows in a table, RAND on its own will give you the same number for every row. You have to be a bit more careful with those types of scenarios.

Don’t Drive Angry

Punxsutawney Phil did see his shadow today to predict six more weeks of winter. As I look outside at the ~5 inches of snow we have on the ground, I’m going to hope he’s wrong.

Thanks for reading!

One thought on “Generating Random Numbers in SQL”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s