SQL RAND Function 1

SQL RAND Function

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

SQL RAND Function Syntax

The basic syntax of the 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, it will assign the seed value at random.
  • If you specify the seed value then, the result will always be the same

SQL RAND Function Example

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

The first three lines of code will return the random values. If you observe the below screenshot, we called the RAND Function three times and it is returning three different (random) values.

In the next two statements, We used the SQL RAND function directly on the positive numeric values 12 and 150. We also assigned a new name to the result using the ALIAS Column.

Next, We used this Mathematical method directly on the negative numeric values -120 and -980.

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]
SQL RAND Function 1

RAND Function Example 2

In this example, we will use the RAND function inside the WHILE LOOP.

I suggest you refer to both the SUBSTRING, ALIAS, and WHILE LOOP articles to understand the SQL Server query execution.

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

The next statement is returning the same result for each iteration because we specified the seed value as 10.

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;
SQL RAND Function 2