In SQL Server, the T-SQL RAND() function allows you to generate a random number. Specifically, it returns a pseudo-random float value from 0 through 1, exclusive.

The function accepts an optional argument which provides the seed value. For any given seed value, the results will always be the same.

Syntax

The syntax goes like this:

RAND ( [ seed ] ) 

Where seed is an integer expression (tinyint, smallint, or int) that gives the seed value. If this argument isn’t provided, then SQL Server assigns a seed at random.

Example 1 – No Arguments

Here’s a basic example to demonstrate what happens when we don’t provide an argument.

SELECT RAND() Result;

Result:

+--------------------+
| Result             |
|--------------------|
| 0.0354675287734768 |
+--------------------+

The result is not a constant – it will be different each time you run it.

Here’s an example of what happens when we run multiple RAND() functions together.

SELECT 
  RAND() 'Result 1',
  RAND() 'Result 2',
  RAND() 'Result 3';

Result:

+-------------------+------------------+--------------------+
| Result 1          | Result 2         | Result 3           |
|-------------------+------------------+--------------------|
| 0.999568268427369 | 0.40098746841349 | 0.0606836711764244 |
+-------------------+------------------+--------------------+

Example 2 – Using a Seed Value

As mentioned, you can pass in an argument to determine the seed value. This allows you to influence the output of the function.

SELECT RAND(8) Result;

Result:

+-------------------+
| Result            |
|-------------------|
| 0.713722424011731 |
+-------------------+

In this case, the function will return the same value each time if an equal argument value is provided. This is demonstrated in the following example:

SELECT 
  RAND(8) 'Result 1',
  RAND(8) 'Result 2',
  RAND(8) 'Result 3';

Result:

+-------------------+-------------------+-------------------+
| Result 1          | Result 2          | Result 3          |
|-------------------+-------------------+-------------------|
| 0.713722424011731 | 0.713722424011731 | 0.713722424011731 |
+-------------------+-------------------+-------------------+

We ran the function three times. And because we used the same seed each time, the results were all the same.

Example 3 – Using RAND() as Part of an Expression

You can use the RAND() function as part of an expression.

SELECT RAND()*10 Result;

Result:

+------------------+
| Result           |
|------------------|
| 3.32720913214171 |
+------------------+

Example 4 – Rounding the Result

We can also remove the fractional part by nesting the function inside a function such as FLOOR() or CEILING().

SELECT CEILING(RAND()*10) Result;

Result:

+----------+
| Result   |
|----------|
| 3        |
+----------+

Example 5 – Returning a Random Integer Between Two Numbers

You can also specify that the random number must be between two numbers.

Here’s an example of generating a random number between 5 and 10 (inclusive).

SELECT FLOOR(RAND()*(10-5+1)+5) Result;

Result:

+----------+
| Result   |
|----------|
| 9        |
+----------+

Let’s run the same code multiple times to see the varying results.

SELECT 
  FLOOR(RAND()*(10-5+1)+5) 'Result 1',
  FLOOR(RAND()*(10-5+1)+5) 'Result 2',
  FLOOR(RAND()*(10-5+1)+5) 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| 10         | 7          | 6          |
+------------+------------+------------+