Walkthrough: Creating and Running a Data Generation Plan

Article
02/04/2013

In this walkthrough, you create a data generation plan and run it to fill a test database with randomly generated data. First you create a database project and deploy it to an isolated test database. You can use the isolated database to test data generation without touching your production data or database.

Prerequisites

To complete this walkthrough, you must have the following products installed:

Visual Studio Premium
SQL Server 2008 or SQL Server 2005

Creating the Database Project

First you create a database project and import the schema from a script.

Note

In a team environment, you might check an existing project out of version control to work on a database project. For more information, see Starting Team Database Development.

To create the database script

On the File menu, point to New, and then click File.

The New File dialog box opens.
In the Categories list, click General if it is not already highlighted.
In the Templates list, click Sql File, and then click Open.

The Transact-SQL editor opens.

Copy the following Transact-SQL code and paste it into the Transact-SQL editor.

PRINT N'Creating dbo.Customer...';
GO
CREATE TABLE [dbo].[Customer] (
    [CustomerId] UNIQUEIDENTIFIER NOT NULL,
    [UserId]     UNIQUEIDENTIFIER NOT NULL,
    [UserName]   VARCHAR (256)    NOT NULL
);
GO
PRINT N'Creating dbo.Menu...';
GO
CREATE TABLE [dbo].[Menu] (
    [MenuId]       UNIQUEIDENTIFIER NOT NULL,
    [RestaurantId] UNIQUEIDENTIFIER NOT NULL,
    [StartDate]    DATETIME         NOT NULL,
    [EndDate]      DATETIME         NOT NULL,
    [MenuType]     VARCHAR (50)     NULL
);
GO
PRINT N'Creating dbo.MenuItem...';
GO
CREATE TABLE [dbo].[MenuItem] (
    [MenuItemId]      UNIQUEIDENTIFIER NOT NULL,
    [MenuId]          UNIQUEIDENTIFIER NOT NULL,
    [Name]            VARCHAR (128)    NULL,
    [Description]     VARCHAR (512)    NULL,
    [ImageLocation]   VARCHAR (MAX)    NULL,
    [Price]           MONEY            NULL,
    [PreparationTime] INT              NULL
);
GO
PRINT N'Creating dbo.Order...';
GO
CREATE TABLE [dbo].[Order] (
    [OrderId]          UNIQUEIDENTIFIER NOT NULL,
    [SubmittedDate]    SMALLDATETIME    NOT NULL,
    [CustomerID]       UNIQUEIDENTIFIER NOT NULL,
    [Total]            MONEY            NOT NULL,
    [ContactTelephone] CHAR (20)        NULL,
    [PostalCode]       CHAR (10)        NULL,
    [State]            CHAR (2)         NULL,
    [StreetAddress]    VARCHAR (75)     NULL,
    [City]             VARCHAR (25)     NULL
);
GO
PRINT N'Creating dbo.OrderDetail...';
GO
CREATE TABLE [dbo].[OrderDetail] (
    [OrderDetailId]     UNIQUEIDENTIFIER NOT NULL,
    [OrderId]           UNIQUEIDENTIFIER NOT NULL,
    [RestaurantId]      UNIQUEIDENTIFIER NOT NULL,
    [MenuItemId]        UNIQUEIDENTIFIER NOT NULL,
    [DeliveryId]        UNIQUEIDENTIFIER NOT NULL,
    [Quantity]          INT              NOT NULL,
    [UnitCost]          MONEY            NOT NULL,
    [Status]            NCHAR (20)       NOT NULL,
    [StatusUpdatedTime] SMALLDATETIME    NOT NULL,
    [WorkflowId]        UNIQUEIDENTIFIER NOT NULL,
    [ETA]               SMALLDATETIME    NULL
);
GO
PRINT N'Creating dbo.OrderPayment...';
GO
CREATE TABLE [dbo].[OrderPayment] (
    [PaymentID]        UNIQUEIDENTIFIER NOT NULL,
    [OrderID]          UNIQUEIDENTIFIER NOT NULL,
    [CreditCardNumber] CHAR (4)         NULL,
    [NameOnCard]       VARCHAR (75)     NULL,
    [Address]          VARCHAR (50)     NULL,
    [Country]          VARCHAR (50)     NULL,
    [City]             VARCHAR (50)     NULL,
    [State]            VARCHAR (50)     NULL,
    [PostalCode]       CHAR (10)        NULL,
    [ExpirationDate]   SMALLDATETIME    NULL,
    [CreditCardType]   VARCHAR (50)     NULL
);
GO
PRINT N'Creating dbo.Restaurant...';
GO
CREATE TABLE [dbo].[Restaurant] (
    [RestaurantId]           UNIQUEIDENTIFIER NOT NULL,
    [Name]                   VARCHAR (256)    NULL,
    [Description]            VARCHAR (1024)   NULL,
    [RestaurantCategoryId]   UNIQUEIDENTIFIER NOT NULL,
    [LogoImageLocation]      NVARCHAR (MAX)   NULL,
    [SmallLogoImageLocation] NVARCHAR (MAX)   NULL,
    [BannerImageLocation]    NVARCHAR (MAX)   NULL,
    [MainImageLocation]      NVARCHAR (MAX)   NULL,
    [BackgroundLocation]     NVARCHAR (MAX)   NULL,
    [PostalCode]             VARCHAR (128)    NOT NULL,
    [StreetAddress]          VARCHAR (256)    NULL,
    [City]                   VARCHAR (512)    NULL,
    [State]                  VARCHAR (256)    NULL
);
GO
PRINT N'Creating dbo.RestaurantCategory...';
GO
CREATE TABLE [dbo].[RestaurantCategory] (
    [RestaurantCategoryId] UNIQUEIDENTIFIER NOT NULL,
    [Description]          VARCHAR (255)    NOT NULL
);
GO
PRINT N'Creating dbo.Default_Menu_StartDate...';
GO
ALTER TABLE [dbo].[Menu]
    ADD CONSTRAINT [Default_Menu_StartDate] DEFAULT GETDATE() FOR [StartDate];
GO
PRINT N'Creating dbo.PK_Customer...';
GO
ALTER TABLE [dbo].[Customer]
    ADD CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([CustomerId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating dbo.PK_Menu...';
GO
ALTER TABLE [dbo].[Menu]
    ADD CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED ([MenuId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating dbo.PK_MenuItem...';
GO
ALTER TABLE [dbo].[MenuItem]
    ADD CONSTRAINT [PK_MenuItem] PRIMARY KEY CLUSTERED ([MenuItemId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating dbo.PK_Order2...';
GO
ALTER TABLE [dbo].[Order]
   ADD CONSTRAINT [PK_Order2] PRIMARY KEY CLUSTERED ([OrderId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating dbo.PK_OrderDetail...';
GO
ALTER TABLE [dbo].[OrderDetail]
    ADD CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED ([OrderDetailId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating dbo.PK_OrderPayment...';
GO
ALTER TABLE [dbo].[OrderPayment]
    ADD CONSTRAINT [PK_OrderPayment] PRIMARY KEY CLUSTERED ([PaymentID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating dbo.PK_Restaurant...';
GO
ALTER TABLE [dbo].[Restaurant]
    ADD CONSTRAINT [PK_Restaurant] PRIMARY KEY CLUSTERED ([RestaurantId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating dbo.PK_RestaurantCategory...';
GO
ALTER TABLE [dbo].[RestaurantCategory]
    ADD CONSTRAINT [PK_RestaurantCategory] PRIMARY KEY CLUSTERED ([RestaurantCategoryId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating dbo.FK_Menu_Restaurant...';
GO
ALTER TABLE [dbo].[Menu]
    ADD CONSTRAINT [FK_Menu_Restaurant] FOREIGN KEY ([RestaurantId]) REFERENCES [dbo].[Restaurant] ([RestaurantId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
PRINT N'Creating dbo.FK_MenuItem_Menu...';
GO
ALTER TABLE [dbo].[MenuItem]
    ADD CONSTRAINT [FK_MenuItem_Menu] FOREIGN KEY ([MenuId]) REFERENCES [dbo].[Menu] ([MenuId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
PRINT N'Creating dbo.FK_Order2_Customer...';
GO
ALTER TABLE [dbo].[Order]
    ADD CONSTRAINT [FK_Order2_Customer] FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[Customer] ([CustomerId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
PRINT N'Creating dbo.FK_OrderDetail_MenuItem...';
GO
ALTER TABLE [dbo].[OrderDetail]
    ADD CONSTRAINT [FK_OrderDetail_MenuItem] FOREIGN KEY ([MenuItemId]) REFERENCES [dbo].[MenuItem] ([MenuItemId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
PRINT N'Creating dbo.FK_OrderDetail_Order2...';
GO
ALTER TABLE [dbo].[OrderDetail]
    ADD CONSTRAINT [FK_OrderDetail_Order2] FOREIGN KEY ([OrderId]) REFERENCES [dbo].[Order] ([OrderId]) ON DELETE CASCADE ON UPDATE CASCADE;
GO
PRINT N'Creating dbo.FK_OrderDetail_Restaurant...';
GO
ALTER TABLE [dbo].[OrderDetail]
    ADD CONSTRAINT [FK_OrderDetail_Restaurant] FOREIGN KEY ([RestaurantId]) REFERENCES [dbo].[Restaurant] ([RestaurantId]) ON DELETE NO ACTION ON UPDATE CASCADE;
GO
PRINT N'Creating dbo.FK_OrderPayment_Order...';
GO
ALTER TABLE [dbo].[OrderPayment]
    ADD CONSTRAINT [FK_OrderPayment_Order] FOREIGN KEY ([OrderID]) REFERENCES [dbo].[Order] ([OrderId]) ON DELETE CASCADE ON UPDATE CASCADE;
GO
PRINT N'Creating dbo.FK_Restaurant_RestaurantCategory...';
GO
ALTER TABLE [dbo].[Restaurant]
    ADD CONSTRAINT [FK_Restaurant_RestaurantCategory] FOREIGN KEY ([RestaurantCategoryId]) REFERENCES [dbo].[RestaurantCategory] ([RestaurantCategoryId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
PRINT N'Creating dbo.CK_Menu_EndDate...';
GO
ALTER TABLE [dbo].[Menu]
    ADD CONSTRAINT [CK_Menu_EndDate] CHECK (([EndDate] > '01/01/2000') AND ([EndDate] >= [StartDate]));
GO
PRINT N'Creating dbo.CK_Menu_StartDate...';
GO
ALTER TABLE [dbo].[Menu]
    ADD CONSTRAINT [CK_Menu_StartDate] CHECK ([StartDate] > '01/01/2000');
GO
PRINT N'Creating AutoCreatedLocal...';
GO
CREATE ROUTE [AutoCreatedLocal]
    AUTHORIZATION [dbo]
    WITH ADDRESS = N'LOCAL';
GO
PRINT N'Creating dbo.Menu.EndDate.ExtProp_Menu_EndDate_Description...';
GO
EXECUTE sp_addextendedproperty @name = N'ExtProp_Menu_EndDate_Description', @value = 'Date the menu expired. Must be > 01/01/2000 and must be after the StartDate', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Menu', @level2type = N'COLUMN', @level2name = N'EndDate';
GO

On the File menu, click Save SqlQuery_1.sql As.

The Save File As dialog box opens.
In Object name, type SampleImportScript.sql.

You can save the file to any location on your computer. Make note of the location so that you use it in the next procedure.
Click Save.
On the File menu, click Close Solution.

Next you create a database project and import the schema from the script that you have created.

To create a database project

On the File menu, point to New, and then click Project.

The New Project dialog box appears.
Under Installed Templates, expand the Database node, and then click SQL Server.

Note

If you are using Visual Studio Professional, look under Installed Templates, expand the Database node, expand the SQL Server node, and then click Advanced.
In the list of templates, click SQL Server 2008 Database Project.

Note

If your want to deploy to a server other than SQL Server 2008, click the template that corresponds to your target deployment environment.
In Name, type WalkthroughDataGenerator, and click OK.

A solution is created that contains the WalkthroughDataGenerator empty project. This is your database project. No one else has access to your database project when you work on it.
In Solution Explorer, click WalkthroughDataGenerator.
On the Project menu, click Import Script.
In the Import SQL Script File dialog box, click Next.
In Filename, type the path and file name of the script that you created earlier in this walkthrough.

As an alternative, you can also click Browse to find the script file.
Click Finish.

The script that contains the definition of the structure of your database is imported.
When the schema is imported, click Finish.

The database schema was imported into your database project. Project items that correspond to the objects in the database appear under the database project in Solution Explorer and Schema View. Next you configure, build, and deploy the project to your local development environment.

Deploying to an Isolated Development Environment

Next you deploy the project to a new database. This procedure creates a database that has the imported schema but contains no data. This database is an isolated development environment, or sandbox, in which you can develop and test the database.

To build the database project

In Solution Explorer, click the database project WalkthroughDataGenerator.
On the Project menu click WalkthroughDataGenerator Properties.

The project properties appear.
Click the Deploy tab.
In the Deploy Action list, click Create a deployment script (.sql) and deploy to database.
In Target Database Settings, click Edit to display the Connection Properties dialog box.
Set the connection properties for the database where you want to work, and then click OK.

The Target Connection box is filled with the correct connection string.

Warning

You should create the new database on a test server, a development server, or on your local computer. You should not use your production server.
In the Target database name text box, type DinnerNowDataGenerator.
On the File menu, click Save All.
On the Build menu, click Build Solution.

When you build the project, you verify that the .dbschema file can be created without error. You see the status of the build in the Output window, and you should see Build: 1 succeeded or up-to-date as the last line.

To deploy the database project

In Solution Explorer, click the database project WalkthroughDataGenerator.
On the Build menu, click Deploy WalkthroughDataGenerator. You can also right-click the project in Solution Explorer and click Deploy.

Warning

You should run this deployment against a test server, a development server, or your local computer. You should not use your production server.

The database project is deployed to a new database. You see the status of the deployment in the Output window, and you should see Deploy: 1 succeeded as the last line.

Note

If the Output window does not appear, open the View menu and click Output.

Creating the Data Generation Plan

Next you create the data generation plan. The data generation plan contains the information about which tables and columns you want to fill with data. For more information, see How to: Create Data Generation Plans.

To create the data generation plan

In Solution Explorer, right-click the Data Generation Plans node, point to Add, and click Data Generation Plan.

The Add New Item dialog box appears.
In the Name text box, type PartialDGenPlan.dgen.
Click Add.

The data generation plan is created. The data generation plan and the Data Generation Preview window appear. The data generation plan window is divided horizontally into two panes. The upper pane lists the tables that are defined in the database project schema. The lower pane displays column details for the table that is highlighted in the upper pane.

Note

If the Data Generation Preview window is not open, you can open it by opening the Data menu, pointing to Data Generator, and then clicking Preview Data Generation. By default, the Data Generation Preview window is docked and tabbed at the bottom of the data generation plan window. To expand your view, click the window, and then click Dock as Tabbed Document on the Window menu. You can also right-click the title bar and then click Dock as Tabbed Document.
In the PartialDGenPlan.dgen designer, clear the check boxes for all the tables.

Note

You can select or clear the check boxes for all the tables by using the Include All Tables In Data Generation and Exclude All Tables From Data Generation commands. You can access these commands by right-clicking any row in the data generation plan window or, on the Data menu, pointing to Data Generator.
In the PartialDGenPlan.dgen designer, select the check box for the dbo.Restaurant table.

The check box for the dbo.RestaurantCategory table is automatically selected also. Because the Restaurant table has a foreign key to the RestaurantCategory table, you must fill the other table to fill the Restaurant table. For more information, see How to: Specify Tables for Data Generation.
On the File menu, click Save All.

Specifying Details for Data Generation

Next you specify the details of how you want to fill columns with data. For more information, see Specify Details of Data Generation for a Column.

To specify details for data generation

Set the number of rows of data to generate by following these steps:
1. In the PartialDGenPlan.dgen designer, click the row for the Restaurant table.
2. Click the Related Table column, and use the drop-down arrow to specify the RestaurantCategories table.
3. Click the Ratio to Related Table column and type 10:1.
Based on these settings, you will generate 10 restaurants for each category that is generated. For more information, see How to: Specify the Number of Rows to Generate.
Set the number of NULL rows of data to generate by following these steps:
1. In the PartialDGenPlan.dgen designer, click the row for the Restaurant table.
2. In the column-details pane, click the row for the Description column.
3. In the Properties window, set the Percentage Null property to 10.
Based on these settings, 10% of the data that is generated in the Description column will contain NULL. You can open the Data Generation Preview window and verify that the Description column contains some null values.
Set the text of the data that is generated by following these steps:
1. In the PartialDGenPlan.dgen designer, click the row for the Restaurant table.
2. In the column-details pane, click the row for the Name column.
3. Click the Generator column, and use the drop-down arrow to specify the RegularExpression data generator.
4. In the Properties window, set the Expression property to the following:
```
(Delicious|Golden|Family|Sweet|Dancing|Magic|Thai) (Ginger|Duck|Flower|Potato|Pumpkin|Kitchen|Grill|Onion|Corral)
```
The data that is generated in the Name column will contain two-word names. You can open the Data Generation Preview window and verify that the Name column contains randomly generated restaurant names. For more information, see The Regular Expression Generator.
On the File menu, click Save All.

Running the Plan to Generate Data

Finally you run the data generation plan. After you generate the data, you can use a different tool to log on to the database and verify the new data.

To run the plan to generate data

In Solution Explorer, double-click PartialDGenPlan.dgen.

Note

The data generation plan must be open. If the plan is not open, you cannot generate data.
On the Data menu, point to Data Generator and then click Generate Data.

The Connect to Database dialog box appears.
In the Data Generation Connection Information list, specify a connection to the database that you deployed earlier in this walkthrough, and then click OK.
Click Yes when you are prompted to clear the contents of the tables before inserting new rows.

The data is generated. In the Population window, the status column is updated with the status of the data generation. The status bar summarizes the data generation for all the tables.
(Optional) Use a different tool to log on to the database. You can use the Transact-SQL editor that is provided in Visual Studio Premium for this step. For more information, see Editing Database Scripts and Objects with the Transact-SQL Editor on the Microsoft web site. View the new data by running the following query:
```
use DinnerNowDataGenerator

select * from [dbo].[RestaurantCategory]
select * from [dbo].[Restaurant]
```
Verify that 10 times as many rows were generated for the Restaurant table as for the RestaurantCategory table. Verify that the Description column in the Restaurant table contains NULL values. Verify that the Name column in the Restaurant table contains data that matches the regular expression that you specified.

Next Steps

You would typically configure data generation for the other columns and tables in your database. After you finish that configuration, you would make your project available to your team by checking it, including the data generation plan, in to version control. For more information, see Add Files to Version Control.