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:


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


DECLARE @TestCount AS SMALLINT = 10000;

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

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

	SET @TestCount -= 1;

SELECT RandNum, count(*) AS 'Count'
FROM #RandomCheck

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:


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!

2 thoughts on “Generating Random Numbers in SQL”

Leave a Reply

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

You are commenting using your 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