Which Of The Following Will Enter Random Data To The Inputs Of An Application

Introduction

In this article, we will talk well-nigh generating random values for testing purposes.

I once had a customer with software that worked fine in the demo with xxx rows, just after some months, the software had more than a meg rows and it became very deadening. The trouble was non 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, information technology is very important to generate data and test the software with millions of rows. This is not e'er an easy task. In this article, we will requite you some useful T-SQL tips that may assist or at least inspire you on this. In general, random information is very useful for testing purposes, to learn most query efficiency, demos and more than.

In this commodity, we will teach how to generate up to a meg 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
state names

Requirements

SQL Server
SQL Server Management Studio (SSMS)
Run a risk Works 2014 Full and Take a chance Works DW 2014 databases

Getting started

1. Generate a million first and concluding names

In the commencement example, we will employ 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 cantankerous bring together y'all tin 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 final names:

USE [ AdventureWorksDW2014 ]

-- Alter 1000000 to the number of your preference for your needs

SELECT Top million

c1 . [ FirstName ] ,

c2 . [ LastName ]

FROM [ dbo ] . [ DimCustomer ] c1

CROSS JOIN

DimCustomer c2

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

Figure i. Generating all the possible combinations between the beginning and last name

If you desire 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 Acme limits the number of rows.

2. Generate random integer values

The post-obit instance will show how to create a table of k rows with random values from i to 100. We volition use the RAND function to create random values and CHECKSUM(NEWID()) to generate distinct values. Nosotros utilise the bandage to convert the values from real to integer:

three

xvi

xviii

nineteen

with randowvalues

as (

select 1 id , Cast ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 100 as int ) randomnumber

-- select 1 id , RAND ( CHECKSUM ( NEWID ( ) ) ) * 100 randomnumber

wedlock all

select id + i , Cast ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 100 as int ) randomnumber

-- select id + 1 , RAND ( CHECKSUM ( NEWID ( ) ) ) * 100 randomnumber

from randowvalues

where

id < m

)

select *

from randowvalues

Selection ( MAXRECURSION 0 )

The code will show 100 values between 1 to 100:

Effigy 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 yous desire to generate values from 1 to 10000 change these lines:

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 desire to generate real values instead of integer values use these lines replace these lines of the lawmaking displayed before:

select 1 id , CAST ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 10000 as int ) randomnumber

union all

select id + one , CAST ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 10000 as int ) randomnumber

from randowvalues

And utilize these ones:

select 1 id , RAND ( CHECKSUM ( NEWID ( ) ) ) * 10000 randomnumber

union all

select id + i , RAND ( CHECKSUM ( NEWID ( ) ) ) * 10000 randomnumber

from randowvalues

The query will show real numbers from 0 to 100

three. Random real numbers with a specific range

Another typical request is to provide random values with specific ranges. The post-obit example volition bear witness a range of temperatures in °F (I really adopt the metric organisation, 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.seven–99.5 °F, college values means fever, Hyperthermia and lower values Hypothermia).

In this example, nosotros volition generate values between 95 to 105.8 °F:

iii

vii

nine

nineteen

with randowvalues

equally (

-- 10.8 is the difference betwixt 105.eight minus 95

select one id , Bandage ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 10.8 equally existent ) + 95 as randomnumber

union all

select id + ane , CAST ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 10.8 as existent ) + 95 equally randomnumber

from randowvalues

where

id < 100

)

select *

from randowvalues

Pick ( MAXRECURSION 0 )

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

Figure 3. Random real numbers from 0 to 100

If you want real numbers from half-dozen to ten, change these lines of code:

select 1 id , Bandage ( RAND ( CHECKSUM ( NEWID ( ) ) ) * ten.8 as existent ) + 95 equally randomnumber

marriage all

select id + i , CAST ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 10.8 equally real ) + 95 every bit randomnumber

With these ones:

select 1 id , Bandage ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 4 as existent ) + 6 as randomnumber

union all

select id + 1 , Cast ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 4 every bit real ) + 6 as randomnumber

Where half-dozen is the minimum value and four is the difference betwixt 10 and half dozen.

4. Random passwords in SQL Server

Another mutual request is to generate passwords. This instance is used for initial passwords that will be inverse latter by the user or when the user forgets the password.

The following example will generate 100 passwords:

with randowvalues

every bit (

select 1 id , Catechumen ( varchar ( xx ) , CRYPT_GEN_RANDOM ( ten ) ) as mypassword

wedlock all

select id + 1 , Catechumen ( varchar ( 20 ) , CRYPT_GEN_RANDOM ( 10 ) ) as mypassword

from randowvalues

where

id < 100

)

select *

from randowvalues

Selection ( MAXRECURSION 0 )

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

Effigy 4. Random passwords

We use the CRYPT_GEN_RANDOM function to generate passwords and nosotros will and so convert them to a varchar. The function returns hexadecimal values and we catechumen information technology to characters.

5. Generating random emails

The following example, will generate some passwords. We will use the Showtime names and last names of the example 1 of the table DimCustomer to generate random false emails in SQL Server. If we have for case a Client named John Smith, we will generate an email that can be jsmith@gmail.com, or use a Hotmail or Yahoo business relationship. Let'southward have a expect to the code:

ane

five

ten

thirteen

xix

USE [ AdventureWorksDW2014 ]

WITH random

equally

(

SELECT TOP 10000

c1 . [ FirstName ] ,

c2 . [ LastName ] , CAST ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 3 as int ) randomemail

FROM [ dbo ] . [ DimCustomer ] c1

Cantankerous JOIN

DimCustomer c2

)

select

Firstname ,

Lastname ,

email =

CASE

when randomemail = 0 then

lower ( left ( FirstName , 1 ) + [ LastName ] ) + '@hotmail.com'

when randomemail = i then

lower ( left ( FirstName , 1 ) + [ LastName ] ) + '@gmail.com'

else

lower ( left ( FirstName , i ) + [ 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:

Figure 5. Random emails

6. Generate land 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 role:

SELECT ROW_NUMBER ( ) OVER ( ORDER By Name ) As id ,

[ Proper noun ]

FROM [ AdventureWorks2016CTP3 ] . [ Person ] . [ CountryRegion ]

This table contains 238 countries:

Effigy 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 full number of countries) nosotros volition use an inner join to bring together the random numbers with the countries and generate land names randomly:

fifteen

; with countries

every bit

(

-- Create a country id and a country name . The countryid will be used to -- - join with the random numbers

SELECT ROW_NUMBER ( ) OVER ( Order Past Proper noun ) Equally countryid ,

[ Name ]

FROM [ AdventureWorks2016CTP3 ] . [ Person ] . [ CountryRegion ]

) ,

-- - Create 1000 random numbers from 1 to 238

randowvalues

equally (

select 1 id , CAST ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 238 equally int ) randomnumber

union all

select id + 1 , Bandage ( RAND ( CHECKSUM ( NEWID ( ) ) ) * 238 as int ) randomnumber

from randowvalues

where

id < 1000

)

-- - Bring together 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:

Effigy 7. List of countries generated randomly

Conclusions

Generate random values for testing can be difficult. All the same, this article can be useful to inspire you lot to create your own data. Sometimes nosotros tin can use existing tables to generate more values. Sometimes we tin create the information from nothing. In this example, we show how to create data using the Random office.

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.

Writer
Recent Posts

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 xiii years of feel working with different databases.

He has worked for the government, oil companies, spider web sites, magazines and universities around the globe. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server grooming materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte