Home > Articles > Transact SQL Programming Articles

Generating Random Numbers in Transact-SQL

The Transact SQL Rand function can be used to return a random floating point number between 0 and 1:

SELECT RAND() AS RandomNumber

The Rand function can also be supplied with an integer value (i.e. smallint, tinyint or int) to use as a random seed:

SELECT RAND(@MyInteger) AS RandomNumber

Creating Random Numbers in a Certain Numerical Range

The following SQL code can be used to generate random integers between the values of the @MinValue and @MaxValue variables.

DECLARE @RandomNumber float
DECLARE @RandomInteger int
DECLARE @MaxValue int
DECLARE @MinValue int

SET @MaxValue = 4
SET @MinValue = 2

SELECT @RandomNumber = RAND()

SELECT @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

SELECT @RandomNumber as RandomNumber, @RandomInteger as RandomInteger

The output of this SQL will be a random number between 2 and 12 (inclusive).

Random Numbers in SELECT Statements

An important consideration is that if the RAND function is called within a single query then it will return the same random number. You might, therefore, want to consider a different approach, such as the solution described in the article generating randomly ordered SQL Server result sets.