The SQL RAND function is one of the SQL Mathematical function which is used to return the Pseudo random number between Zero and One. In this article we will show you, How to write / use SQL Server RAND function with example.
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:
|
1 2 |
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
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 |
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
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.
|
1 2 3 |
SELECT RAND() AS [Random Result 1] SELECT RAND() AS [Random Result 2] SELECT RAND() AS [Random Result 3] |
In the below statement, We used RAND function directly on the positive numeric values. We also assigned new name to the result as ‘Random Result 4’ using SQL ALIAS Column.
|
1 2 |
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
|
1 2 |
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
|
1 2 3 4 5 6 7 8 9 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; |
OUTPUT
ANALYSIS
If you observe the above screenshot, below statement is returning different result for each iteration because we haven’t specified the seed value
|
1 |
RAND() AS [Random Result] |
Below statement is returning same result for each iteration because we specified the seed value
|
1 |
RAND(10) AS [Random Seed Result 1] |
Thank You for Visiting Our Blog
Leave a Reply