Improve Your Database Unit Testing Skills and Win Free Stuff

by data-centric on September 7, 2012

As the SQL Developer community grows to embrace the benefits of test-driven development for databases, so the importance of learning to do it properly increases. One way of learning effective TDD is by the use of code kata – short practice sessions that encourage test-first development in baby steps. Thanks to the guys at Red-Gate, I have a limited number of licences for SQL Test to give away free – just for practicing a bit of TDD and telling me about it.

All you need to do is to try out a code kata using T-SQL, either on your own or with a colleague, then tell me about it. This could be just a paragraph or two in a comment on this post or an entry on your own blog which links back to this post. Write about which kata you chose and why, how you wrote and tested the code and what you gained from the experience. I will pick the five best write-ups (from a mixture of blogs and comments) and send each of those contributors a single-user licence key for Red-Gate’s SQL Test. You can use any test framework as long as your tests are written in T-SQL.

You could even publish your experiences as an article on Simple-Talk. They pay for original content and provided you include a link back to this post, you could still win a SQL Test licence. Although you’ll have to be quick to allow for the editorial lead time between submitting an article and it being published.

SQL Test

Regular readers will be aware of my enthusiasm for tSQLt the open-source unit testing framework for SQL2005+. I’m not the only one that rates this library since Red-Gate liked it so much they have used it as the platform on which they built SQL Test. This graphical test manager is a plug-in for SQL Server Management Studio, originally developed during one of Red-Gate’s famous down-tools weeks and now an established part of their SQL Developer product suite.

What is a Code Kata?

According to Wikipedia, “kata is a Japanese word describing detailed choreographed patterns of movements practised either solo or in pairs… Kata originally were teaching/training methods by which successful combat techniques were preserved and passed on“. I believe the term “code kata” was first coined by Dave Thomas (blog) in his book The Pragmatic Programmer who writes:

Code Kata is an attempt to bring this element of practice to software development. A kata is an exercise in karate where you repeat a form many, many times, making little improvements in each. The intent behind code kata is similar. Each is a short exercise (perhaps 30 minutes to an hour long). Some involve programming, and can be coded in many different ways. Some are open ended, and involve thinking about the issues behind programming. These are unlikely to have a single correct answer.

What this means in test-driven programming terms is a simple, often deceptively so, logic problem which developers attempt to solve using strict test first development. People will often pair program on this task redoing the same exercise every day for a week or more pairing with different developers each day and trying to improve on the solution at each attempt. Typically, they will spend no more than 30 or 60 minutes per day on these exercises.

This practice is intended to help programmers learn to write code using TDD properly – not always a luxury we have in the real world. The aim is to write code in small baby steps each supported by tests to gain an understanding of the test-first paradigm. The code kata is a safe environment in which to experiment with ideas and best practice without any real consequences or deadlines. Peter Provost has a nice write-up on the importance of kata in learning effective TDD.

Why is SQL Different?

Although often language-neutral, many kata are written with object oriented languages in mind. Looking at how some online examples are developed and refactored, more complex kata would struggle to work in T-SQL in their current form. But whilst we don’t have many of the standard OO features in SQL Server programming, we do have a set-based language along with features like DRI, views and table-valued functions. The nature of the language will lead to different solutions and such features present different opportunities for refactoring.

Which Kata to Do?

The game of FizzBuzz can be a nice easy starting point and I have produced a SQL-friendly version too. The Potter kata might offer a little more challenge and there is also Toy Story which is a kata that I created specifically for SQL. If you’re feeling really brave there is always Uncle Bob’s Bowling Game

You could also choose from the list of kata on Coding Dojo or Dave Thomas also has a comprehensive list although these are not all programming tasks.

The Small Print

  • This is just a bit of fun, these five licenses have been donated by Red-Gate and I get no reward from this other than the pleasure of spreading the TDD gospel and seeing how you get on with your kata.
  • Participants agree to extracts from their comments or blog entry being quoted (with full credit and links) in a follow-up article which will be published on my blog and/or Simple-Talk.
  • Comments and blog entries must be in by 19 October 2012 and winners will be announced and licenses sent by 30 November 2012.
  • Only one licence per winning contribution and there is no cash alternative available.

So have some fun, try it and tell me how you got on for a chance to get one of five free licences for SQL Test worth around £175.00 each (donated by RedGate).

Arthur Fuller September 19, 2012 at 4:26 pm

That was easy and fun, but I admit that I should have know to test the AND condition first. I forgot about that; I was thinking in terms of CASE originally. It printed out fine on first run, but 15 and its multiples printed ‘Fizz’ only. Then I realized.

Here’s my simple quick version:

   ,@j INT = 100

WHILE @i < @j
        SET @i = @i + 1

        IF @i % 3 = 0
            AND @i % 5 = 0
            PRINT 'FizzBuzz'
            IF @i % 3 = 0
                PRINT 'Fizz'
                IF @i % 5 = 0
                    PRINT 'Buzz'
                    PRINT @i


Arthur Fuller September 19, 2012 at 5:08 pm

No! It screwed up again. I don’t know why. The lines beginning with ‘WHILE @i 0’ are all wrong. What’s weird though is that immediately after I paste the code in, and prior to clicking the Submit button, everything looks fine. I’m going to try something else, and if this doesn’t work, I’ll give up.

/* FizzBuzz Version 2
	This version looks for '3' and '5' anywhere with the numbers
	Uses CharIndex() to locate the digits within the numbers
   ,@j INT = 100
   ,@k VARCHAR(2)
   ,@c3 CHAR(1) = '3'
   ,@c5 CHAR(1) = '5'
   ,@l INT = 0
   ,@m INT = 0

WHILE @i  0
                 AND @m > 0
            PRINT 'FizzBuzz'
            IF ( @i % 3 = 0 )
                OR ( @l > 0 )
                PRINT 'Fizz'
                IF ( @i % 5 = 0 )
                    OR ( @m > 0 )
                    PRINT 'Buzz'
                    PRINT @i


Arthur Fuller September 19, 2012 at 5:09 pm

Ok that’s it. I give up. The problem is NOT on my end.

Greg M Lucas October 1, 2012 at 5:32 am

Arthur, please accept my apologies, I’ve just spotted your response on 19-Sep (over aggressive spam filtering). Here’s is Arthurs complete response:

Here’s FizzBuzz version 2. As to testing it, I confess that I did it by eyeball. I walked through the printout and checked each number and its associated message. I left in the original simple print statements so when I was satisfied, I could un-comment them and comment the informational statements instead.

/* FizzBuzz Version 2
This version looks for '3' and '5' anywhere with the numbers
Uses CharIndex() to locate the digits within the numbers
DECLARE @i INT = 0 /* counter */
   ,@j INT = 100 /* UPPER boundary */
   ,@k VARCHAR(2) /* string version OF counter */
   ,@c3 CHAR(1) = '3' /* string version OF FIRST target  */
   ,@c5 CHAR(1) = '5' /* string version OF SECOND target */
   ,@l INT = 0 /* POSITION OF @c3 within string version OF counter */
   ,@m INT = 0 /* POSITION OF @c5 within string version OF counter */
WHILE @i < @j
        SET @i = @i + 1
        SET @k = CAST(@i AS VARCHAR(2))
        SET @l = CHARINDEX(@c3, @k)
        SET @m = CHARINDEX(@c5, @k)
        IF ( @i % 3 = 0
             AND @i % 5 = 0 /* divisble BY 3 and 5 */
            OR ( @l > 0
                 AND @m > 0 /* CONTAINS '3' and '5' */
            /* PRINT 'FizzBuzz' */
            PRINT @k + ': FizzBuzz' + ' - divisible by 3 and 5, or contains 3 and 5: FizzBuzz'
            IF ( @i % 3 = 0 ) /* divisble BY 3 */
                OR ( @l > 0 ) /* CONTAINS '3' */
                /* PRINT 'Fizz' */
                PRINT @k + ': Fizz' + ' - divisble by 3 or contains 3'
                IF ( @i % 5 = 0 ) /* divisble BY 5 */
                    OR ( @m > 0 ) /* CONTAINS '5' */
                    /* PRINT 'Buzz' */
                    PRINT @k + ': Buzz' + ' - divisble by 5 or contains 5'
                    PRINT @i /* neither */

Greg M Lucas September 19, 2012 at 5:22 pm

Arthur, thanks for responding and I’m glad you enjoyed it. Not sure about the problems you were having with paste but I think we get the jist of what you are doing. Just one question, how did you go about writing the tests for this? If you prefer you can email the SQL to “admin” AT this domain and I’ll make sure it appears correctly

Steve September 20, 2012 at 4:49 pm

I chose the FizzBuzz kata (from Coding Dojo–I didn’t want to look at the SQL version you created before I tried it on my own because I [mistakenly] thought you were providing a walk-through). I chose the FizzBuzz Kata mainly because I understood it easily and I didn’t have much time to do it in.

My background is in coding (C/C++); as such, I started the kata by creating a scalar function that took a single number and returned a string (varchar) value. After getting the first requirements done in that function, I began working on how to return the values between 1 and 100 in a set based way (no cursors or while loops).

Instead of creating an SP (which is how I thought I’d do this when I started), I ended up refactoring and creating an iTVF (Inline Table Valued Function) that accepted 2 integers: a @start and an @end. I decided to go this route so that my tests would be testing the actual code that would [presumably] be running in “production” without the need for a stored procedure wrapper. There are good arguments both ways on this so in the end it’s really just a matter of style.

In my tests, I am able to call the iTVF with the same start and end values and get just 1 value back. In that way, I can do things like test that 6 returns “Fizz” (divisible by 3) and that 15 returns “FizzBuzz” (divisible by 3 and 5). It was much easier to write targeted tests against 1 value rather than have to create a temp table to contain a series of values for testing.

It was a rather fun excercise working through a SQL proc in a TDD manner–a little odd for me at first as I am usually used to writing most of the meat of the proc first to see how the data, keys, and constraints line up. In this case, since there was no data (per se), I was able to make the jump to writing tests fairly quickly (starting with divisible by 3 and divisible by 5). When it came to the second requirements, I forgot about writing the test first and jumped right into adding the functionality to the function. Fortunately, I remembered (after writing the “contains 3” portion) and quickly wrote that test and the following tests before going back and implementing any more.

As I was testing the straight-forward implementation, I thought of some other tests to try and put those in there as well. I got snagged on interpretation of the requirements when it came to values that contain both a 3 and a 5 but are not divisible by either (ie. 53). I left that test failing with a note to get clarificaion from the stakeholders. 🙂

I’d be happy to share the scripts I created, but there’s rather a lot of it for a blog entry–and I’m a little leery to try given the trouble Arthur had. 😉 Below is the signature of the function I created as well as a list of the tests I ended up creating as I went:

Function Specification:
CREATE FUNCTION [dbo].[FizzBuzz] (@start int, @end int) RETURNS TABLE

Tests (created in tSQLt):
– [Tests_FizzBuzz].[test Fizz – divisible by 3]
– [Tests_FizzBuzz].[test Buzz – divisible by 5]
– [Tests_FizzBuzz].[test FizzBuzz – divisible by 3 and 5]
– [Tests_FizzBuzz].[test Fizz – contains 3 but not divisible by 3]
– [Tests_FizzBuzz].[test Fizz – contains 3 and divisible by 5]
– [Tests_FizzBuzz].[test Buzz – contains 5 but not divisible by 5]
– [Tests_FizzBuzz].[test FizzBuzz – divisible by 3 and contains 5]
– [Tests_FizzBuzz].[test Fizz – contains 3 and 5 but not divisible by 3 or 5]
– [Tests_FizzBuzz].[test FizzBuzz returns proper number of rows]

Below is a sample test showing how I called the function:

CREATE PROCEDURE [Tests_FizzBuzz].[test Fizz - divisible by 3]
	DECLARE @expected varchar(10)
		, @actual varchar(10)

	SELECT @actual = [value] FROM FizzBuzz(6, 6)
	SELECT @expected = 'Fizz'

	EXEC tSQLt.AssertEqualsString @expected, @actual

Greg M Lucas September 20, 2012 at 7:24 pm

Steve, nice write up. Thank you. No need to provide any further code samples at this stage. I would be interested to know if this experience would change how you code SQL going forward. Would you write more or less unit tests?

Steve September 20, 2012 at 9:35 pm

I’m not sure it’s really changed my view of unit tests (I’ve actually been using tSQLt for the last year in a large project that we’re rolling out in Q1 2013). I jumped on tSQLt for the database once I saw the framework–I had actually been slowly developing a framework for testing, but tSQLt already had several pieces I hadn’t yet implemented or thought through fully.

Right now I have a couple of team members that are helping to write tests (but only in their copious spare time) to get better coverage of existing SPs. We’re only going for the “happy path” at this point so we can potentially find breaks sooner (before check-in). My goal would be to get every “requirement” covered and then begin adding corner case coverage–that being said, the reality of our business is that we typically move very fast and requirements can change as little as once a month to as frequent as a couple times a week–at that rate we might not get full coverage, but it’s a [lofty] goal.

We aren’t doing TDD in any way (either in code development or in the database–and those are different teams at the company I work for). Depending on what I am able to socialize in the DBA area, I think I’d like to begin handling bugs in a TDD way such that we don’t cause regressions in the database tier. I’ll likely have my team do this exercise as well and see how that goes.

Greg M Lucas September 21, 2012 at 8:03 am

Steve, I’m pleased to hear you’re already making good use of tSQLt (you can probably tell, I’m a big fan). I think there is definite value in working through a kata or two to help your team get into the “test-first” mindset. I would recommend trying the SQL-friendly version of Fizz Buzz for the database team as it does address some of the common refactorings one can typically face when trying to do set-based TDD. For example, if you start off returning a result set from 1 to 100 then start adding the Fizz and Buzz functionality, you have no choice but to refactor the existing test rather than adding new tests. This is one of the major ways that TDDD differs from TDD. Toy Story is a longer kata to do so may be better spread over a couple of sessions but is a gain very representative of the kind of thing that we SQL developers do on a day to day basis.

Greg M Lucas October 3, 2012 at 1:08 pm

Zac Harlan provided this solution over on Code Kata for SQL – FizzBuzz

Just knocked this out in about 10 minutes. I’ll spend some more time later today to remove the looping

DECLARE @StartDigit AS INT = 1
DECLARE @EndDigit AS INT = 1000;
WITH  allNumbers(digit)
        AS (
            SELECT  @StartDigit AS digit
            UNION ALL
            SELECT  digit + 1
            FROM    allNumbers
            WHERE   digit < @EndDigit
  SELECT  CASE WHEN digit % 3 = 0
                    AND digit % 5 = 0 THEN 'FizzBuzz'
               WHEN digit % 3 = 0 THEN 'Fizz'
               WHEN digit % 5 = 0 THEN 'Buzz'
               ELSE CAST(digit AS VARCHAR(4))
          END AS Result
  FROM    allNumbers WITH (NOLOCK)

I particularly like his use of a recursive CTE to get a starting point for the record set.

Aaron Held October 3, 2012 at 1:27 pm

My team used the game of life as a department wide kata. It was fun to compare aprroaches in SQL, Javascript, C#, Ruby etc…

Greg M Lucas October 3, 2012 at 1:35 pm

Aaron, that looks like a pretty ambitious kata, particularly in SQL. I would be interested to know more about your team’s attempts to do this in SQL – how they tested it and what they learnt from the practice.

Manoj Joseph October 6, 2012 at 5:40 am

I chose to attack the FizzBuzz kata as it was easy and fun. Knocked it out in less than 10 minutes. I preferred to use Jeff Moden’s tally table method to work this out.

-- Conditionally drop and create a Tally table
DROP TABLE dbo.Tally

INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

--Now the select statement to get the results
Select Case When Convert(varchar(10), N%3)=0 and Convert(varchar(10),N%5)=0 Then 'FizzBuzz'
When Convert(varchar(10),N%3)=0 Then 'Fizz'
When Convert(varchar(10),N%5)=0 Then 'Buzz'
Else Convert(varchar(10),N) End as [Result] from Tally
where N<=100

Manoj Joseph October 6, 2012 at 5:46 am

There is some problem in the way the quotation mark gets converted when copying the code from the website and pasting it in SSMS. We have to remove the quote and type it in again for the code to work as the quotation mark is shown in black colour instead of red.

Greg M Lucas October 7, 2012 at 8:09 am

Thank you for taking the time to respond and full credit to you for attributing the tally table to Jeff Moden. Sometimes people use bits of code published by other people so often they forget where the original idea came from. That’s fine until they include it as part of something they make public when proper credit should be given (as you’ve done).

Not sure what was happening with your cut and paste from SSMS becuase as you can see it displays fine.

Carol D October 9, 2012 at 11:24 am

On starting the Code Kata, I hadn’t done one before, so I was interested in trying that way of working also I’d only recently tried the tsqlt framework so was an opportunity to get more familiar with using it. The tests I’d done before were always written after the thing they were testing so I was wanting to discipline myself to try to write the tests first. I found following the ‘Code Kata Way’ quite productive as tasks were being broken down into small manageable chucks and each session I was approaching with a fresh mindset. I’ve now got some discipline in writing the tests first and each session I felt I was refining and improving the product. The finished product isn’t perfect, I know there are better ways of doing some of the things I’ve done but I feel I have confidence in the product working as expected and feel it’s far more maintainable.

Day 1 CodeKata FizzBuzz 35 – 40 mins
Slow to write the test, spent most of the time doing this as needed to check the tsqlt syntax, to keep time spent doing it short, the test just checks the result set is made up of 100 elements. Found that the code I wrote to populate the expected result set I used in the Fizz Buzz procedure so I didn’t spend long at all writing the FizzBuzz procedure. So felt it was a lot of effort to write the test but it made writing the procedure relatively easy.

Day 2 CodeKata FizzBuzz 45 mins
Replace any number which is divisible by 3 with “Fizz”
Again as my test is comparing expected and actual tables, the code I write to populate the expected table ends up being used in the FizzBuzz procedure to implement the logic

Day 3 CodeKata FizzBuzz 40 mins
Replace any number which is divisible by 5 with “Buzz” and
Replace any number which is divisible by 3 and 5 with “FizzBuzz”
Done both of these, got them done quicker today. When I ran all the tests by divide by 3 and divide by 5 tests now fail, I need to change those tests, my expected result sets needs to change as the code for FizzBuzz developes, am thinking about having the expected result set in test startup so it’s in one place and I only need to change it in one location.

Day 4 CodeKata FizzBuzz 35 mins
Created a setup procedure for my expected result set and modified all tests to use this, simplifying the tests.
Started Stage2 Refactoring to use a separate string function given an integer input and checked all tests still pass

Day 5 CodeKata FizzBuzz 35 mins
Stage 2 Refactoring, wrote test to test correct values and out of range values tests failed for out of range 0 and null, only needed to amend test and FizzBuzz function so 101 would expect or return the string ‘OutOfRange’, all other tests still successful
No loops or cursors in the production code so stage 2 now complete

Day 6 CodeKata FizzBuzz 40 mins
Stage 3 Changing Requirements
Created a new function fn_FizzBuzzContainsDigit and amended fn_FizzBuzz to use it and my test setup function to use it

Day 7 CodeKata FizzBuzz 40 mins
Decided using function in the setup not a good idea as not testing the function independantly so changed the setup to just use a manually populated table and not the function. Then found the tests failed and it was due to the number 53 which is not divisible by 5 or 3 but contains both digits so had to decide which takes precedence and decided to use the assumption the string of the highest digit takes precedence ie buzz, had to amend the function to include this logic too

Day 8 CodeKata FizzBuzz 40 mins
Stage 4 new features
Added a new test for a FizzBuzz range and amended my FizzBuzz sp to accept input parameters for the start and end of the range. And amended tests that use the sp. On looking over this CodeKata noticed I do have loops in my code so next step would be to refactor those out.

Products of CodeKata:
test FizzBuzz number divisible by 3 Fizz.sql
test FizzBuzz replace number divisible by 3 and 5 by FizzBuzz.sql
test FizzBuzz replace number divisible by 5 by Buzz.sql
test FizzBuzz return result set with 100 elements.sql
test FizzBuzz returns correct value.sql
test FizzBuzz returns result set within range.sql

FizzBuzz is the final sp, it uses the function fn_FizzBuzz which in turn uses the function fn_FizzBuzzContainsDigit. SetUp.sql is the setup for the tests which generates the expected table of results, 6 tests compare an actual table with the expected results, or compare counts or compare individual values as part of the results.

Greg M Lucas October 9, 2012 at 11:53 am

Carol – this is a really nice write up. It seems that you gained a lot from this exercise.

I’m glad you tried the sql-friendly version of FizzBuzz as it was designed to expose a dilema typically faced when writing tests for set-based outputs. As I see it there are two choices…

1) Write a series of tests checking that each rule is followed separately (as you have done) – which means that you have to factor each of these tests as the complexity of the result set increases.
2) Write a single test to cover all aspects of the result set – factoring this single test as the complexity increases – which means that you’re breaking one of the fundamental rules of testing – one assertion per test

There are merits in both choices. Although option 2 might offend some purists in my experience those people are often from a C#/Java type background rather than SQL. Option 1 involves more ongoing test maintenance but does ensure that (in theory) such tests “should” only have a single reason for breaking. But as you found out that isn’t necessarily the case 🙂

As I write this, I’ve just thought of a 3rd option – write a single test for each assertion but filter the results for the condition being tested. That way, if other untested conditions change the test wouldn’t need to be re-factored. You would probably still need one test to cover the entire result set though.

Thanks again for your input – it’s given me food for thought

Alex October 11, 2012 at 1:24 am

I tried to take a set based approach to do the FizzBuzz kata.

DECLARE @Numbers AS TABLE(intNumber INT);
DECLARE @Fizz AS TABLE(intNumber INT, strWord VARCHAR(50));
DECLARE @Buzz AS TABLE(intNumber INT, strWord VARCHAR(50));


@FizzNumber = 3,
@BuzzNumber = 5,
@FizzText = 'Fizz',
@BuzzText = 'Buzz',
@Count = 100;


INSERT INTO @Numbers( intNumber )

INSERT INTO @Fizz ( intNumber, strWord )
@FizzText AS strWord
@Numbers AS NumSrc
NumSrc.intNumber % @FizzNumber = 0
OR CAST(NumSrc.intNumber AS VARCHAR) LIKE '%' + CAST(@FizzNumber AS VARCHAR) + '%';

INSERT INTO @Buzz ( intNumber, strWord )
@BuzzText AS strWord
@Numbers AS NumSrc
NumSrc.intNumber % @BuzzNumber = 0
OR CAST(NumSrc.intNumber AS VARCHAR) LIKE '%' + CAST(@BuzzNumber AS VARCHAR) + '%';

WHEN FizzSrc.intNumber IS NULL AND BuzzSrc.intNumber IS NULL THEN CAST(NumSrc.intNumber AS VARCHAR)
ELSE ISNULL(FizzSrc.strWord, '') + ISNULL(BuzzSrc.strWord ,'')
END AS Display,
CAST(NumSrc.intNumber AS VARCHAR)

@Numbers AS NumSrc
@Fizz AS FizzSrc
ON NumSrc.intNumber = FizzSrc.intNumber
@Buzz AS BuzzSrc
ON NumSrc.intNumber = BuzzSrc.intNumber;

Greg M Lucas October 11, 2012 at 5:44 am

Thank you for your response.

It looks like you’re basing your set on Itzik Ben-Gan’s virtual numbers table – which is similar to FizzBuzz solution I tend to end up with.

Alex I would be interested in how you went about testing this. One of the main reasons for practicing kata is to get better at test-first development.

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: