Tutorial Gateway

SQL RAND Function

by

The SQL RAND function is one of the SQL Mathematical function which is used to return the Pseudo random number between Zero and One.

TIP: 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 SQL server RAND Function is used to return 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 for 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 new name to the result as ‘Random Result 4’ using ALIAS Column.

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

Next, We used 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 to refer both the SUBSTRING and WHILE LOOP articles to understand the query execution.

T-SQL CODE

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