Data Warehousing and Data Science

27 February 2011

Creating Test Data on SQL Server 2008

Filed under: SQL Server — Vincent Rainardi @ 11:31 pm

The key thing here is that you need Visual Studio 2010 to do this. You can not generate test data using SQL Server 2008. You need Visual Studio 2010 Premium or Ultimate. The normal Visual Studio (Professional edition) can not do it. Alternatively you can do it using Visual Studio 2008 Database edition. In VS 2010 there is nothing called Database edition, it only exists in VS 2008.

Unfortunately VS 2010 Premium is £3,695, very different to the normal VS 2010 (Professional edition) which is £694).

As per the how to do it, Arshad Ali wrote a very good post here. And the formal MSDN guide is here. One of the most important things to notice is that the test data is rubbish, e.g. contains a series of random, unreadable characters. For numeric columns it’s OK, they contains random numbers, which is what we want. But again the numbers are rubbish. Most users are used to meaningful data, so for them to view reports containing the test data, their usual reactions is that they can’t stand looking at the reports. In their view the reports are rubbish because they contain rubbish data. You won’t understand it unless you see it for youself, so I’d recommend doing it (showing the user a report with dummy data). I’m of the opinion that reports with dummy data are more appropriate for developers and testers within IT, not the end users.

If rubbish data is not what you have in mind, do not set 3 days in your project plan to generate test data. You will need to do it the old way, using stored procedures. Mitchel Sellers wrote a very good article on this topic: link.

Alternatively, you can use Red Gate data generator tool (link), which is £175/user. Cheaper than buying the VS 2010 Premium which is £3,695. But, again, as per any test data generator tools, the data is rubbish. It’s just a series of random, unreadable characters.

1 Comment »

  1. The data generated by VS2010 doesn’t have to be rubbish, you have multiple options to constrain or modify the data generation. You could for example pick names from an existing customer database, constrain integer or date ranges by a simple min/max or write complex regular expressions when needed.

    Check out the following link for the full details:

    Comment by Daniel Wikar — 28 February 2011 @ 8:25 am | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: