1022 builds later, today, we (Red Gate) released SQL Data Generator which is available to download as a 14 day trail. SQL Data Generator intelligently creates meaningful test data by automatically taking the table and column names in to account, along with field length and any existing constraints. We feel it’s a great tool and is really useful during test and development, it’s just a great tool to have in order to quickly fill your database. From a DBA’s point of view, the tool is great for performance and scalability when huge amounts (millions of rows) is required to test against. To prove this, see below for how I used the application to generate data for the Northwind database.
We are really looking forward to hearing what you think in order to make the best possible product. If you have any comments or questions, please let us know via the forums.
On a side note, we have created a CodePlex site to host all any generators created by the community. We already have a few online for you to download and use so I strongly recommend you take a look. My favourite generator is the C# Code Generator created by Lionel, allowing you to write and compile C# code directly within the UI to use as the column generator.
Generating data for Northwind
Northwind is one of the sample Microsoft databases originally shipped with SQL Server 2000 and is used in a very large number of articles, as such it seemed logical to use it for this post. Below demonstrates how you can very quickly generate data for the Northwind database.
After loading the application, you will be presented with a Project Configuration dialog. This allows you to specify which server and database you want to produce data for. At this point you can configure project options, such as prepost scripts to execute and if triggers should be fired when generating data. I’m just going to connect to my local server and the Northwind database.
At this point, the application will load the schema and automatically attempt to match our built-in generators to columns within your table. The screenshot below has three main areas, the list on the left specifies which tables will be populated. The top middle panel provides all of the generation settings for the table or column (based on what is selected). In this example, I have the Categories table selected and I wish to generate 1,000 rows. In the lower middle panel, you have a preview table of how the data might look. The CategoryName column has been populated with some possible categories, while Description has some latin text with the Picture column being populated with a valid image. This is straight out of the box, I haven’t done anything yet apart from connect to the database.
Selecting a column in the preview table displays the column generation settings. Each generator has its own set of properties allowing you to adapt the data to your own requirements, you can also select different generators by using the dropdown at the top. I’m happy with the pre-defined data so I will leave it as it is.
Clicking the Generate Data… button on the toolbar will display the Data Population Summary, this just simply says what is going to be done against the database. In this case, DELETE FROM and TRUNCATE TABLE commands will be executed with 1000 rows being inserted for each table.
Clicking Generate Data will populate the table. After which a report will be displayed detailing what happened and if any errors occurred. The data looks to have been inserted successfully, It’s amazing how quickly data can actually be generated using this application.
That’s it – very direct and simple. We can select data from the table using SQL Management Studio. This data is all the default settings, I haven’t modified anything.
If your happy with the data produced then you can save generation settings as a project file to use later, or you can tweak the settings to tailor the data generated for your own database.
However, the application has really loads of cool features which I haven’t even touched on here, hopefully I will be able to cover them in later posts and articles. Until then, why don’t you download the free 14 day trail from the website and see what features you really like. The team and I would love to hear your feedback!
More information at http://www.red-gate.com/products/SQL_Data_Generator/index.htm
Product forums at http://www.red-gate.com/sqldatagenerator/forum/v1
CodePlex website at http://www.codeplex.com/SDGGenerators