dbForge Data Generator for SQL Server

DevArt Software has been busy at producing a great number of database tools and components for both Delphi and Visual Studio developers over the last few years. They’re still coming out with new ones as well. One of the most recent additions is dbForge Data Generator for SQL Server. I purchased version 1.0 in April, 2015 and they’ve been hard at work improving and adding features–they’re already at version 3.5! You can read about their features on their web site linked above, but I’d like to highlight a few I think are notable and have been very useful to me.

As you would expect from a data generator, they support all the basic column types. But they don’t stop there. Character strings, for example, could have several different purposes, from storing a person’s name to an address or to a unique ID. All these choices of generators are available through a drop-down list. But wait–there’s more! They even look at the field names and make good guesses about which type you want and pre-select them for you to make it easier. But wait–there’s more! What if you want only names usually used by males? Or females? They have those separated out or combined as you wish. But wait–there’s more! You can also choose from First Names, Middle Names (which include initials), just initials, Last Names, Nicknames, or even a Full Name. But wait–there’s more! There are also Honorific Prefixes (Mr. Dr., etc.) and Honorific Suffixes (Ph.D, III, etc.). But wait–there’s more! I also saw in the list DE First Names, DE Last Names, DE Honorific Prefixes. I believe these are Germanic versions of the samples you might use in the U.S. Examples of these are Wolfhard, Swena, and Christoph for First Names, Zurbriggen, Ittenbach, Schelling, and G√ľnther for Last Names, and Herr, Frau, and Doktor for Titles.

There are 10 different phone number generators (5 each for US and UK), social security numbers for both US and Canada, sample job titles, email addresses, courtesy titles, ethnic groups, and even sample twitter accounts! And that’s just the list of generators in the Personal category! Let’s say you wanted some random business names, or ISO codes, or stock ticker codes? They have those, too. Drug names, time zones, bank routing numbers, clothing sizes, colors, ISBNs and UPCs and VINs, shipping methods, product categories and names, and many more. For programmers, there are also random file paths, image names, document titles, IPv4 and IPv6 addresses.

Maybe you don’t need fake “real” data. Maybe all you need is some random text to fill the database. Of course there is Lorem Ipsum text, random strings of characters or numbers or both. One I used a lot was Weighted List. This allows you to enter list of allowed values and tell it to generate a certain percentage of each value in the generated rows. For example, one table I was working with held a list of “constituents” which could either be people or organizations. A field called KEY_INDICATOR of CHAR(1) could only be ‘I’ (for individual) or ‘O’ (for organization). My weighted list held these two values with it generating many more individuals than organizations.

Finally, if all else fails, you can always resort to regular expressions. And if one of the built-in generators is close but not quite what you need, select it and see if it’s one of the ones that are simply preset regular expressions. I needed to generate a random death date in a VARCHAR field but was unsure how to structure the regular expression to give a valid date. I first selected a date field (the generators are type-sensitive) and selected the Regular Expression generator. Then I copied that, switched back to my VARCHAR death field, and pasted it in–voila!

This tool is also smart enough to deal with foreign keys. When selecting a random value for a field that is a foreign key to another table, you have the option of selecting one of those values instead of random data (which could break a table relationship). It also generates “child” tables before “parent” tables. For example, if you are generating data for an Address table and a People table where the People table links to records in the Address table, it will automatically generate Address records first so they’ll be available to fill foreign key references in the People table.

Another nice feature is forcing randomly generated data to be unique. This is especially nice considering foreign keys. In my People-Address example, you would might want all the AddressID references in the People table to be unique–unless the table includes spouses as separate records that share the same address. But this is where things can start to get a little muddy. I’m not sure any random database generator could cover every situation, and I found I had to write some SQL to do some additional processing after the bulk of the data was generated.

For example, in the table I was working with that had a random death date, I wanted most of the people records to be alive, so I set the percentage of null values (another great feature for making the data seem real) rather high which would generate very few death dates. There’s another field in that table called DECEASED. The generator doesn’t (yet) have a way to set the value of a field based on another randomly generated value in the same record, so I defaulted the value for the DECEASED field to 0 (false) and wrote a short SQL statement to set DECEASED to -1 (true) where DEATH_DATE was not NULL.

There were a few other instances that I had to do this. One parent-child table relationship had a two-way linking with both tables holding the ID of the other. There’s no way that I could tell do that with this tool, especially since one of them was a VARCHAR holding an INT ID of the other and therefore needed to be CAST.

When I had my data generators all setup and saved to a .DGEN file, I was ready to run it. In the first several runs, I was running it directly against the database so the data was there immediately–along with errors at first. But it can also generate all the INSERT statements to an editor window or to a saved .SQL file. This allows you to not only add your own custom SQL statements before running it, but also to send the whole script to someone who doesn’t have dbForge Data Generator installed.

I have been very happy with this purchase. I really appreciate DevArt’s licensing structure (which is becoming quite common among software companies). The purchase price automatically gives you one year of updates after which you can renew that purchase subscription for a reduced rate. I am looking forward to even more enhancements in the months and years to come!

 Post details 

 Leave a comment 

Your email address will not be published. Required fields are marked *

*




 © 2017 - Cornelius Concepts