Applies to
ApexSQL Generate
Summary
This article explains how to override the global options in ApexSQL Generate and apply settings to the specific tables and columns
Description
Applying settings to children refers to transferring user-defined settings used for test data generation from the whole database down to the individual tables and from the table down to the individual columns. It enables the customization of multiple tables and columns without the need to setup each individually. This process of transposing the settings is also called options inheritance because the child object derives the previously set options from its predecessor i.e. column from the table and table from the database.
Inheriting options can be particularly useful in situations where different sets of options need to be applied on a large number of tables. For example, if a database contains 200 tables, one count of generated rows can be set for the first 50 tables and different count for the rest. The usage of options inheritance in this example avoids the need to setup the count of generated rows for each table individually which could otherwise be time consuming.
Types of options inheritance
In ApexSQL Generate there are two types of options inheritance – Apply settings to tables and Apply settings to columns.
Apply settings to tables provides a way of transferring settings from the database to multiple tables. The options that are included in this type of inheritance are:
- Number of rows – count of rows to be generated for each table
- On error – type of action to be used in case of any irregularities
- Clear data – deletes data from the database before insertion of new records
- Disable insert triggers – disables the execution of SQL triggers while data is being inserted
- Disable delete triggers – disables the execution of SQL triggers while data is being deleted
- Disable check constraints – disables the limit of value range imposed by SQL check constraints
- Unique – enables generating only unique values
- Shuffle – rearranges the order of the generated values
- Loop – continually repeats the generated values for each column until the count of rows for the table is reached
- Allow null – enables null values to be inserted
- Percentage of null – sets the percentage of null values in each column
After selecting a database in the tree view, the options are presented on the right side which need to be set prior to applying settings to the tables.
After setting up the options and selecting Apply settings to tables, a dialog is presented where the tables which will inherit the options can be chosen. To choose the desired table, simply select it from the list of available tables on the left and click on > button, or use >> button to choose all the tables.
|
Quick tip: Press and hold CTRL key to select multiple tables or select two tables while holding SHIFT key to select an array of tables |
After applying settings to the selected tables, the options are being transferred to them, which can be seen if one of the tables which inherited the options is selected from the tree view.
Apply settings to columns works in the exact same manner as the previous example, but this time the options are being transferred from the table to the columns. The options which are inherited by columns are:
- Unique
- Allow null
- Percentage of null
- Shuffle
- Loop
This type of inheritance is useful when the same settings need to be applied on multiple columns. For example, if only the unique values are needed to be generated for certain columns, exactly the same principle should be applied as in the previous example.
After setting the options and selecting the Apply settings to columns, a dialog is presented where the desired columns which will inherit the settings can be selected from the list of available columns.
Note: If a column is marked as “Server assigned” it will not be presented in the list of available columns because SQL Server automatically assigns the values to it and any manipulation on that column is not allowed.
After applying settings to the selected columns, the options are being transferred to them and the generated data is automatically being updated.
Top FAQ’s about Applying settings to children in ApexSQL Generate
Q: What are ‘children objects’?
A: The term ‘child object’ refers to a SQL object which is a descendent of another object in SQL hierarchy. For example, a column is a child object of a table and a table is a child object of a database. Similarly, SQL objects which are predecessors of other objects are called ‘parent objects’. Therefore, the database is a parent object of the table and the table is a parent object of the column.
Q: What options are included in “Apply settings to tables”?
A: The options that are inherited from the database to the tables are Number of rows, Invalid data, Clear data, Disable insert triggers, Disable delete triggers, Disable check constraints, Unique, Allow null, Percentage of null, Shuffle and Loop.
Q: What options are included in “Apply settings to columns”?
A: The options that are included in this type of inheritance are Unique, Allow null, Percentage of null, Shuffle and Loop.
Q: What are the benefits of options inheritance?
A: Options inheritance feature enables transferring settings to children objects instantly. This is especially useful in case of great number of tables/columns where setting options for each object individually could otherwise be time consuming.
Q: Can I set the options for columns directly from database?
A: Yes, when a table inherits the options from the database it automatically updates the settings for all of its columns.
Q: How to apply settings to specific table/column?
A: After selecting a database at the top of the tree view, the options which will be transferred to tables are presented. Once the options are set, Apply settings to tables button opens a dialog from which the tables that need to inherit the options can be selected. The same principle should be used when applying settings from table to columns by using Apply settings to columns.
Q: In which use cases is this feature useful?
A: This feature is especially useful when there is a need to set different options for a great number of tables. Most common usage would be setting different count of generated rows and Clear data options for a specific set of tables while preserving a different set of options on other objects.
Q: What are the default options for tables/columns prior to applying inheritance?
A: Before applying inheritance the default options are:
- Number of rows – 100
- Invalid data – Stop generation
- Clear data – off
- Disable insert triggers – off
- Disable check constraints – off
- Unique – off
- Allow null – off
- Shuffle – on
- Loop – on
The default options can be set by clicking on the Database options button in the Options window. Once the preferred options are set, those settings will be remembered on the next application startup. To restore the recommended settings, select the ApexSQL defaults button in the Options window.
