SQL 2005+

SQL Server Random Number Generation

by Richard Carr, published at http://www.blackwasp.co.uk/SQLRandom.aspx

The forty-third part of the SQL Server Programming Fundamentals tutorial completes the examination of Transact-SQL's (T-SQL) mathematical functions. This article describes the Rand function, which allows the generation of pseudo-random numbers.

Pseudo-Random Numbers

Most programming languages provide a function or method that permits the generation of random numbers. In fact, it is unlikely that the numbers produced are actually random. Instead, they are usually produced by an algorithm that returns pseudo-random numbers. These are results that have similar properties to random numbers but that, given details of the original state of the generator, can be predicted.

Transact-SQL (T-SQL) includes a pseudo-random number generator function. It is useful when you wish to create one or more values that appear to be random. However, it is not safe to use in situations where predicting the sequence would be problematic. For example, you should not use the function to select the next number in a roulette game, as the predictability of the values would risk the possibility of cheating.

Generating Random Numbers

In this article we will run several T-SQL commands, including one against the JoBS tutorial database. This is the sample database that has been created and populated with data throughout the course of the tutorial. If you do not have an up-to-date version of the database, you can create one by downloading and running the script via the link at the top of the page.

Generating Random Values

Pseudo-random values are generated using the Rand function. The simplest way to use the function is to provide no arguments. The function returns a floating-point number between zero and one. The higher end of the range is exclusive, so the function will never return a value of exactly one.

SELECT Rand()

Generating Random Integers

A common requirement is to generate a random integer within a given range. To do so, multiply the result of the Rand function by the size of the desired range, cast the new value as an integer and add the smallest desired number. For example, to generate random integers between one and ten, the random element is multiplied by ten and the result is increased by one.

SELECT CAST(Rand() * 10 AS INT) + 1

Setting a Seed Value

As mentioned earlier in the article, the Rand function returns pseudo-random numbers. The algorithm uses a seed value to determine the next value in the series, with the seed value changing each time the function is invoked. You can set the initial seed value by passing an integer to the Rand function's only parameter. Once a seed has been specified, the sequence of random numbers can be predicted. This is useful when you wish to use pseudo-random numbers but have repeatable results.

In the following script, the first call sets the seed. The results for the three calls are therefore identical every time you execute the commands.

SELECT Rand(99) -- 0.715418024396227
SELECT Rand()   -- 0.539054083429417
SELECT Rand()   -- 0.386143218540593

Generating Random Numbers in Queries

As with many other T-SQL functions, when used in a query the Rand function is only evaluated once. This means that if you include Rand within the column list of a query, the result set will contain the same random number in each row. You can see this by executing the following query against the JoBS database. If you wish to obtain a different random number per row, you must use an alternative method for generating them.

SELECT EngineerName, Rand() FROM Engineers