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.