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.
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.
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!