Share via


Specifying Details of Data Generation for a Column

To generate data, you must create and run a data generation plan. A data generation plan contains the information about which tables and columns you want to fill with data. After you create the plan, you can specify the details of how you want to fill columns with data. For more information, see Data Generation Plans and How to: Create Data Generation Plans.

Specifying Column Details

By specifying the Generator and Generator Output for each column in the Column Details window, you can control what data is generated to fill columns. The following table explains how to specify the details for each column:

Column Name

Details

Column

The name of the column. This column contains a check box. Select the check box to generate data for the column. Clear the check box to leave the column empty. You must generate data for columns that are not nullable, except columns that have a default constraint. The Properties window displays the Allow Nulls and Default Value properties. You can use these properties to decide which columns to fill with data. For more information about how to specify the columns that you want to fill with data, see How to: Specify Columns for Data Generation.

Key

This column contains a picture of a key if the column is a primary or a foreign key column. This column is read-only, and for information only.

Data Type

The SQL data type of the column. This column is read-only and for information only. For more information about SQL data types, see Data Types and Data Types (Transact-SQL).

Generator

The type of data generator that will be used to generate data for this column. Each SQL data type has a default data generator, and the default generator is entered in this column when you first create a data generation plan. You can change the data generator by clicking the drop-down arrow in the column, and selecting a different data generator. The following are important points about how to select data generators:

  • Not all data generator types can be used with all SQL data types. The list displays only the data generators available for the SQL data type of the column.

  • Identity and foreign key columns use the SQL Computed Value and Foreign Key data generators regardless of the SQL data type of the column. You cannot change the data generator for identity and foreign key columns.

For more information, see Standard Data Generator Types and Options (Database Tools/Data Generator/Default Generators).

Generator Output

This column is used for any data generator that produces more than one output value. For example, when you use the Data Bound Generator, you must specify a select query to retrieve data from the data source. After you specify a select query, this column contains a list of the data columns returned by the query. Use the drop-down arrow to select the data that you want to use to fill the column. For more information, see the Connection Information and Select Query properties later in this document.

This column can also be used by custom data generators. One custom data generator can produce more than one output value. For example, a custom data generator could produce dates in one of two distinct date ranges. Then you would use this column to specify which output you want to use.

Setting Column Properties

You can control what data is generated to fill columns by setting the properties of the columns in the data generation plan. To set the properties open the Column Details window and then open the Properties window. There are three categories of properties: Column Information, Database Constraints, and Generator. The Column Information and Database Constraints properties are read-only. They give you information about each column that you can use to help customize your data generation plan. The Generator properties are usually read-write, although some data generators might have read-only properties. The generator properties are the properties that you set to control how the data is generated.

Note

If your Properties window is categorized, you will see the categories. If your Properties window is alphabetical, you will not see the categories. You can use the Categorized and Alphabetical buttons on the toolbar in the Properties window to switch between the two views.

Column Information and Database Constraint Properties

The following table contains the column information and database constraint Properties properties for each column:

Property

Details

Size

The size, in bytes, of the database column.

Allow Nulls

True or False. Specifies whether a column may have NULL values.

Check Constraints

Empty if the column does not have a check constraint. If the column has a check constraint, specifies the formula of the check constraint.

Aa833245.alert_note(en-us,VS.90).gifNote:
Because a column can have more than one check constraint, this property is an array of strings when it is not empty. Each element of the array is the string of the formula of one of the constraints.

Default Value

Empty if the column does not have a default constraint. If the column has a default constraint, specifies the default value.

Foreign Key

Empty if the column does not have a foreign key constraint. If the column has a foreign key constraint, specifies the table and column that the foreign key references.

Primary Key

True or False. Specifies whether a column is part of a primary key on the table.

Unique

True or False. Specifies whether a column is part of a unique constraint.

Generator Properties

The Generator properties are the properties that you set to control how the data is generated. The properties that are available for each column depend on the data generator currently selected for that column. Not all data generators have all properties. For example, the data bound generator has a Connection Information property that you use to specify the connection string to a data source. No other data generator requires this property.

The following table contains the generator properties for each column:

Property

Details

Data Generator Types

Default Value

Percentage Null

The approximate percentage of NULL values that are generated for the column. You can modify this property only if the column allows NULL. This property must be 0 for columns that do not accept NULL values. A value of 0 means that no NULL values are generated. A value of 100 means that all values that are generated are NULL.

All data generators except SQL Computed Value.

0

Seed

The seed value that is used in the random data generation algorithms. The standard data generators are deterministic. You can generate the same random data again if you use the same seed value. You can generate a new (but deterministic) set of random data by changing the seed value.

All data generators except SQL Computed Value.

5

You can change the default value for the Seed property by using the Options page. For more information, see Options (Database Tools/Data Generator/General).

Unique

When this property is true, the data that is generated for the column is unique. This property is used together with the Step property for numeric types.

All data generators except SQL Computed Value, RegularExpression, Image, and Data Bound.

True if the column is part of a unique constraint, False otherwise.

Step

When the Unique property is true, each row is calculated by adding the Step value to the previous row. Step can be negative.

Decimal, Real, Float, Money, DateTime, and the integer data generators.

Based on an existing check constraint if the constraint can be successfully parsed.

smalldatetime: 1 minute

datetime: 1 minute

all other numeric types: 1

Distribution

The statistical distribution curve that the randomly generated data approximates. The options are as follows: Uniform, Normal, Normal Inverse, Exponential, ExponentialInverse.

Decimal, Money, DateTime, Real, Float, and the integer data generators.

Uniform

Min

The minimum value of the data that is generated for the column.

Decimal, Money, DateTime, Real, Float, and the integer data generators.

Based on an existing check constraint if the constraint can be successfully parsed.

smalldatetime: 1/1/1900

datetime: 1/1/1753

all other numeric types: 0

Max

The maximum value of the data that is generated for the column.

Decimal, Money, DateTime, Real, Float, and the integer data generators.

Based on an existing check constraint if the constraint can be successfully parsed.

smalldatetime: 6/6/2079

datetime: 12/31/9999

all other numeric types: The maximum value defined by the data type or the column definition.

Locale

The locale that will be used to generate random characters. Use the drop-down arrow to see the list of available locales. This property is based on the locale of the collation of the database column, not based on the operating system or the Visual Studio locale.

String

Default

Minimum Length

The minimum length of the data that is generated for the column.

String, Binary

1

Maximum Length

The maximum length of the data that is generated for the column. You must keep this value reasonable to avoid OutOfMemory exceptions.

String, Binary, RegularExpression

The maximum length defined by the data type or the column definition.

4000

8000

Expression

The regular expression that you want to use to generate data. The data generated matches the specified pattern. For more information, see The Regular Expression Generator.

RegularExpression

[a-zA-Z0-9]*

Height

The height of the images that are generated for the column.

Image

Computed based on the size of the column to make sure that the image produced fits in the column.

64

Width

The width of the images that are generated for the column.

Image

Computed based on the size of the column to make sure that the image produced fits in the column.

64

Connection Information

A connection string to a data source. You can enter the connection string in the following ways:

  • Use the drop-down arrow to select an existing connection.

  • Use the drop-down arrow to select <Add new connection...>

This property is connected to Server Explorer in Visual Studio. Connection strings that are specified in Server Explorer appear in this property, and connection strings that are specified in this property appear in Server Explorer.

For more information, see The Data Bound Generator.

Data Bound Generator

No default value.

Select Query

The select query that is used to retrieve data from the data source. After you specify a select statement, the Generator Output column is updated with the names of the columns that the select query returned. All columns that the query returns must have a name or an alias to be valid for data generation. You must then select the column that you want in the Generator Output column. For more information, see the Generator Output column previously mentioned in this topic.

Aa833245.alert_caution(en-us,VS.90).gifCaution:
A malicious user can enter arbitrary Transact-SQL (T-SQL) in this property. For more information, see Security of Data Generators.

Data Bound Generator

No default value.

Next Steps

Now that you have specified the details of the data that you want to generate, you can preview and generate the data. For more information, see How to: Preview a Data Generation Plan and How to: Run a Data Generation Plan to Generate Data.

Security

For more information, see Security of Data Generators.

See Also

Tasks

Walkthrough: Creating and Running a Data Generation Plan

Concepts

Overview of Generating Data

Other Resources

Using Standard Data Generators

Creating Custom Data Generators

Data Generator Walkthroughs