Rudy Rodarte . com

Information

This article was written on 28 Dec 2017, and is filled under SQL Blogs.

Current post is tagged

, , ,

Generate fresh data for WideWorldImporters

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:

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:

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