Specify Details of Data Generation for a Column

To generate data in Visual Studio Team System Database Edition, you must create and run a data generation plan. A data generation plan contains information about which tables and columns you want to fill with data. After you create the plan, you can specify the details about the kinds of data with which you want to fill those columns. For more information, see Generate Test Data for Databases by Using Data Generators and How to: Create Data Generation Plans.

Parts of a Data Generation Plan

The following illustration shows the data generation plan window, which includes the column details pane and the Data Generation Preview window.

Data Generation Plan Window

Common Tasks

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.

Common Tasks	Supporting Content
Get hands-on practice: You can follow an introductory walkthrough to become familiar with how to create and run a simple data generation plan.	Walkthrough: Creating and Running a Data Generation Plan
View the column details: For each table that you include in your data generation plan, you can view the current details for the columns in that table. You can specify the columns that are included when you generate data.	View Column Details
Specify a data generator for each column: Each column must have an associated data generator. You can specify only those data generators that are supported for the SQL data type of the column.	Specify a Data Generator for a Column Standard Data Generator Types
Specify properties for the specified data generator: You can configure the details of data generation for each column by specifying properties. You can constrain the values by range or distribution, or you can provide details that are necessary for data generation. Such details might include a source connection string for the data bound generator.	Specify Properties for Data Generators The Regular Expression Generator The Data Bound Generator
Change the default data generators for SQL data types: Each SQL data type has an associated default data generator. For example, columns of type Int16 will use the SmallInt data generator by default. You can view and change the default generators for each SQL data type.	Default Generators for SQL Server Data Types How to: Change the Default Generator for a Column Type
Use the data bound generator to generate data from an Excel spreadsheet: You can configure the data bound generator to generate data from a table in an Excel spreadsheet. You can use this approach, for example, to populate a reference table.	How to: Generate Data from an Excel Spreadsheet

View Column Details

When you highlight a table in your data generation plan, the column details pane shows the columns in that table. For each column, the pane shows whether the column is included for data generation, the SQL data type of the column, and the data generator that is assigned to that column. You can select or clear the check box for the column to include or exclude that column when you generate data.

The following table describes the details for each column:

Column Name	Details
Column	The name of the column. This column contains a check box that you select to generate data for the column or clear to leave the column empty. You do not need to generate data for columns that are nullable or that have a default constraint. The Properties window displays the Allow Nulls and Default Value properties which you can use to decide which columns to fill with data. For more information, see How to: Specify Columns for Data Generation.
Key	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, see these topics on the Microsoft Web site: Data Types (SQL Server 2000), Data Types  (SQL Server 2005), and Data Types (Transact-SQL).
Generator	The type of data generator that will generate data for this column. Each SQL data type has a default data generator, which appears in this column when you first create a data generation plan. You can change the data generator by clicking the down arrow in the column and clicking a different data generator.
Generator Output	The output for this generator. This column is used for any data generator that produces more than one output value. For example, you must specify a SELECT query to retrieve data from the data source when you use the data bound generator. After you specify a SELECT query, this column contains a list of the data columns that the query returns. You specify the data with which you want to fill the column by clicking the down arrow and then clicking the appropriate option. For more information, see the Connection Information and Select Query sections later in this document. Custom data generators can also use this column. 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.

Specify a Data Generator for a Column

You can control what data is generated to fill columns by specifying the Generator and Generator Output for each column in the column details pane of the data generation plan window.

You should consider the following points about how to assign data generators:

• You cannot use all data generator types with all SQL data types. The list displays only the data generators that are 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).

Specify Properties for Data Generators

After you specify the type of data generator for a column, you can configure the properties of that data generator to refine the data that you generate. To configure the properties, click the column details pane of the data generation plan window and then open the Properties window. You can configure three categories of properties: Column Information, Database Constraints, and Generator. The Column Information and Database Constraints properties are read-only, and they give you information about each column that you can use to customize your data generation plan. The Generator properties are usually read-write, although some data generators might have read-only properties..

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 click 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 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 to which the foreign key refers.
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

You set the Generator properties to control how the data is generated. The properties that are available for each column depend on the data generator that is assigned to 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 accepts NULL values. 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 only NULL values are generated.	All data generators except SQL Computed Value.	0
Seed	The seed value that is used in the algorithms for generating random data. The standard data generators are deterministic. You will generate the same random data again if you use the same seed value. You can generate a different (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; otherwise, False.
Step	When the Unique property is true, each row is calculated by adding the Step value to the previous row. The value of the Step property 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. You can set this property to Uniform, Normal, Normal Inverse, Exponential, or 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 that is defined by the data type or the column definition.
Locale	The locale that will be used to generate random characters. You can click the down arrow to display the list of available locales. This property is based on the locale of the collation of the database column, not on the operating system locale 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 should keep this value reasonable to avoid OutOfMemory exceptions.	String, Binary, RegularExpression	The maximum length that is defined by the data type or the column definition. 4000 8000
Expression	The regular expression that you want to use to generate data. The generated data 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 produced image 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 produced image fits in the column. 64
Connection Information	A connection string to a data source. You can specify the connection string in the following ways: Click the down arrow, and then click an existing connection. Click the down arrow, and then click <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 a data source. After you specify a SELECT statement, the Generator Output column is updated with the names of the columns that the query returned. All columns that the query returns must have a name or an alias to be valid for data generation. You must then specify the column that you want in the Generator Output column. For more information, see the Generator Output column previously mentioned in this topic. Caution: A malicious user can add arbitrary Transact-SQL (T-SQL) code in this property. For more information, see Generate Test Data for Databases by Using 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.

Related Scenarios

• Generate Test Data for Databases by Using Data Generators
  Before you run database unit tests, you might want to insert test data into your database. You can use the data generator to create realistic test data without exposing production data to your developers.

• Generate Specialized Test Data with a Custom Data Generator
  You can create your own data generators if you must generate data that meets requirements that the built-in data generators do not support.

See Also

Tasks

Walkthrough: Creating and Running a Data Generation Plan

Concepts

Terminology Overview of Database Edition