These scripts create the objects listed above. The database in this example is called Dummy_Data. No TSQL is included here as nearly everyone’s hardware setup will be different. In addition, there is a function and a sequence needed. There are 5 tables created with the names and rowcounts shown below. The attached scripts will generate the following database items: Select First_Name.Name + ' ' + Last_Name.Name SurnameĪttila the Hun Attila the Hun Creating the data SQL Server TSQL provides the CROSS JOIN which operates as a Cartesian join, unless you include a WHERE clause which links the two tables e.g. Imagine I have two tables with data as shown below: How Cartesian joins workĪ Cartesian join multiplies every row in a dataset with every row in the joined dataset. In this case, the Reporting Data Layer and Base Data Layers may be in a separate database to the application database. When the system goes live, point the synonyms to the application database. If data cannot be created in the application database, create tables in the Reporting Data Layer and then create synonyms to them, populate the tables and use the synonyms for reporting. There may be a number of steps involved in getting data from the base tables into a format that can be imported into your application database. This layer contains derived tables and views which create data similar to what would be seen in the application that you are testing. The base data layer contains the dummy data tables, any data generation scripts, and a sequence or sequences for generating Ids. These layers can be within different schemas within the database. The solution is broken into a number of layers. Child – Parent relationships are created by including the parent id in a Cartesian product with the child data (See below).ĭiagram 1 - Populating the Application databaseĭiagram 2 - Use of Synonyms where changes to the application database are not allowed.

Id values are generated using a sequence. Using the generated dataset this gives 8,000,000 (10 x 10 x 1000 x 4 x 20 x 1) rows of data. datetime, character and numeric.ĭata is generated by creating Cartesian joins on the tables. If you wanted data that was only based on data type and had no other meaning you could classify the tables by data type e.g. Some tables are reused to generate street names and post codes. The scripts below create a number of tables for storing different data descriptors, e.g. The scripts and methodology allow sample data to be generated. The purpose of this article is to provide a means of generating random data which can be inserted into a database for testing purposes. This caused a lot of rework after GO-Live which in turn affected the perception of quality of the project as a whole.

We didn’t have enough data to test response times or to tune SQL until after GO-Live. The database was a third party database and the vendor wouldn’t allow data to be entered via the back end. I have worked on a number of projects where there hasn’t been enough data to test reports and extracts and other database applications.

Ms sql data generator