Generate realistic test data with a predefined data generator

ApexSQL Generate is a very effective data generator tool that helps developers to populate SQL databases through the predefined generator with hundreds of meaningful data types.

During the application development period, we mainly consider three limited sources. These are:

• Money
• Time
• Human resource

Moving from this idea, we should use these sources carefully and economically. Most likely, a poorly tested application can cause waste to these resources. The following cost of the bug-fixing graph explains that the cost of the bug-fixing is increasing dramatically after the production publishments.

At the same time, fixing the bugs in the production is risky and can cause grumbles from the customer. Briefly, a well-tested application releases gain prestige before the production and prevent from spending more money.

There is no doubt that software testing is a very wide topic and it includes various methods and approaches. A software that is tested with meaningful and realistic data is a crucial point to succeed in the testing operations. A properly prepared test data environment allows us to simulate a close production ecosystem. After this small
information about the importance of software testing and test data generation, we will particularly focus on how we can generate test data for SQL Server. At first, we will review common SQL data generator options.

SQL test data generation methods

In order to create test data for SQL Server, we can use several different techniques. The most prevalent ones are as follows:

Manual test data generation: This option is a very simple and primitive technique in order to create test data generation. Most often, we use SQL queries to perform this option, however, for every new test data requirement, we should code a new query or change the existing one. This technique has some disadvantage such as it is laborious, causes time-wasting and also depends on personal effort.

Restoring test data from the production: This method is based on a very simple approach. Take backup from production and restore the backup and then offer it for test operations. The advantage of this technique is very simple, however, we have to secure sensitive data for regulatory compliance. On the other hand, big data sizes may lead us to time and source wasting because moving and restoring the large files will be very compelling.

Obviously, these two methods have disadvantages and for this reason, we will steer our eyes to another advanced SQL data generator solution of the ApexSQL.

ApexSQL Generate allows us to generate meaningful and realistic SQL test data for SQL Server. Its GUI is intuitive and easy to use and generation process for large data sets is comparatively fast enough. ApexSQL Generate provides the whole options listed below so that we can avoid wasting time in order to create SQL test data:

• Allows generating meaningful and predefined SQL test data
• Easy usage and fast SQL test data generation ability
• Allows automating SQL test data generation
• Exports generated test data from common data sources
• Provides foreign key consistency during SQL test data generation

It also provides various SQL test data generation scenarios. For example:

ApexSQL Generate reads the schema of the database and determines the types of the test data and then according to the column name, it offers a predefined meaningful test data for the usage.Sometimes, in this manner, we can handle the whole task.

Generate SQL test data with predefined generator

In this part of the article, we mentioned about the predefined generator in the ApexSQL Generate and learned the usage of this option with a very simple example. ApexSQL Generator offers an advanced predefined data generator and this generator also has capable data type prediction ability.

Note: In all examples, we will work with an Azure SQL database and a sample table named customers. At the same time, you can perform this example for an on-premise SQL Server versions.

We can create this table through the query:

IF EXISTS(SELECT *
    FROM sys.objects
    WHERE name = 'customers')
DROP TABLE [dbo].[customers];
CREATE TABLE [customers]
([customer_id] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED , 
[first_name]  [VARCHAR](255) NOT NULL, 
[last_name]   [VARCHAR](255) NOT NULL, 
[phone]       [VARCHAR](25) NULL, 
[email]       [VARCHAR](255) NOT NULL, 
[street]      [VARCHAR](255) NULL, 
[city]        [VARCHAR](50) NULL, 
[state]       [VARCHAR](25) NULL, 
[zip_code]    [VARCHAR](5) NULL
);
GO

ApexSQL Generate supports Microsoft Azure SQL as a data generator tool so that we can easily generate SQL test data for Azure SQL. We will launch the ApexSQL Generate. In the Data Source tab, ApexSQL Generate offers two types of metadata source option which are:

The Live database allows us to connect to any on-premise SQL Servers or SQL Azure.

SQL Script allows us to use any SQL script that includes table creation scripts.

We will select the Live database option and directly connect to the Azure SQL database and then click the Load button.

In this process, ApexSQL Generate reads the schema of the database and determines the proper types according to column data types so that we don’t have to deal with this work.

After the loading process, the ApexSQL Generate project window meets us. In this window, we can change the types of test data and we can also set and change various options of the test project.

As you can see in the above image, ApexSQL Generate automatically assigned the meaningful predefined test data according to columns data types and names. In the data preview grid, we can see the sample of the assigned test data:

In the generator panel, we can change the predefined type for the individual columns. For our example, we will change the first_name column as the First name (female) and the changing directly occurs in the data preview tab.

ApexSQL Generate provides 224 total number of the predefined data types so that we can generate more meaningful and realistic SQL test data easily. Also, these datatypes are separated into categories for easy usage so that we can find out the required predefined datatype in a very short time. Under the following categories, we can find various predefined data types:

• Art
• Auto industry
• Business
• Education
• Food and Beverage
• Geographical
• Health
• IT
• Payment
• Personal
• Product

Now, we will generate our first test data in one click. We click the Generate button and approve the action plan.

After the test data generation process, the Post generation summary window meets us and gives detailed information about the test data generation process.

In the Generator panel, we can set the number of SQL test data. With the help of the ApexSQL Generate you can create more than millions of rows of SQL test data into SQL Azure and there isn’t any limitation in the ApexSQL Generate.

Performance benchmark

The crucial factor which is affecting the performance of the SQL Azure is Purchase Models. The below chart illustrates the performance of the SQL Azure in different service tiers and DTUs. In this test, ApexSQL Generate is used as a data generator tool in order to populate the data.

Conclusions

In this article, we discovered essentials of the SQL test data generation methods and then we learned the usage of the predefined generator in ApexSQL Generate. ApexSQL Generate is the most powerful and effective SQL data generator tool to generate test data for SQL Server. SQL test data generation is a pretty common task and if we want to further facilitate this task, we should use ApexSQL Generate.