I was playing around with TDD again. It highlighted to me just how bad a very simple and correct looking function can be.

In maths, Factorial(n) is written n!, so Factorial(10) is the same as 10!.

1! =1
2! =1*2=2
3! =1*2*3=6
n! = n * n-1 * n-2 * ... * 1

I wrote this function without TDD to calculate the nth Factorial number recursively:

I then rewrote the function using TDD. I discovered 4 quite serious bugs in the above code, see if you can find them (answers in my next post, although I expect there will be others that I've missed too). I also tried a radically different approach to writing the function, how would you write it?

The benefit of unit-testing is that seemingly correct code should get smoked out.

The benefit of TDD is you get a better design, and just enough tests to prove it is correct.

Selecting random rows from existing databases:

NewID() generates a GUID which can then be used to sort the rows into a random order

SELECT
 TOP n
 <query>
 ORDER BY NEWID()

You can use this technique to generate whatever you like names, addresses or ref codes. If you do it once per column, you have pseudo random dta i.e. you won't be using a whole specific row from the database.

Excel: To generate test data in excel with a particular frequency distribution:

Say you want to generate random ISO 4217 'currency codes' but 80% of the time you want GBP, 10% USD and 10% JPY.

Create a range of data on a separate tab (call it DataDistribution) as follows
A1:A10 number 1-10
B2:B8 'GBP'
B9 'USD'
B10 'JPY'

Then to generate a random value according to the required frequency distribution, you can use the following function:

=VLOOKUP(RANDBETWEEN(1,10),DataDistribution!A1:A10,1,FALSE)

You can amend the size of the vlookup array to meet the needs of your frequency distribution - have 100 rows if you want percentages.

Warning - be careful using spreadsheets with formulas that use random data for input data. Excel will recalculate the formulas when the file is opened or when you edit the spreadsheet. To overcome this, copy and paste your data using the 'paste special\values' option.