SQL Random Function to Generate Random Numbers With Examples
Generating random numbers and populating SQL tables with sample data serves many practical goals. Database developers and QA experts use realistic datasets to test database management tools, and automated generation of random data can significantly accelerate their workflows.
Another common use case is generating sample unbiased data for analysis and experimenting. With representative dataset formed with random numbers generated by SQL functions, data analysts and developers can test and validate their ideas.
This article explains how to generate random numbers or select random numbers within different ranges in SQL Server databases with syntax and examples. The detailed description of steps and explanatory screenshots will give you an idea of how you can perform tasks using dbForge Data Generator for SQL Server.
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 an SQL file to a CSV file.
Random function in SQL Server
The RAND 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 defined range
Next, let's see 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 rounded to one decimal place.
How to generate a random number between 1 and 10
In this example, we are going to generate a random number between 1 and 10 in SQL Server. Note 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.
Let's 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.
How to 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;
How to 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 the list shuffled, execute the following SQL statement with the NEWID function in the ORDER BY clause:
SELECT * FROM film ORDER BY NEWID();
Difference between RAND() and NEWID() functions in SQL Server
While both functions generate random values, there are distinct differences in their way of working, purpose, and common use cases.
| Feature | RAND() | NEWID() |
|---|---|---|
| Type | Numeric random number generator | Unique identifier (GUID) generator |
| Returned result | A float value between 0 (inclusive) and 1 (exclusive) | A globally unique identifier (GUID) |
| Repeatability with seed | Yes - accepts a seed value for predictable output | No - always returns a new unique value |
| Random value per row | No - same value for all rows in a single query unless seeded per row | Yes - generates a new unique value per row |
| Common use cases | Generating random floats, integers, and test data values | Row shuffling, unique values for random seeds or primary key |
| Performance on large datasets | Lightweight; better performance for pure number generation | Slightly heavier due to GUID computation |
| Use with CHECKSUM for seed | Often seeded with CHECKSUM(NEWID()) for row-level randomness | Can be used as seed for RAND() when combined with CHECKSUM() |
| Repeatability without seed | No - different each run if no seed is used | No - always generates a new unique ID |
| Ordering rows randomly | Not recommended - produces the same value across rows | Recommended - ORDER BY NEWID() is a common way to shuffle rows randomly |
| Function category | Mathematical function | System function |
Summary of data generation functions in SQL
As we saw, SQL Server supports a number of useful functions that generate random data. Depending on your goal, you can use and combine them to get the expected result. The table below provides an overview of SQL functions used in generating data along with the typical use cases.
| Objective | SQL Expression | Notes |
|---|---|---|
| Decimal in [0,1) - no seed | SELECT RAND(); | Returns different values on each call. |
| Decimal in [0,1) - fixed sequence | SELECT RAND(5); | Produces the same output each session with the same seed. |
| Decimal in [0,1) rounded | ROUND(RAND() * n, m) | m = decimal places, n = your upper bound in the formula. |
| Decimal in [1,10) | SELECT RAND() * (10 - 1) + 1 AS rnd; | Produces decimals ≥ 1 and < 10. |
| Integer in [1,1000] | SELECT FLOOR(RAND() * (1000 - 1 + 1)) + 1; | Uses FLOOR to convert decimals to integers. |
| Shuffle rows randomly | SELECT ... FROM MyTable ORDER BY NEWID(); | Randomizes row order using NEWID(). |
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 capabilities:
- Automatically identify the data type and use the appropriate data generator
- Select from multiple supported SQL data generators
- Maintain consistency of random data across multiple tables through foreign keys
- 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
Conclusion
Generation of realistic datasets is an essential component of many data management and analytical processes. dbForge Data Generator for SQL Server by Devart supports multiple data generation functions addressing various data requirements. Take advantage of the 30-day free trial of this tool to get first-hand experience of its data generation capabilities.
If, however, your database management objectives are more complex and SSMS is your SQL environment of choice, browse our SQL Server management tools for a solution that meets your needs, such as dbForge Studio for SQL Server - an all-in-one IDE for SQL database management.
FAQ
To generate a random number, use the RAND() function that generates a random number from 0 (inclusive) to 1 (exclusive) or within the specified range.
To generate a random number in the range of 1 to 10, execute the following SQL statement:
SELECT RAND() * (10 - 1) + 1;
To generate a random number for each row, use the RAND() function with CHECKSUM(NEWID()) and FLOOR(). For for example, for INT (0 to 2,147,483,647), execute the following query:
SELECT FLOOR(RAND(CHECKSUM(NEWID())) * POWER(2.0, 31)) AS random_number FROM your_table;
Here, POWER(2.0, 31) = 2,147,483,648, the number of possible values in the positive range of INT (0 to 2,147,483,647), ensuring the random integer covers the full positive range.
To generate a random integer, use the FLOOR() function together with the RAND() function. For example, to generate a random integer in the range of 1 to 1000, execute the following SQL statement:
SELECT FLOOR(RAND() * (1000 - 1 + 1)) + 1;
The RAND() function generates a random numeric value, while the NEWID() function generates a globally unique identifier (GUID).
Yes, you can. You can use the RAND() function with a seed value, for example, RAND(5), to get repeatable results.
To insert multiple rows with unique random values into a SQL table, use the NEWID() function that generates unique identifiers for table rows. For example, to insert 10 new rows with unique values, execute the following SQL statement:
INSERT INTO TableName (UniqueColumn) SELECT TOP (10) NEWID() FROM sys.objects
In this syntax, sys.objects is a system table containing rows for each schema-scoped object in the database.
To shuffle rows in a SQL table, use the NEWID() function. It assigns random identifiers to table rows and, when used together with an ORDER BY clause, redistributes them in a random order. For example:
SELECT * FROM film ORDER BY NEWID();
The RAND() function assigns the same random value to all rows in a table. However, if you need per-row randomness, use CHECKSUM(NEWID()), where NEWID() generates a unique identifier for every row and CHECKSUM(NEWID()) converts it into an integer value. This way, each row will have a unique random number.