SQL Data Generator

I’ve been using Red Gate Software's SQL Data Generator 1.2 for a while now and have really grown to love it. I've used it to create sample data for development and testing and to quickly populate tables to validate performance requirements and address capacity planning needs. I’ve also used it to generate a whole suite of intricately related data (nearly 1GB in size) for a sample data warehouse. In all these cases, the tool performed admirably.

The first time I used SQL Data Generator, I needed to get it up and running quickly. Happily, installation was a breeze and within a few minutes it had generated my data. The intuitive UI uses the concept of "projects": With each project, you point SQL Data Generator at a target database, let it read the schema, then specify how many rows you want loaded into each table. You can also save projects to re-use your specifications and customizations for each data-load operation.

The tool really shines when you're specifying the kinds of data that you want to generate for each of your tables. It offers a wide assortment of "generators" that you can select from and assign to each column: simple generators for generating sequential or random INTs and UNIQUEIDENTIFIERs, and weighted-list generators, which let you manually create lists of data and weight the frequency at which each item should appear within the generated results. You can also use Foreign Key Generators to pull in data from a reference table, and SQL Statement Generators, which let you provide a connection and a custom query for generation purposes. A handful of business, personal, and other types of canned data generators make it very easy to generate commonly occurring types of data. And if these data generators don’t meet your needs, you can import data from .csv and other files or create custom generators.

One of my favorite generators is the RegExpGenerator, which lets you specify a regular expression that the tool basically turns in to "regex in reverse" for population purposes. Besides letting you combine data and contents from flat files, it also provides a very powerful way to generate sample and test data that represent a wide variety of cardinalities for performance testing.

Best of all, with each generator-to-column mapping, you can specify whether values should be unique or not as well as what percentage of values should be NULL (assuming NULLs are allowed) and even set the seed used for randomization. And, as you can see from Figure 1, all of this can be managed from within a very easy-to-use UI.

SQL Data Generator provides the option to bind your own scripts for execution before and after data generation. This can help in preparing an environment for rapid-loading, and I’ve found it very useful in ensuring that my parent-child relationships are temporally viable.

Given how easy SQL Data Generator is to use and extend, how well it performs, and how quickly it pays for itself by providing real value at a cheap price, I honestly don’t think that any serious developer or DBA should be without it. It's a great tool with lots of real-world potential. Michael K. Campbell ([email protected]) is a consultant with years of SQL Server DBA and development experience. He spends most of his time engaged in consulting, technical evangelism, and creating free online SQL Server videos.