About generators - SQL Data Generator

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.

When you select a column in the Tables to populate pane, Generators lists only the 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

  For information about the generic generators, see Generic generators.

For full details about customizing generators, see Customizing generators.

For information about how you can create your own generators, see Creating new generators.

Data types supported by the supplied generators

To see a matrix of the data types that are supported by the supplied generators, see the table of supported data types by generator (PDF)

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. (Note that you can change the number of values to be displayed in the preview by changing your application options, which are available 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 generator to generate XML strings
• use the RegExGenerator 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.