For many years, one of the first sample databases we installed for testing was AdventureWorks. As many of us knew, the data set in AdventureWorks was fairly small. The community came up with several solutions to grow the database, but the one I’ve used the most is a nice enlargement script from Jonathan Kehayias (b|t)

But AdventureWorks has been around for almost 10 years, and SQL Server has changed quite a bit in that time. To showcase all of SQL Server 2016 and 2017’s features, Microsoft has given us a new sample database that replaces AdventureWorks. That database is WideWorldImporters. You can download the latest version of WideWorldImporters from Github.

Once you restore the backup of WideWorldImporters, you’ll see that the data is fairly small, again. This was intentional, to keep the download size small. But this time, Microsoft has provided us with a built in stored procedure that will generate new data for you. That procedure is named DataLoadSimulation.PopulateDataToCurrentDate.

The procedure accepts the following parameters:

  • @AverageNumberOfCustomerOrdersPerDay int – The average number of orders per day
  • @SaturdayPercentageOfNormalWorkDay int – The percentage of orders placed on a Saturday
  • @SundayPercentageOfNormalWorkDay int – The percentage of orders placed on a Sunday
  • @IsSilentMode bit – Print detailed activity for the day to the results window
  • @AreDatesPrinted bit – Print the working day to the results window.

Here are the parameters that I’ve used below:

use WideWorldImporters
go

DECLARE @RC int
DECLARE @AverageNumberOfCustomerOrdersPerDay int
DECLARE @SaturdayPercentageOfNormalWorkDay int
DECLARE @SundayPercentageOfNormalWorkDay int
DECLARE @IsSilentMode bit
DECLARE @AreDatesPrinted bit

-- TODO: Set parameter values here.

set @AverageNumberOfCustomerOrdersPerDay = 40
set @SaturdayPercentageOfNormalWorkDay = 20
set @SundayPercentageOfNormalWorkDay = 10
set @IsSilentMode = 1
set @AreDatesPrinted = 1

EXECUTE @RC = [DataLoadSimulation].[PopulateDataToCurrentDate] 
   @AverageNumberOfCustomerOrdersPerDay
  ,@SaturdayPercentageOfNormalWorkDay
  ,@SundayPercentageOfNormalWorkDay
  ,@IsSilentMode
  ,@AreDatesPrinted
GO

The parameter @AverageNumberOfCustomerOrdersPerDay is the average number of orders per day. The actual number will be around this value.
The parameter @SundayPercentageOfNormalWorkDay is the percentage of orders generated on a Sunday. If your @AverageNumberOfCustomerOrdersPerDay is 40 and @SundayPercentageOfNormalWorkDayis 10, you should see around 4 orders per Sunday.

The parameter @SaturdayPercentageOfNormalWorkDay is the percentage of orders generated on a Saturday. If your @AverageNumberOfCustomerOrdersPerDay is 40 and @SaturdayPercentageOfNormalWorkDay is 20, you should see around 8 orders per Saturday.

The parameter @IsSilentMode controls the verbosity of the stored procedure. If @IsSilentMode = 1, you will not see any details. If @IsSilentMode = 0, the procedure will list all of the tables with new data for the current day.

The parameter @AreDatesPrinted controls wheter the stored procedure prints the current working day or not.

This is sample output when @AverageNumberOfCustomerOrdersPerDay = 40, @IsSilentMode = 0, and @AreDatesPrinted = 1:

Tue Aug 01, 2017
 
Receiving stock from purchase orders
Changing 1 passwords
Activating 0 logons
Creating 50 customer orders
Picking stock for customer orders
Processing customer payments
Invoicing picked orders
Placing supplier orders
Recording invoice deliveries
Adding 0 customers
Adding 0 stock items
Adding special deals
Modifying a few temporal items 
Recording delivery van temperatures
Recording cold room temperatures
 
Wed Aug 02, 2017
 
Receiving stock from purchase orders
Changing 0 passwords
Activating 0 logons
Creating 61 customer orders
Picking stock for customer orders
Processing customer payments
Invoicing picked orders
Placing supplier orders
Recording invoice deliveries
Adding 0 customers
Adding 0 stock items
Adding special deals
Modifying a few temporal items 
Recording delivery van temperatures
Recording cold room temperatures

A warning, this procedure is pretty slow. Microsoft estimates around 10 minutes per year. I’ve seen DataLoadSimulation.PopulateDataToCurrentDate run slower, but this was on non production systems.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter