SQL SERVER – ApexSQL Generate – SQL Server Test Data Generation

Intro

ApexSQL Generate is a test data generation tool for SQL Server. In this article, we’ll introduce the tools’ core features, describe the main types of generators, walk you through an example and, finally, cover some advanced features.

Data generation

ApexSQL Generate populates SQL databases with synthetic test data by assigning a generator to each column. The application automatically recognizes which generators are suited for each column based on the column name and its data type and only these generators will be presented for that specific column.

All the generators have some partially shared options. Which option is presented for a specific generator depends on the generator itself, as some of these are available only for a specific data types:SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 1

  • Shuffle – Randomly reorganizes generated data for a specific column. If this option is presented in conjunction with Seed and Timestamp, when it’s unchecked it disables using the Seed and Timestamp options
  • Randomize data by:
    • Seed – Each value of the Seed option represents a specific sequence of generated data. This way, if necessary, the identical set of data can be used on multiple columns
    • Timestamp – Ensures the uniqueness of the generated set of values
  • Unique – Ensures that there is no repetition of values in the current column
  • Loop – Allows circular repetition of data so that the desired number of rows can be inserted
  • Allow null – Enables insertion of NULL values in the current column. The amount of NULL values is determined by setting the percentage included in this option

Commonly used generators

The Random generator is the most commonly used generator. It can be used on all SQL data types except for server assigned:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 2

Usually, it is distinguished by the option of defining the minimum and maximum values, but this isn’t a case when it’s applied on the columns with a bit, SQL_variant, uniqueidentifier, xml or hierarchyid data types. Than it has only shared options presented:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 3

The Predefined generator contains predefined lists of values. ApexSQL Generate has over 150 predefined lists of values which cover various areas of interest: Personal, Business, Art, Health, Education, Geographical and more:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 4

Each Area contains Types of values. For example, IT area contains the following types:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 5

The Regular expression generator covers a wide spectrum of data types and provides a user with a very high level of data customization. It is presented for all data types except for timestamp, xml, image and server assigned. This generator creates and inserts values by following the Regular expression pattern defined by the user.

Additionally, the Regular expression generator is enhanced with the ability to insert predefined lists, incremental values (numeric, string and binary) or even values from other columns in the same table:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 6

This generator also provides the ability of inserting custom lists from CSV or Text files.
The Foreign key (Manual) generator acts like a regular Foreign key constraint. It references to any column within the currently loaded database with the matching data type and in that way enables using these as a data source for the current column.

This generator has a flexibility in setting the wager of data sources and this is done by slider in the generator settings. In each position, the slider determines the percentage of the data pulled from generator or the database:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 7

Ratio of data sources is clearly visible in this case:

Solarwinds

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 8

Step by step walk through

In this example, we’ll populate the data for a single table with several common column types

  • Connection:By default, when ApexSQL Generate is started, the Connection form will open:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 9

Before selecting a database, the SQL server instance must be specified, as well as the username and the password, if access to the instance is via SQL authentication. Once the server instance is defined, click on the Database drop-down list will present all the existing databases in the given instance. After all is set, you can load the database by clicking the Connect button.

  • Populating table with data:Once the connection to the database is established, the loaded database with all its tables will be previewed in the Main grid:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 10

To populate one table, only that table should be checked. Once the table is expanded, all the containing columns will be previewed as sub items. Next to the column name, the generator automatically assigned to each by the application will be previewed.

Since all the column names have a matching Predefined list, the Predefined generator is automatically assigned to all of these. The values when this generator is assigned will look like this:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 11

If, for example, a full name is needed instead of the first name in the Name column, it can be easily changed:

Click on the Name column in the main grid and the generator settings currently assigned to it will present on the right of the Main grid:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 12

In the Type drop-down list, simply select Full name list and it will instantly apply:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 13

Just like this, cities and states can be changed as the Predefined generator provides lists with cities and countries/states worldwide.

Another example is that for the ZipCode column you can use the Random generator:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 14

By defining the values as shown in the image above, 5 digit number is ensured to be inserted.

If the phone number format isn’t working for you, it can be customized using the Regular expression generator:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 15

This expression will provide the following results:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 16

  • Table properties

 

So far we talked about customizing your data for each table. The thing that wasn’t mentioned before is setting the row count.

Row count is set in the Table settings. These are presented when the table is selected in the main grid:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 17

Note that the Apply settings button must be used only when the Column generation settings are changed.

Also, important thing when generating is to disable Insert and Delete triggers as well as Check constraints. This can be done in database settings which are shown once the database is selected in the Main grid:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 18

All the changes made in this settings must be confirmed on the Apply settings button.

Advanced features

Dependent objects

Including/excluding dependent objects from generation is critical feature when populating a table with the Foreign key constraint. If a table has a column with Foreign key, and the column it references to is in another table, that table too has to be included in the generation process. Otherwise, the generation for the first table will not be possible.

The way to see and include dependent objects in the generation is to turn on the Dependent objects feature in the ribbon> the View tab:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 19

Once this is selected the Dependent object grid will present in the bottom-right corner of the main form:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 20

Parent tables are mandatory to be included in the generation process for the successful generation.

Test data preview

The Test data preview grid shows values that will be inserted instantly when changes are made to any table or column:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 21

Since ApexSQL Generate provides the ability to export prepared data in various formats, there is also an option of pre-export preview in this same grid. These previews can be enabled in the ribbon > the View tab:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 22

In the grid, it’s previewed how the documents will look like once they’re exported. All the changes made, either in table and column settings or in options for any of these export formats are instantly updated in the preview grid:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 23

Action plan and the Post-generation summary

By the default, when starting the generation process, first the Action plan will preview; All the actions that will be executed in the generation process will be listed in the order of execution:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 24

Action items can be grouped for clearer preview. In order to proceed with the generation, the Generate button is clicked as shown in the image above.

The Post-generation summary previews the successfulness of the generation. If some tables failed to reach the defined number of rows it can be seen in this summary. Also, if the reason for this is some mistake while setting up the generation, it will be noted:

SQL SERVER - ApexSQL Generate - SQL Server Test Data Generation 25

In the image above is previewed an example of the warning that occurs when parent table isn’t included in the generation process.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Database Backup and Restore Management and Automation with ApexSQL Backup
Next Post
MySQL – How to Drop Table If Exists in Database?

Related Posts

Leave a Reply

Menu