SQL SERVER – Generating Meaningful Test Data with dbForge Data Generator for SQL Server

Recently I faced with necessity of generating meaningful SQL data for testing purposes. During my search for a proper application for the task, I found out that Devart, a recognized developer of database solutions, released dbForge Data Generator for SQL Server, a GUI tool for fast generation of meaningful SQL data for development and testing purposes. Devart stated that with the tool, I will be able to:

  • Populate SQL Server tables with millions of rows of test data
  • Generate meaningful data that looks just like real data
  • Use 100+ predefined generators with sensible configuration options
  • Customize built-in generators
  • Create user-defined generators
  • Preview generation results
  • Save data population script for future reference
  • Automate data generation with the command-line interface

It sounded great, so I decided to give a quick test for the tool. Moreover, Devart offers a 30-day free trial for the most of its products, including Data Generator for SQL Server.

The first thing I noticed after running SQL Data Generator was clear user-friendly interface. The start page of the tool includes quick access to the main features and allows to quickly run recent projects, if any.

SQL SERVER - Generating Meaningful Test Data with dbForge Data Generator for SQL Server dbforge-1

After running new data generation, I was prompted to select connection and database as well as to set default data generation options. I decided to generate meaningful data for few columns of the AdventrureWokrsTest database.

SQL SERVER - Generating Meaningful Test Data with dbForge Data Generator for SQL Server dbforge-2

On the Options page, I selected to generate 2000 rows, and cleared the Include Null values option.

Solarwinds

SQL SERVER - Generating Meaningful Test Data with dbForge Data Generator for SQL Server dbforge-3

After that, Data Generator showed the generation document. I selected the following columns of the Person.Contact table for data population:

  • ContactID
  • Title
  • First Name
  • LatName
  • EmailAddress

Now, the time has come for selecting generators for each column. In dbForge Data Generator for SQL Server, all generators are split into several categories for easier look-up.

SQL SERVER - Generating Meaningful Test Data with dbForge Data Generator for SQL Server dbforge-4

I was really impressed with the collection of generators the application offers. The tool automatically detects and offers appropriate generators for a given column.  Also, Data Generator for SQL Server includes multiple ways to make generated data more customized. For instance, you can:

  • Set the range of generated values for different SQL data types
  • Define the percentage of NULLs to be generated
  • Set the value distribution mode
  • Immediately preview data that will be generated

Data Generator suggested the following generators:

  • IDs for the ContactID Additionally, I selected the Random by timestamp value distribution mode for getting more complex numbers.
  • Honirific Prefix (Title) for the Title column
  • First Name (male) for the FirstName column
  • Last Name for the LastName column
  • Email for the EmailAddress This generator is based on the following regular expression \c{3,8}(\d{0,3}|.\c{3,10})@nowhere.com that can be customized right in the Fill settings section of Column generation settings. For instance, you can modify the regular expression, so that the domain name would be “mailservice.com” instead of “nowhere.com”.

SQL SERVER - Generating Meaningful Test Data with dbForge Data Generator for SQL Server dbforge-5

Data Generator also allows to modify existing generators and create your own ones, so in future you won’t need to modify the same generator over and over again. I opened the Email.xml file, that is located at C:\Users\Public\Documents\Devart\dbForge Data Generator for SQL Server\Data Generators,  modified regular expression so that the domain name would consist of combination of letters, updated description, name, and saved the file as MyEmail.xml:

SQL SERVER - Generating Meaningful Test Data with dbForge Data Generator for SQL Server dbforge-6

The following screenshot shows the data generation document with the result of my manipulations:

I was quite satisfied with the results showed in Preview, so I was ready to populate the target database with data. Data Generator offers several ways to do that, namely:

  • Open the data population script in the internal editor
  • Save the SQL script to a file
  • Run the script automatically

SQL SERVER - Generating Meaningful Test Data with dbForge Data Generator for SQL Server dbforge-7

I chose the first option and in few seconds, I got the following population script:

SQL SERVER - Generating Meaningful Test Data with dbForge Data Generator for SQL Server dbforge-8

To sum up, I would like to say that the tool has done a fantastic job for me. In my opinion, the most strong points of Devart’s Data Generator are:

  1. Simple work-flow in a convenient interface. With dbForge Data Generator, the process takes just couple of minutes and basically, it is simplified to three simple steps:
  • Selecting required tables
  • Selecting and customizing a required generator
  • Populating tables with test data
  1. Impressive collection of generators. The tool includes over 100 generators that allow to populate tables with realistic-looking test data.
  2. Flexible Customization Options. Developers put really much effort for making data more customized and suiting specific needs.

I strongly recommend to give dbForge Data Generator for SQL Server a try and to evaluate its functionality on your own by downloading a 30-day free trial.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal
Next Post
SQL SERVER – What are T-SQL Median? – Notes from the Field #090

Related Posts

2 Comments. Leave new

  • Hi Pinal,
    As you said that following query gives different time output
    SELECT
    CAST(‘2015-01-01 12:45:29.755’ AS SMALLDATETIME),
    CAST(‘2015-01-01 12:45:35.755’ AS SMALLDATETIME)

    Because the SMALLDATETIME datatype returns the date with time with 24 hours pattern ;but this time always consider seconds as :00 and no any fractional seconds considered.
    Above query returns following outputs
    2015-01-01 12:45:00
    2015-01-01 12:46:00 respectively
    The second date’s time is rounded to 46 min ,and hence it gives difference between these two outputs

    Thanks!

    Reply
  • Hi Pinal,
    I didn’t get 20 rows inserted when i tried to generate 20 rows with the same id. Even if i had disabled the check constraint.

    I’d just want to generate many data with the same id.
    For example:
    I have a table called person, this table has two collumns id_person and name.
    I want to put twenty distinct names to the same person:

    number_line | id_person | name

    1 1 Jhon
    2 1 Carl
    . . .
    . . .
    . . .
    20 1 Nick

    How can i do this?? The dbForge Data Generator isn’t helping.

    Reply

Leave a Reply

Menu