Tutorial Gateway

SQL RAND Function

by

The SQL RAND function is a SQL Mathematical function used to return the Pseudo random number between Zero and One. The value generated by the SQL Server RAND function is from 0 (included) and less than 1.

SQL RAND Function Syntax

The basic syntax of the SQL Server RAND Function is as shown below:

SELECT RAND (seed)
FROM [Source]

Seed: It is an integer expression of type int, tinyint, or smallint.

  • If you omit this argument then, SQL Server will assign seed value at random.
  • If you specify the seed value then, the result will always be same

SQL RAND Function Example 1

The RAND Function returns the Pseudo random numbers between 0 to 1. In this example, We are going to check the same and display the output

SELECT RAND() AS [Random Result 1]
SELECT RAND() AS [Random Result 2]
SELECT RAND() AS [Random Result 3]

SELECT RAND(12) AS [Random Result 4]
SELECT RAND(150) AS [Random Result 5]

SELECT RAND(-120) AS [Random Result 6]
SELECT RAND(-980) AS [Random Result 7]

OUTPUT

SQL RAND Function 1

ANALYSIS

Below lines of code will return the random values. If you observe the above screenshot, We called the RAND Function three times and it is returning three different (random) values.

SELECT RAND() AS [Random Result 1]
SELECT RAND() AS [Random Result 2]
SELECT RAND() AS [Random Result 3]

In the below statement, We used SQL RAND function directly on the positive numeric values. We also assigned a new name to the result as ‘Random Result 4’ using the ALIAS Column.

SELECT RAND(12) AS [Random Result 4]
SELECT RAND(150) AS [Random Result 5]

Next, We used the RAND function directly on the negative numeric values

SELECT RAND(-120) AS [Random Result 6]
SELECT RAND(-980) AS [Random Result 7]

SQL RAND Function Example 2

In this example, We are going to use the RAND function inside the WHILE LOOP. I suggest you refer both the SUBSTRING and WHILE LOOP articles to understand the query execution.

DECLARE @i INT 
SET @i = 0;  
 
WHILE @i <= 10
	BEGIN
		SELECT RAND() AS [Random Result],
		       RAND(10) AS [Random Seed Result 1],
		       RAND(10 * @i) AS [Random Seed Result 2]
		SET @i = @i + 1
	END;

OUTPUT

SQL RAND Function 2

ANALYSIS

If you observe the above screenshot, below statement is returning different result for each iteration because we haven’t specified the seed value

RAND() AS [Random Result]

Below statement is returning same result for each iteration because we specified the seed value

RAND(10) AS [Random Seed Result 1]

Placed Under: SQL