SQL Random Function to Generate Random Numbers with Examples

How it works

dbForge Data Generator for SQL Server is a visual data generation tool that enables you to populate SQL databases with random test data quickly and easily. First, you select the database you want to populate with data and set up data generation options. Next, you select tables and columns to be populated. The tool analyzes the column name, its data type, and properties, and automatically assigns the appropriate data generator. In the case of relationships between tables, dbForge Data Generator can preserve data integrity and consistency of your databases. Once done, you can start working with test data, for example, use it for load testing or export a sql file to a csv file.

Random function in SQL Server

The RANDOM function generates a random decimal number from 0 (inclusive) through 1 (exclusive) or within the specified range. The syntax of the SQL random function is as follows: RAND([seed]) where seed is an optional parameter that refers to the tinyint, smallint, or int data type. If you do not specify a seed value, SQL Server generates a random number. When specified, the function returns the same sequence of random numbers within the session. If you want to receive a different value, you should use either different sessions or different seed values.

SQL RAND() example

Let's compare two examples and see how the RAND() function generates a random number in decimals between 0 to 1. In the first example, we omit the seed, while in the second one, we specify the value (5) for the seed.

SELECT RAND();
SELECT RAND(5);

As you can see, in the first example, the RAND() function generates a random number in decimals between 0 to 1. Each time you execute the statement, it returns different random values.

On executing the SELECT RAND(5) statement with the specified seed value multiple times, the values you get are the same.

ROUND function in SQL Server

The ROUND function in SQL returns a number rounded to the specified number of decimal. The ROUND function can be used along with the RAND function when you need to generate a random value within the specified range (m, n). The syntax is as follows:

ROUND(RAND() * n, m);

In this case, the output value will be equal or higher than n (the smallest number) and less than m (the largest number).

How to generate a random decimal number within the range

Next, let's demonstrate how you can use the ROUND function along with the RAND function to generate a random decimal number within the specified range. In the example, we select the range of numbers between 1 to 10. The decimal value will be round to one decimal place.

How to generate a random number between 1 and 10

In the example, we are going to generate a random number between 1 and 10 in SQL Server. It should be noted that the random decimals to be returned will be greater than 1 and less than 10 but will not be equal to 1 or 10.

If we execute the following statement,

SELECT RAND()*(10- 1) + 1 AS random_number_one
RAND()*(10- 1) + 1 AS random_number_two
RAND()*(10- 1) + 1 AS random_number_three;

where 10 is the maximum value in the range, while 1 is the minimum value.

In the output, we see that the function returns different decimal values that refer to the specified range from 1 to 10.

Select a random integer number between 1 and 1000

When you need to generate a random integer number within the specified range in SQL Server, you can use the FLOOR function with the RAND function:

FLOOR(RAND() * (max_value- min_value + 1)) + min_value

Let's now generate a random integer number within the range of 1 and 1000 where 1 is the minimum value and 1000 is the maximum value. As mentioned, whenever you execute the query, SQL Server will return different random integer numbers.

SELECT FLOOR(RAND() * (1000 - 1 + 1)) + 1;

Order rows by random in SQL Server

Except for calculating random numbers, we can use the RANDOM function for the operations with database objects, for example, to sort a SQL query list in random order. To do that, in SQL Server, we need to use the NEWID function in the ORDER BY clause. However, keep in mind that it would be better to use it for short lists. Otherwise, SQL performance may get worse.

For demo purposes, let's form a list of films from the SQL table film of the sakila database so that it can be played by random.

So, first, retrieve data from the table. As you can see, all the rows are arranged in sequential order.

Next, to get a list shuffled, execute the following SQL statement with the NEWID function in the ORDER BY clause.

Random number generator for SQL Server

In practice, it is much easier to use a SQL data generator tool to generate random numbers, especially when you work with a huge volume of data. The Devart team developed the top data generator tool as part of the dbForge product line for SQL Server - dbForge Data Generator for SQL Server. It is a powerful GUI tool that enables you to generate test data of any complexity and volume within a few clicks. Moreover, the tool contains all the required features and generators to cut the time needed for data generation and turn it into a real pleasure. Among its main features, you can also evaluate the following:

• Automatically identify the data type and use the appropriate data generator
• Support for multiple SQL data generators
• Support for foreign key that help maintain consistency of random data across multiple tables
• Use check constraints to keep domain data integrity
• Customize predefined generators to suit your needs
• Automate data generation through the command line
• Preview data to be generated
• Integrate the tool into SQL Server Management Studio