How to generate random SQL Server test data using T SQL
How to generate random SQL Server test data using T-SQL
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: 123 SELECT TOP 1000000 With this one: 123 SELECT TOP 34000000 The query generates a Cartesian product with all the combinations and TOP limits the number of rows.
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: 123456 select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumberunion allselect id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumberfrom randowvalues If you want to generate real values instead of integer values use these lines replace these lines of the code displayed before: 123456 select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumberunion allselect id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumberfrom randowvalues And use these ones: 1234567 select 1 id, RAND(CHECKSUM(NEWID()))*10000 randomnumberunion all select id + 1, RAND(CHECKSUM(NEWID()))*10000 randomnumberfrom randowvalues The query will show real numbers from 0 to 100
Figure 3. Random real numbers from 0 to 100 If you want real numbers from 6 to 10, change these lines of code: 12345 select 1 id,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber union allselect id + 1,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber With these ones: 12345 select 1 id,CAST(RAND(CHECKSUM(NEWID()))*4 as real) +6 as randomnumber union allselect 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.
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.
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: 12345678910111213141516171819202122232425262728293031 ;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:
Figure 7. List of countries generated randomly
Author Recent Posts Daniel CalbimonteDaniel 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 Latest posts by Daniel Calbimonte (see all) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022
SQLShack
SQL Server training EspañolHow to generate random SQL Server test data using T-SQL
January 26, 2017 by Daniel CalbimonteIntroduction
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 namesRequirements
SQL Server SQL Server Management Studio (SSMS) Adventure Works 2014 Full and Adventure Works DW 2014 databasesGetting 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: 123456789101112 seUSE [AdventureWorksDW2014]GO--Change 1000000 to the number of your preference for your needsSELECT TOP 1000000 c1.[FirstName], c2.[LastName] FROM [dbo].[DimCustomer] c1CROSS JOINDimCustomer c2 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: 123 SELECT TOP 1000000 With this one: 123 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: 12345678910111213141516171819 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: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: 123456 select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumberunion allselect id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumberfrom randowvalues If you want to generate real values instead of integer values use these lines replace these lines of the code displayed before: 123456 select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumberunion allselect id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumberfrom randowvalues And use these ones: 1234567 select 1 id, RAND(CHECKSUM(NEWID()))*10000 randomnumberunion all select id + 1, RAND(CHECKSUM(NEWID()))*10000 randomnumberfrom 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: 1234567891011121314151617181920 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:Figure 3. Random real numbers from 0 to 100 If you want real numbers from 6 to 10, change these lines of code: 12345 select 1 id,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber union allselect id + 1,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber With these ones: 12345 select 1 id,CAST(RAND(CHECKSUM(NEWID()))*4 as real) +6 as randomnumber union allselect 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: 12345678910111213141516 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: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 [email protected], or use a Hotmail or Yahoo account. Let’s take a look to the code: 12345678910111213141516171819202122232425262728 USE [AdventureWorksDW2014]GOWITH randomas(SELECT TOP 10000 c1.[FirstName], c2.[LastName],CAST(RAND(CHECKSUM(NEWID()))*3 as int) randomemail FROM [dbo].[DimCustomer] c1CROSS JOINDimCustomer 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'ENDfrom 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: Figure 5. Random emails6 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: 123456 SELECT ROW_NUMBER() OVER(ORDER BY Name) AS id, [Name] FROM [AdventureWorks2016CTP3].[Person].[CountryRegion] 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: 12345678910111213141516171819202122232425262728293031 ;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:
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.Author Recent Posts Daniel CalbimonteDaniel 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 Latest posts by Daniel Calbimonte (see all) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022