SQL Data Generator 3

SQL Data Generator uses generators to create the data for the tables that you choose to populate.

Different generators are used to create different types of values, and to enable you to define specific parameters for the values. SQL Data Generator automatically assigns a generator to each column based on information such as table name, column name, data type, and any constraints; otherwise the Regular Expressions Generator is assigned.

Selecting a generator

To select a generator for a column, in the Tables to populate list, click to view the columns for the table you want to use, and then click a column:

In the Column generation settings pane, you can then use the Generator list to select the generator you want to use:

The Generator list only includes generators that create data of the same data type as the column. For example, if the column type is int, only generators that create integer values are available in the list.

SQL Data Generator provides a number of pre-defined generators, such as FirstName, WorkingAge, Country, and so on. These generators are grouped by subject area in the Generator list. You can change the settings for these generators as required.

In addition, SQL Data Generator provides some non-specific generators for you to customize:

SQL Type lists a generator for each SQL data type (except CLR)
Generic lists some basic generators

See also:

Uniqueness

Many of the generators have a Set unique setting. When this check box is selected, SQL Data Generator makes the values that are generated for the column unique.

If the column schema has a uniqueness constraint (such as a unique index or primary key), Set unique is selected by default. However, you can override the uniqueness for the column by clearing the check box. For example, you may want to do this if the uniqueness constraint applies across multiple columns, and you know that another of the columns is unique. A warning is displayed, but you can proceed with the generation.

Generators that do not offer the Set unique option are not available for columns that have a uniqueness constraint, except for the SQL statement generator.

If Set unique is selected but there are not enough unique values to display in the preview, a warning is displayed. However, you can proceed with the generation.

You can change the number of values to be displayed in the preview by changing your application options from the Tools menu.

Check constraints

When SQL Data Generator automatically assigns generators to the columns in a new project or new schema, it sets the generator parameters to take account of any check constraints.

However, it is not always possible to set the generator parameters appropriately. When you generate data, if the values generated do not comply with a check constraint, data generation for that table is stopped and an error is reported.

You can set up the project so that check constraints are not enforced when the data is generated. To do this, clear the Enforce check constraints check box in the project configuration options (click Edit Project and select the Options tab).

Foreign keys

When SQL Data Generator automatically assigns generators to the columns in a new project or new schema, the Foreign Key generator is assigned to all columns that have foreign key constraints.

You cannot change the generator, but you can change the settings for the Foreign Key generator.

In the example below, Table 2 Column 1 references Table 1 Column A, and Table 2 Column 2 references Table 3 Column a.

SQL Data Generator assigns the Foreign Key generator to Column 1 and Column 2. You can change the settings for these columns individually.

For a composite foreign key, the generator settings are the same for each of the columns; if you change the settings on one column, they are automatically changed on the others. In the example below, changing the generator settings for Column 2 in Table 2 also changes the settings for columns 1 and 3.

Similarly, if two or more composite foreign keys overlap, the generator settings are the same for each of the foreign keys. In the example below, changing the generator settings for Column 4 in Table 2 also changes the settings for Columns 1, 2, and 3 in Table 2.

In addition, when two composite foreign keys overlap, for the overlapping column(s) SQL Data Generator uses values that appear in both referenced tables; that is, if a value appears in one referenced table but not in the other, that value will not appear in the generated data. In the example above, only values that appear in both Table 1 Column C and Table 3 Column c will be used for Table 2 Column 3.

A NULL value in a composite foreign key is NULL across all of the columns in the foreign key.

SQL Data Generator cannot display preview values for the Foreign Key generator; is displayed instead.

Foreign Key (manual) generator

You can create a single-column foreign key by using the Foreign Key (manual) generator, which is available under the SQL Types category.

There is no restriction on the data type of the column you select. However, if possible, you should select a column with the same data type. If you select a column with a different data type, SQL Data Generator attempts to convert the values when the data is generated; if SQL Data Generator is unable to convert the data, the data generation may fail.

You cannot create a self-referential foreign key using this generator.

SQL Data Generator cannot display preview values for the Foreign Key (manual) generator; is displayed instead.

Dependencies

SQL Data Generator takes dependencies into account when defining the order in which tables' data will be generated.

If there are any circular dependencies, is displayed next to the relevant columns in the Tables to populate pane, and Generate Data is not available.

Computed columns

If your database contains computed columns, SQL Data Generator generates data for the referenced columns. The values for the computed column are then calculated by SQL Server.

Generating XML

There are a number of ways in which you can generate XML values:

use the XML or Python script generator to generate XML strings
use the Regex generator and write a regular expression that obeys the XML definition
use the File Import generator to import XML files
use the SQL Statement generator to retrieve values from another database that contains schema-validated XML

Generating real numbers

When you use the real SQL type generator, if you set Min or Max to be a large value, sequential distribution will not produce sequential values because the increment cannot be set high enough.