Tag: software development

This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today.

One of the interesting things with Redgate’s SQL Data Generator (SDG) is that it allows the user of custom data patterns for your columns. The user of regular expressions (RegEx) is used, which is something I find many SQL Server DBAs don’t really work with often.

While there are plenty of RegEx tutorials out there, I wanted to give some simple ideas here that I’ve used to make slightly more realistic data.

Help

There’s a help icon available if you choose a column in one of your tables and have a regular expression selected. In this case, I can see there are a few different suggestions for the “title” column.

SDG guesses that title is a Mr/Mrs/Ms field, but it’s not. I really need something that looks like the title of an article. If I scan through some of the articles on SQLServerCentral, I see titles like:

• Working with SQL Server data in Power BI Desktop
• A Start with Automating Database Configuration Management
• Archiving Hierarchical, Deleted Transactions Using XML
• Beginning SQL Server 2005 Reporting Services Part 1
• Ten Common Database Design Mistakes
• SQL Server Index Basics
• Temporary Tables in SQL Server

In this case, there are a few patterns. SQL Server appears in a few places, Some articles user a “top x” type of format. The word lengths vary, and are in the 6-12 range. Around six or so words are in many titles.

Building a Regular Expression

I could certainly do something like this, which just gives me a few words made up of random letters, of the specified lengths.

[A-Z]{3} [A-Z]{5} [A-Z]{7} [A-Z]{4}

I’ve got a 3 letter word, then a 5 letter one, then 7, then 4. All upper case, all random. That gives me results like this:

Not quite what I want. I’d rather have some words at the beginning like “A”, “The”, or “Top”. I can do that with literals.I enclose those in parenthesis rather then brackets.

(A|The|Top) [A-Z]{5} [A-Z]{7} [A-Z]{4}

This gives me something slightly better.

Not perfect, but better. Let’s clean up the words themselves, following some capitalization rules for titles. In this case, we’ll use a pattern like this:

[A-Z]{1}[a-z]{5}

Now I see something a bit better. This doesn’t make complete sense, but it does look like random word structures.

Let’s change the “Top” item to include a number. I can do that, but changing just the part of the OR (|) that includes Top. I’ll do that like this:

(A|The|Top [3-7]{1})

Now I see a number, randomly from 3 to 7, when Top comes up.

This still isn’t great. How about if I include SQL Server at the end? I’ll use a space, or something with SQL Server.

( |(in|for|on) SQL Server)

That shows me a random addition to SQL Server a the end of some titles.

Getting better. I can even use the space or something to get the versions of SQL Server.

( |(in|for|on) SQL Server ( |2005|2008|2012|2014))

Now I see some interesting titles. What if I want real words instead of random ones? There’s nothing wrong with random data for testing, but if I’m actually trying to compare data values in queries, it’s hard to focus on and remember random patterns. I could use the same OR values.

(A|The|Top [3-7]{1}) (Blocking|Indexing|Tuning|T-SQL) (Tips|Techniques|Methods) (for| |in) ( |(in|for|on) SQL Server ( |2005|2008|2012|2014))

Now I get some interesting, and perhaps memorable titles.

Have Fun

For much of our development work, the data itself doesn’t matter, but if needs to be easily discerned if you want to ensure that it’s easy to examine in queries. While random values work fine, I find them hard to deal with.

I like the idea of using random words. Often the results are still nonsense, but they can be fun to work with. They remind me of a set of magnets my kids have on the fridge. Each is a word that will get randomly combined with others for humorous sentences.

You can do the same thing with some RegEx and SDG.

I’ve been working more with SQL Data Generator (SDG) because it solves some problems that many software developers have with test data. Often each developer needs to create their own set of test data, with these being the common actions:

• Use a backup of production, perhaps old.
• Randomly insert a few values each developer comes up with
• Use random data from SDG, a new project each time.
• Load a known data set from production or test systems

While I think small amounts of random data work well, I think the data should reflect (somewhat) the types of data in production. Totally random strings don’t work, but similar words, structures, etc. make sense.

However having a consistent set of data for each developer is a great idea. SDG can consistently generate data sets, but to make them meaningful, you might want to have control over the types of data inserted.

Using Real Words

I talked about the difference between random words and real words. However, what if you want to include specific types of items?

Let’s evolve some data. If I have a varchar(500) column, SDG defaults to this:

[A-Z0-9]*

Which gives me this:

That’s not great if I wanted to examine specific rows and determine if they were being returned by a query. This is just too random and hard to verify.

However I have options. For example, I could use the “Insert File List” item. This gives me a list of files that could be helpful. In this case, let’s choose “Color”.

I see this in the RegEx box.

($”Color.txt”)

Now my test data looks like this:

What’s in “color.txt”? Let’s see. The file is under the Data Generator 3 folder, in a Config location. I see lots of XML and text files.

If I open Color.txt, I see what I expect.

Now, let’s experiment. Let’s create a SQL Server file. I’ll put values in like this:

I need to change permissions on the config folder to allow saving, but I put it there.  I also had to close and re-open SDG to pick up the new file.

Now I’ll add that to the RegEx box.

That’s cool. What if I made a file with a number of random words in it. Like a dictionary of sorts. I could do this. I create dictionary_small.txt.

Now I include that a number of times.

Those are some great descriptions. I’m sure I could have fun with this in other ways as well. Let’s create some good and bad data for a cleaning operation.

The ability to include data from flat files is a great option in SDG for putting together a data set that developers can actually use, understand, and count on for loading up new databases or tables, especially when creating quick code branches to test something out.