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:
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. Note 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:
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. Warning 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
Other Resources
Using Standard Generators
Creating Custom Generators
Data Generator Walkthroughs