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:
- 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:
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:
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:
Each Area contains Types of values. For example, IT area contains the following types:
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:
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:
Ratio of data sources is clearly visible in this case:
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:
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:
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:
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:
In the Type drop-down list, simply select Full name list and it will instantly apply:
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:
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:
This expression will provide the following results:
- 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:
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:
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:
Once this is selected the Dependent object grid will present in the bottom-right corner of the main form:
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:
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:
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:
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:
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:
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)
