Create SQL Test Data Using EMS Data Generator for SQL Server

In this short SQL product review for EMS Data Generator for SQL Server, I'll try to share my experience with this sql test data generator tool SQL Server developers and testers would like to use.

I'm developing an ASP.NET application where data is stored in a Microsoft SQL Server 2008 database.
I generally install SQL Server instances on my development computers in order to develop code any where I'm at home, at office or while I'm mobile. This enables me code free from development SQL Servers since sql data is with me.
So first I tried to connect to MS SQL Server 2008 instance local on my computer.

First screen of the sql test data generator tool is for configuring sql server connection properties.
In this first screen of tool SQL Server connection properties can be configured by identifying the SQL Server as local or remote, selecting the authentication type and entering the sql login name and password.

One note on this screen, it does not support local named instances for SQL Server. But there is a work-around.
When you select "local" server, you do not have an input area where you can enter the name of the SQL Server instance you want to connect to.
The host name dropdown list is activated only when you choose the remote SQL Server connection type. In this textbox you can enter named instance SQL Server on remote servers.

So the work-around for connectiong to a local named instance is connecting the local server by selecting remote server option and entering the name of the server with the named instance.
For example instead of connecting to local SQL Server named instance ".\SQLExpress" select "remote server" option and enter the SQL Server name in the format "Kodyaz\SQLExpress".

Though EMS Data Generator seems to connect to my local SQLServer 2008 instance successfully, it was unable to list the databases loacated on my SQL2008 database instance.
So I used the above work-around for the local server which I will create test data using this sql data generator tool.

One small detail with EMS Data Generator for this test data tool is the table list is not refreshed in case of an unexpected case.
For example I successfully connected to a SQL Server, then select a database from the available sql databases created on the SQL instance.
The "Available Tables" list is populated with tables created on this sql database.
The problem occurs when I return back to SQL Server connection screen and select local SQL Server instance for generating test data.
In this case, the databases are not listed but the previous list of available tables are still the same.
I expect the table list to be cleared. I assume that is a small bug for a sql data generator tool.

What I liked in the EMS Data Generator for SQL Server is that it can create random data using different chractersets.
For example, while creating test data for Firstname field in AdventureWorks database dbo.Authors table, a developer can select latin5 (ISO 8859-9 Turkish) chracter set for example.
SQL developers or sql testers can also limit used characters with in the charset using the Start Char and End Char selections.
Here is a sample configuration.

But since the Turkish characters are not listed in order after the A..Z, I had to let many unwanted characters while identifying the end character.
So practically using a test data with only letters used in Turkish alphabet can not be managed simply using the CharSet combobox.
I tried to create mask for string values. But I think the mask utility is not advanced for complex data creation.
For example I could not create a string of lower and upper cases mixed with additional Turkish characters.
In the help documentation there is some mask examples for web addresses, mask for email addresses, for vehicle numbers etc.

Note that XML data type is not supported in this tool for SQL Server. Developers and testers should find some other ways to build XML test data for their test cases.

Generation of random datetime test data for SQL Server datetime and date columns is very easy using this sql tool for data generation.

You can also configure only date test data generation excluding the time part by clearing the "include time" checkbox.

If you take a look at the below screenshot you will see the basic functions and capabilities of the EMS Data Generator for SQL Server.

You can set the percentage of NULL data for a column values. For nullable columns, the data generator lets the users to set the percentage of null data to be created.

4 different data generation modes enables developers and testers to generate customized data.
SQL random data for SQL Server can be created by both using constraints that users can set parameter values and by using mask that is developed by the users
SQL testers can create incremental data for alpha-numeric or string data types as well as numeric data type columns.
For numeric incremental data type creation, you can set an initial value to begin from and increment steps.
For string values, within a character set and between start and end characters test data will be created again considering incremantal step value.
Test data generation can be customized by using sql queries and using lists.
You can connect to an other database using an SQL query and gather your sample data from that select result set.
Also developers can define lists which contain test data. And also for string values you can define a sample text where test data samples will be taken from.
There is also another option which enables developers to get test data among another table column values. For using this the last data generation mode "Get data from field" can be used.

EMS Data Generator for SQL Server can directly execute the data generation process over the target database and tables.
If you want only the data generation script can be created to a file. Or both can be done according to your selection on the action wizard screen.
If you select to save the sql test data generate script in a file, the sql query editor within the tool lets you edit the t-sql script and make more customization.