Introduction
In this article, we will talk about generating random values for testing purposes.
I once had a customer with software that worked fine in the demo with 30 rows, but after some months, the software had more than a million rows and it became very slow. The problem was not SQL Server, the problem was the application, which was not designed for tables with millions of rows. The customer sued to the software provider and lawyers were needed to create a resolution. If the provider had tested the software with millions of rows, this problem would have never happened.
That is why, it is very important to generate data and test the software with millions of rows. This is not always an easy task. In this article, we will give you some useful T-SQL tips that may help or at least inspire you on this. In general, random data is very useful for testing purposes, to learn about query efficiency, demos and more.
In this article, we will teach how to generate up to a million rows of random data in SQL Server including:
- combinations of user names and last names
- integer values
- real numbers with a specific range
- passwords in SQL Server
- emails
- country names
Requirements
- SQL Server
- SQL Server Management Studio (SSMS)
- Adventure Works 2014 Full and Adventure Works DW 2014 databases
Getting started
1. Generate a million first and last names
In the first example, we will use the DimCustomer table from the AdventureWorksDW database mentioned in the requirements. This table contains 18,000 rows. We will use a cross join to generate all the possible combinations of names and last names. With the cross join you can generate a total combination of 341,658,256 users for your tests. The following example shows how to create a combination of 1 million user names and last names:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
se USE [AdventureWorksDW2014] GO --Change 1000000 to the number of your preference for your needs SELECT TOP 1000000 c1.[FirstName], c2.[LastName] FROM [dbo].[DimCustomer] c1 CROSS JOIN DimCustomer c2 |
The example will show 1,000,000 rows of names and last names:
If you want to generate 34 million rows, you have to replace this line:
|
1 2 3 |
SELECT TOP 1000000 |
With this one:
|
1 2 3 |
SELECT TOP 34000000 |
The query generates a Cartesian product with all the combinations and TOP limits the number of rows.
2. Generate random integer values
The following example will show how to create a table of 1000 rows with random values from 1 to 100. We will use the RAND function to create random values and CHECKSUM(NEWID()) to generate distinct values. We use the cast to convert the values from real to integer:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
with randowvalues as( select 1 id, CAST(RAND(CHECKSUM(NEWID()))*100 as int) randomnumber --select 1 id, RAND(CHECKSUM(NEWID()))*100 randomnumber union all select id + 1, CAST(RAND(CHECKSUM(NEWID()))*100 as int) randomnumber --select id + 1, RAND(CHECKSUM(NEWID()))*100 randomnumber from randowvalues where id < 1000 ) select * from randowvalues OPTION(MAXRECURSION 0) |
The code will show 100 values between 1 to 100:
If you want to generate 10000 values, change this line:
id < 1000
With this one:
id < 10000
If you want to generate values from 1 to 10000 change these lines:
|
1 2 3 4 5 6 |
select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumber union all select id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumber from randowvalues |
If you want to generate real values instead of integer values use these lines replace these lines of the code displayed before:
|
1 2 3 4 5 6 |
select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumber union all select id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumber from randowvalues |
And use these ones:
|
1 2 3 4 5 6 7 |
select 1 id, RAND(CHECKSUM(NEWID()))*10000 randomnumber union all select id + 1, RAND(CHECKSUM(NEWID()))*10000 randomnumber from randowvalues |
The query will show real numbers from 0 to 100
3. Random real numbers with a specific range
Another typical request is to provide random values with specific ranges. The following example will show a range of temperatures in °F (I really prefer the metric system, but I will do an exception this time).
The human body has the following fluctuations of temperature: 95 to 105.8 °F (Normal temperature is from 97.7–99.5 °F, higher values means fever, Hyperthermia and lower values Hypothermia).
In this example, we will generate values between 95 to 105.8 °F:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
with randowvalues as( --10.8 is the difference between 105.8 minus 95 select 1 id,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber union all select id + 1,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber from randowvalues where id < 100 ) select * from randowvalues OPTION(MAXRECURSION 0) |
The result of the T-SQL statement will be values from 95 to 105.8 °F:
If you want real numbers from 6 to 10, change these lines of code:
|
1 2 3 4 5 |
select 1 id,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber union all select id + 1,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber |
With these ones:
|
1 2 3 4 5 |
select 1 id,CAST(RAND(CHECKSUM(NEWID()))*4 as real) +6 as randomnumber union all select id + 1,CAST(RAND(CHECKSUM(NEWID()))*4 as real) +6 as randomnumber |
Where 6 is the minimum value and 4 is the difference between 10 and 6.
4. Random passwords in SQL Server
Another common request is to generate passwords. This example is used for initial passwords that will be changed latter by the user or when the user forgets the password.
The following example will generate 100 passwords:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
with randowvalues as( select 1 id, CONVERT(varchar(20), CRYPT_GEN_RANDOM(10)) as mypassword union all select id + 1, CONVERT(varchar(20), CRYPT_GEN_RANDOM(10)) as mypassword from randowvalues where id < 100 ) select * from randowvalues OPTION(MAXRECURSION 0) |
The values displayed by the T-SQL statements are the following:
We use the CRYPT_GEN_RANDOM function to generate passwords and we will then convert them to a varchar. The function returns hexadecimal values and we convert it to characters.
5. Generating random emails
The following example, will generate some passwords. We will use the First names and last names of the example 1 of the table DimCustomer to generate random fake emails in SQL Server. If we have for example a Customer named John Smith, we will generate an email that can be jsmith@gmail.com, or use a Hotmail or Yahoo account. Let’s take a look to the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
USE [AdventureWorksDW2014] GO WITH random as ( SELECT TOP 10000 c1.[FirstName], c2.[LastName],CAST(RAND(CHECKSUM(NEWID()))*3 as int) randomemail FROM [dbo].[DimCustomer] c1 CROSS JOIN DimCustomer c2 ) select Firstname, Lastname, email= CASE when randomemail =0 then lower(left(FirstName,1)+[LastName])+'@hotmail.com' when randomemail =1 then lower(left(FirstName,1)+[LastName])+'@gmail.com' else lower(left(FirstName,1)+[LastName])+'@yahoo.com' END from random |
The code will extract the first letter of the Firstname and concatenate with the last name and concatenate Hotmail or gmail or yahoo randomly:
6. Generate country names randomly
This last example will show how to generate random country names. We will use the table Person.CounryRegion from the adventureworks database and we will add an id using the Row_number function:
|
1 2 3 4 5 6 |
SELECT ROW_NUMBER() OVER(ORDER BY Name) AS id, [Name] FROM [AdventureWorks2016CTP3].[Person].[CountryRegion] |
This table contains 238 countries:
We will use the list of random numbers of the second example to generate values from 1 to 238 (238 is the total number of countries) we will use an inner join to join the random numbers with the countries and generate country names randomly:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
;with countries as ( -- Create a country id and a country name. The countryid will be used to ---join with the random numbers SELECT ROW_NUMBER() OVER(ORDER BY Name) AS countryid, [Name] FROM [AdventureWorks2016CTP3].[Person].[CountryRegion] ), ---Create 1000 random numbers from 1 to 238 randowvalues as( select 1 id, CAST(RAND(CHECKSUM(NEWID()))*238 as int) randomnumber union all select id + 1, CAST(RAND(CHECKSUM(NEWID()))*238 as int) randomnumber from randowvalues where id < 1000 ) --- Join countries with random numbers to generate country names randomly select randomnumber,c.Name from randowvalues r inner join countries c on r.randomnumber=c.countryid order by id OPTION(MAXRECURSION 0) |
The T-SQL statements will generate a list of countries randomly:
Conclusions
Generate random values for testing can be difficult. However, this article can be useful to inspire you to create your own data. Sometimes we can use existing tables to generate more values. Sometimes we can create the data from zero. In this example, we show how to create data using the Random function.
In this article, we generated millions of first names and last names, random integer values, real values with specific ranges, random passwords, random emails using first and last names and random country names.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023