The RAND() function in SQL Server returns a pseudo-random float value from 0 through 1, exclusive.

This function can be deterministic or nondeterministic, depending on how it’s invoked.

Deterministic functions always return the same result for a given set of input values and given the same state of the database. Nondeterministic functions may return a different result with the same set of input values and even if the database state remains the same.

The RAND() function can be called in two ways; with a seed, and without a seed. If you call it without a seed, it’s nondeterministic. If you call it with a seed, it’s deterministic.

In other words, for a specified seed value, the result returned is always the same.

But there is a gotcha: Sometimes calling RAND() without a seed is deterministic. I explain this below.

Syntax

First, here’s the syntax:

RAND ( [ seed ] )

The square brackets means that the seed argument is optional.

Example 1 – No Seed

Here I call RAND() five times without a seed.

SELECT RAND() AS [No Seed]
UNION ALL
SELECT RAND()
UNION ALL
SELECT RAND()
UNION ALL
SELECT RAND()
UNION ALL
SELECT RAND()

Result:

+-------------------+
| No Seed           |
|-------------------|
| 0.2054995913191   |
| 0.821844434880088 |
| 0.4204955495022   |
| 0.286702661673299 |
| 0.394385747185196 |
+-------------------+

Each row has a different value.

Example 2 – With Seed

Here I run the same query, except I add the same seed to each function call.

SELECT RAND(100) AS [With Seed]
UNION ALL
SELECT RAND(100)
UNION ALL
SELECT RAND(100)
UNION ALL
SELECT RAND(100)
UNION ALL
SELECT RAND(100)

Result:

+-------------------+
| With Seed         |
|-------------------|
| 0.715436657367485 |
| 0.715436657367485 |
| 0.715436657367485 |
| 0.715436657367485 |
| 0.715436657367485 |
+-------------------+

In this case, all rows have the same value.

Example 3 – Combine Seed and No Seed in Same Query (Multiple RAND() Calls)

You need to be careful when calling RAND() multiple times in the same connection. If you call RAND() with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call.

So you could inadvertently think that you’re executing RAND() nondeterministically when in fact you’re not.

Here’s an example to demonstrate.

SELECT 
  RAND(100) AS [With Seed], 
  RAND() AS [No Seed], 
  RAND() AS [No Seed]
UNION ALL
SELECT 
  RAND(100) AS [With Seed], 
  RAND() AS [No Seed], 
  RAND() AS [No Seed]
UNION ALL
SELECT 
  RAND(100) AS [With Seed], 
  RAND() AS [No Seed], 
  RAND() AS [No Seed];

Result:

+-------------------+------------------+--------------------+
| With Seed         | No Seed          | No Seed            |
|-------------------+------------------+--------------------|
| 0.715436657367485 | 0.28463380767982 | 0.0131039082850364 |
| 0.715436657367485 | 0.28463380767982 | 0.0131039082850364 |
| 0.715436657367485 | 0.28463380767982 | 0.0131039082850364 |
+-------------------+------------------+--------------------+

Even though the resulting value is different across the columns, each “no seed” call was actually based on the “with seed” call, and therefore, deterministic.

If I shuffle the function calls around, here’s what I get.

SELECT 
  RAND() AS [No Seed], 
  RAND() AS [No Seed],
  RAND(100) AS [With Seed]
UNION ALL
SELECT 
  RAND() AS [No Seed], 
  RAND() AS [No Seed],
  RAND(100) AS [With Seed]
UNION ALL
SELECT 
  RAND() AS [No Seed], 
  RAND() AS [No Seed],
  RAND(100) AS [With Seed];

Result:

+------------------+--------------------+-------------------+
| No Seed          | No Seed            | With Seed         |
|------------------+--------------------+-------------------|
| 0.28769876521071 | 0.100505471175005  | 0.715436657367485 |
| 0.28463380767982 | 0.0131039082850364 | 0.715436657367485 |
| 0.28463380767982 | 0.0131039082850364 | 0.715436657367485 |
+------------------+--------------------+-------------------+