July 22, 2021

The SQL RAND function generates a random number between the range of 0 and 1. The number can be greater than equal to zero but less than one.

Description

The RAND function gives a completely random number if no seed value is specified. The RAND function produces a repetitive random number for all the successive RAND calls in an SQL connection in case of a provided seed value.

SQL RAND Usage Notes

The seed argument of the SQL RAND function is an integer value. The seed value datatype can be int, smallint, or tinyint. If the seed value is not specified, the SQL server assigns a random seed value to the RAND function.

In the case of a specified seed, the output is always the same for that value. Also, all the subsequent RAND calls after a seeded RAND function will produce results according to the seeded value.

The number generated is a pseudo-random number.

The RAND function returns a float datatype.

Syntax

RAND( seed (optional))

SQL RAND Examples

The following query is a simple example of using a RAND function.

SELECT RAND() AS RandomNumber;
/* Answer */
SELECT RAND() AS RandomNumber

The query returns a different random number on each run.

SELECT RAND(500) Seed500, RAND() Rand1, RAND() Rand2
/* Answer */
SELECT RAND(500) Seed500, RAND() Rand1, RAND() Rand2

The above query runs the first RAND function with a seeded value. The following RAND functions generate output based on the seeded call. Moreover, if we run this query multiples times for a single SQL connection, the output numbers remain the same.

Generate A Random Number Within a Range

The following syntax generates a random number within a range from the value a up to but not including b.

SELECT RAND()*(b-a)+a;

Here is a more tangible example where we generate a random value, r, from 8 to 20 noninclusive ( 8 <= r < 20 )

SELECT RAND()*(20-8)+8 r;
/* Answer */
SELECT RAND()*(20-8)+8 r;

See Also

About the authorĀ 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>