What is RAND?
The RAND function gives you – not surprisingly – random numbers and is also one of the most used functions in T-SQL. However, the RAND function is a bit special, so we will look at how it behaves in certain scenarios below.
How can you work with RAND?
Let’s start with you generating a simple random number. The following call returns a random number between zero and one:
SELECT RAND()
If you run this query multiple times, you will get a new number each time. Now let’s try to write several random numbers into a table. For this purpose we will first create a temporary table:
DROP TABLE IF EXISTS #Randomness
CREATE TABLE #Randomness (
MyNumber Decimal(10,2)
)
Now we try to insert random numbers:
INSERT INTO #Randomness (MyNumber) VALUES (RAND()), (RAND()), (RAND()) SELECT * FROM #Randomness
But now you have the possibility to pass a so-called “seed” to the RAND function, i.e. a number on which the random numbers are calculated. Now let’s do this in our example:
INSERT INTO #Randomness (MyNumber) VALUES (RAND(100)), (RAND()), (RAND()) INSERT INTO #Randomness (MyNumber) VALUES (RAND(100)), (RAND()), (RAND()) SELECT * FROM #Randomness
As a result of these two INSERT statements, you will now see not six completely random numbers in your table, but three random numbers, each inserted twice. This means that by setting the seed value, you determine the sequence of random numbers. If you remove the seed at the second INSERT statement, you will get truly random numbers here. Typically, the return value of ‘EDGE’ is a floating-point number with a range of values between 0 and 1. If you want to get back larger values, you must scale accordingly by multiplying by the maximum value you are interested in (or double the maximum value if you want to see negative results). So this is how you generate random numbers between 0 and 10:
INSERT INTO #Randomness (MyNumber) VALUES (10*RAND()), (10*RAND()), (10*RAND()) SELECT * FROM #Randomness
And to get values between -5 and 5, subtract 5 from the result:
INSERT INTO #Randomness (MyNumber) VALUES ((10*RAND())-5.0), ((10*RAND())-5.0), ((10*RAND())-5.0) SELECT * FROM #Randomness
Now, if you’re interested in random integer numbers:
INSERT INTO #Randomness (MyNumber) VALUES (CAST(((10*RAND())-5.0) AS INT)) ,(CAST(((10*RAND())-5.0) AS INT)) ,(CAST(((10*RAND())-5.0) AS INT)) SELECT * FROM #Randomness
And of course you can also generate random date values via RAND. Here you generate a random date that is in the last year, where the time part of the Datetime column always corresponds to the time part of the execution:
DECLARE @startdate datetime = DATEADD(Year, -1, GETDATE()) DECLARE @enddate datetime = GETDATE() SELECT DATEADD(DAY, RAND()*DATEDIFF(DAY, @startdate, @enddate), @startdate)
If you also want to randomize the time, just replace DAY with SECOND:
DECLARE @startdate datetime = DATEADD(Year, -1, GETDATE()) DECLARE @enddate datetime = GETDATE() SELECT DATEADD(SECOND, RAND()*DATEDIFF(SECOND, @startdate, @enddate), @startdate)
By the way, you can’t sort in random order with the RAND function, because the following query always returns the same sort order:
SELECT MyNumber FROM #Randomness ORDER BY RAND()
Instead, you can do things differently here and sort by a value that is randomly generated:
SELECT MyNumber FROM #Randomness ORDER BY NEWID()
You learned today in the Dojo how to create and use random values in SQL Server. The numbers may be random, good SQL is definitely not…
