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 below:

Select random row from a table, solution: Use NewID function:

 

This solution describes a straightforward method of generating randomly sorted result sets in SQL Server. This procedure has a number of potential uses, such as displaying a few randomly chosen news article headlines on a website, or it could be used to randomly select a few advertisements while ensuring the same advert isn't always displayed at the top of the advertising space.

Although it is possible to introduce randomness in SQL Server using time functions, in practice this does not work (especially in stored procedures) because of the speed of execution of the SQL statements [hence many or all of the rows could be returned in exactly the same instant of time]. A far better alternative is, therefore, to use the NewID function to create a unique identifier for each row returned. This returns GUID-like identifiers such as AF53DB47-766F-44B7-82EC-7459E353B3F3.The results set can then be ordered by this column.

The use of the NewID function is shown in this example stored procedure shown below:

CREATE PROCEDURE sp_GetAdverts

(

@MaxNumberOfAdverts int

)

AS

set rowcount @MaxNumberOfAdverts 

select top 100 t_Adverts.AdvertID,

    t_Adverts.TargetURL,

    t_Adverts.AltTag,

    newID() as 'RandomColumn'

from t_Adverts

where getdate() between t_Adverts.AdvertStartDate and t_Adverts.AdvertEndDate

order by newID()

GO