Daniel Calbimonte

How to generate random SQL Server test data using T-SQL

January 26, 2017 by

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:

  1. combinations of user names and last names
  2. integer values
  3. real numbers with a specific range
  4. passwords in SQL Server
  5. emails
  6. country names

Requirements

  1. SQL Server
  2. SQL Server Management Studio (SSMS)
  3. 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:

The example will show 1,000,000 rows of names and last names:


Figure 1. Generating all the possible combinations between the first and last name

If you want to generate 34 million rows, you have to replace this line:

With this one:

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:

The code will show 100 values between 1 to 100:


Figure 2. Integer random values generated in SQL Server

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:

If you want to generate real values instead of integer values use these lines replace these lines of the code displayed before:

And use these ones:

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:

The result of the T-SQL statement will be values from 95 to 105.8 °F:


Figure 3. Random real numbers from 0 to 100

If you want real numbers from 6 to 10, change these lines of code:

With these ones:

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:

The values displayed by the T-SQL statements are the following:


Figure 4. Random passwords

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:

The code will extract the first letter of the Firstname and concatenate with the last name and concatenate Hotmail or gmail or yahoo randomly:


Figure 5. Random emails

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:

This table contains 238 countries:


Figure 6. List of countries in the Person.CountryRegion table of the adventureworks

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:

The T-SQL statements will generate a list of countries randomly:


Figure 7. List of countries generated 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.


Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
T-SQL, Testing

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. 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

168 Views