Sunday, February 5, 2023
HomeProductsSQL Server ToolsGenerate Meaningful Test Data with Data Generator for SQL Server

Generate Meaningful Test Data with Data Generator for SQL Server

Why is meaningful test data important?

While developing an application, you need to make sure you are testing it under conditions that closely simulate a production environment. Most tests rely on sample data for testing. If you manually enter data into a test environment, one record at a time using the UI, most probably, you will never build volume and variety of data that your app will accumulate in a few days in production. It is obvious that the behavior will differ. Most likely, the data you insert into a test database will be based on your own usage patterns and will not match real-world usage, leaving significant bugs untested.

When you fill a test database with meaningful test data, it allows the application owner to see and assess what the application will look like, once it released. If you, for instance, develop a CRM-like application, it is going to be very difficult to test it without a number of customer records, like emails, phone numbers, addresses, and others. Meaningful data is diverse and complex and will contain characters that may not behave well with your code, such as Unicode characters or apostrophes. Thus, testing with meaningful data makes your application more reliable and allows you to uncover bugs that are likely to occur in production.

Summarizing the above, without meaningful test data in the test environment, that represents what you will end up within the production, it is quite impossible to predict the way the application will behave after the release.

Why just not to use real data (from a production database) while testing the application? Well, there might be some constraints to do so, e.g. security issues.

Select the right tool

A half year ago, we released the first version of Data Generator for SQL Server. Many good things have happened since that time. These days we roll out Data Generator for SQL Server v3.5 tool that includes 180+ various generators that you may find useful in many cases.


In the following demo, I will show you how to generate and insert a whole heap of data into database.

Generating Data for Empty Database

Let’s have a look at the database structure first.


To generate test data:

  1. On the Standard toolbar, click New Data Generation.
  2. Specify the connection and select a required database.
  3. The Options page will appear. On the Options page, set required options and click Open.

The data generation document window opens. Now you can select columns to be populated, select and customize a generator to use for each separate column. To populate all the columns, select the check box next to the salesdb. You can see the data to be generated (in real-time) in the Preview window.

Selecting generators for individual columns

For a column you select, Data Generator automatically assigns a generator based on the column and table characteristics: name, data type, etc. You may select a different generator for the column and customize the parameters of this generator in the settings pane.

The CustomerID column

Select the CustomerID column. The ID (sequential) generator is automatically assigned, which generates unique identifiers for the column. For this generator, along with the basic settings, you can specify the Min and Max values for the IDs to be generated.


The FirstName column

The First Name (any gender) generator is automatically assigned to this column. The generator provides realistic male first names. In the text box, you can see a predefined regexp template to generate names. The template may be customized by inserting various snippets, counters, files, and columns.


The MiddleInitial column

Select the MiddleInitial column. The Middle Name generator is automatically assigned to this column. The generator provides a wide range of middle names including both the full names and initials. The values to be generated may be customized using regular expressions. For example, if you want only one-character initials to be generated, specify the [A-Z] regular expression in the text box of the Settings pane. The values may also be adjusted by inserting predefined regexp snippets, counters, files, and columns.


The Gender column

Select the Gender column. The Gender generator is automatically assigned to this column. The data grid in the settings pane is designed to show weight ratios and percentages of the male and female values to be generated. The weight ratios can be customized by manually altering the numbers in the data grid. The percentages will be automatically recalculated concurrently with the change of the weight ratios.


The SSN column

Select the SSN column. The US Social Security Number (SSN) is automatically assigned to this column. The generator will populate the column with random numbers. You can customize the numbers in the text box using various regular expressions or by inserting regexp snippets, counters, files, and columns.

For example, with the help of regexp snippets, you can add or generate lower case and uppercase letters, hexadecimal characters, digits without a zero. You can also specify groups, alternatives, repeats, etc. of the generated values.

With the help of the counter, you may get a sequence of digits in increment/decrement order, a sequence of characters in increment/decrement order, and hexadecimal characters in increment/decrement order.

The Avatar column

Select the Avatar column. The Avatar Images generator is automatically assigned to this column. The generator contains more than 150 different avatars. You can specify the fold and a subfolder to load images from, as well as select a file pattern.

The ShippingMethod column

Select the ShippingMethod column. The Shipping Method generator is automatically assigned to this column. The generator provides a wide range of real-world shipping methods, such as USPS Express Mail, FedEx, DHL, Airmail Priority, etc. The list of all shipping methods is visible in the text box of the Settings pane. You can customize this list manually or by adding regexp snippets, counters, columns, files from the drop-down lists below the text box.


While selecting tables and customizing generators, the Preview window displays data to be generated in real-time.


Well, we generated all the required data for our table. Now, it is time to populate the database with this data!

Populating Database

To populate the database, on the Data Generator toolbar, click Populate data to the target database…


Then you can open the SQL script in the internal editor to customize it, save this script to a file, or execute it to populate the target database.


On the next step, you can configure the synchronization options.


The Summary tab allows you to see the data population actions and warnings.

The last thing you need to do is to click Generate to finish the process.

As you can see, the overall process is quite intuitive and doesn’t require too much time to generate a large volume of meaningful test data!

Andrey Langovoy
Andrey Langovoy
Product manager at Devart