An Example of Test-Driven Development

  • Test-Driven Development doesn't work in every situation. In my experience, nothing works in every situation. That's one of the reasons I titled the article "An Example..." in the first place.

    My goal in this article is to introduce the concepts. Granted, the example lacks practical application in most scenarios. But there are benefits to this approach that I explore in the next part of the article.

    Hang in here with me. All of this probably won't work for you, but I bet pieces of it will.

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Andy Leonard (5/6/2009)

    Hang in here with me. All of this probably won't work for you, but I bet pieces of it will.

    Would that include purchasing Team System licenses and going the dbpro development route forcing your database developers to develop in Visual Studio, deploying database changes using team system, no access to real data or databases? What's your next sales pitch: "If you don't use source or version control, I predict you will one day." {whether you want to or not} you should have added.

    It never fails to amuse me how many different angles this comes from.


  • Hi Max,

    There are different mindsets for DBAs and database developers. There's stuff they share, and stuff that one can learn from the other. It's folly to assume DBAs are going to think like database developers or database developers are going to think like DBAs.

    All this means, in this context, is: People in our field are going to react differently to the idea of test-driven database development.

    Regarding Team System: If you've been working with SQL Server for a while, you can see a pattern in the tools emerging. If you worked with DTS in SQL Server 2000, you did that in Enterprise Manager - the same place you did a lot of enterprise DBA tasks. With SQL Server 2005 came SSIS, and along with it a version of Visual Studio for developing SSIS.

    Can you do SSIS development inside SQL Server Management Studio? Yes - and you do, in fact, whenever you create a Maintenance Plan. So if it's possible inside there, why not leave it all there?

    My thinking is Microsoft is attempting to sever purely development activities from administrative activities. If I was a betting man, I'd bet the trend will continue. If that bugs you, you may be bugged.

    Database Edition is free with Developer Edition as of October 2008. A full Team System license isn't required unless you're wanting to do other Team System stuff (and then it's justified for those reasons).

    As for source control, you may not like it - many do not - but it's a reality for most database and application developers. Beyond the legal / auditing requirements, source control is just a good idea. I doubt seriously you'd question the assertion you should backup your database. One (merely one) benefit of source control is it backs up your business logic.

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • mike brockington (5/6/2009)


    Maybe I should have made it explicit that my comments were based on experience, not on conjecture.

    If the OP thinks it works well, I would be interested to hear what was different about his setup.


    Sorry, I don't follow what you are getting at.

    A previous employer implemented TDD for their projects. A co-worker from there said it didn't work but explained why. The developers wrote their tests to pass their code which isn't how it is supposed to work.

    Maybe it didn't work for you, but that doesn't invalidate the methodology, just the implementation you were working under.

  • I see you've had to try and justify this all before, and yes, I've heard it all before too.

    Suggesting that I'm just not with it isn't good enough Andy. My company has been struggling for over two years now to get VSTS and dbpro integrated as part of our development lifecycle and that includes all aspects of it right down to customised SSRS reporting based on the Team System database and cube and I'm strongly suggesting that it's an immature product that isn't ready for market yet. Why did we go down this route despite the incredible cost in money, time and sanity? well because of "legal / auditing requirements" paranoia. I know another company who had similar issues and eventually had the common sense to pull out before their evangelists started suggesting it was the team who were wrong and not them. But hey, if you want to be responsible for costing your company millions of squids in times like these and getting nowhere for greater overheads, be my guest.

    Here's my suggestion:

    - If you want to back up your databases (presuming you don't have a maintenance plan) and you prefer having your database comprised of a collection of text files, why not script them out and hey presto there's, thrown into the bargain, your auditing trail (see SQLCompare).

    - If you want a customisable quantifiable work register see Fogbugz.

    - If you want a refactoring tool, write one, I have.

    - If you really like checking things in see TortoiseSVN.

    I just don't see how defining duties between dba's and developers (BI included) based on the current product suite will herald a grand new future. Frankly, I think it's irresponsible of MS to release such an immature product.

    There goes my invite to the next developer forum :doze:


  • Hi Max,

    Thanks for sharing your experiences and insight. We disagree, but I respect where you're coming from.

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Thanks, Andy,

    I've been following along with these articles over the past few months, but didn't have the time to sit down and DO your examples. (Your explanations are GREAT, btw!)

    We've been kicking around the idea of adding unit-testing for a few months now, but I have been so busy, I didn't want to slow down to write some. (No, we don't have the luxury of a Test Team right now.) Although I test the heck out of my code, I have really been running "functional" tests, not unit tests.

    Building the tests from the very beginning looks like the way to discipline oneself to write them. I am now motivated to use this technique on new projects, going forward.

    Thanks also for walking through building a solution from SSMS. I had never tried that and will try to use that in the immediate future.

  • good

  • Thanks for this good documented article. I definitely will get more into test driven development on SQL Server now. Actually in my old times as Cobol programmer (Hear, hear) this was the rule 😉 You do something and then you check possible return codes. 😎

  • Hey Andy,

    Why would we want this: "Unit Test developed here will live on as a Regression Test for the remainder of this database's lifecycle".

    What the purpose of keeping this test? If my database is gone, I will get a clear error message and know it is gone. I will spend no time troubleshooting, and this test will not help me in any way.

    If the clear error message thrown by the server is suppressed, we should rather fix error handling - we need to do it anyway.

    I suspect that this test would just bloat my project and slow everything down; it is not worth keeping it.

    Can you please explain in which scenarios we are better off having this test than not having it?

  • Hi Alex,

    While I agree this adds to the amount of code that lives with the project, I disagree that it's bloat. For me, it's a question of how you want to manage change. This isn't the only way to do it. There are ways to manage change without using a process and those ways have advantages and disadvantages. There are change management processes and methodologies which, again, have their advantages and disadvantages. This is merely one process to manage change.

    For me, regression test results are initially a Boolean and answer the question: "Did I just break something I fixed earlier?" Regression tests should grow over the life of the project, and I find a natural relationship between unit tests and regression tests. One way to express that relationship is this: "Current unit tests find their way into the regression testing scripts."

    If this doesn't work for you - for any reason - don't use it. It works well for me.

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Andy,

    What will happen if you just drop this test? Is there any chance the problem you are testing will go unnoticed?

    I don't think so. If your database is gone, every other test will surely fail as well, right?

    So let us apply Occum's razor and remove this test - it does not add any value. The biggest problem with unit testing T-SQL is slowness, so getting rid of unnecessary tests is highly important.


  • Hi Alex,

    When it comes to testing, I prefer to apply mathematical proof methodologies over Occam. One anecdote about a logic test goes something like this:

    Given: The pencil can move from the chair to the floor. Start by holding the pencil over the floor. How do you get the pencil to the floor?

    The answer is "Place the pencil on the chair." We all know if we drop the pencil it will fall to the floor, but this isn't about what we all know; it's about proving what we all know.

    So no, I respectfully disagree. I understand the tediousness of the code and appreciate that it will not appeal to everyone who reads this article. And I'm fine with you and others dsagreeing, but I maintain this is a viable and useful means to approach test-driven database development.


    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • What an utterly useless and pointless article!! Andy, you need to stick to something you know about, such as SSIS. Messing around with an actual database may well make your head explode!!

    Just kidding! 😀 Nice coverage of the basics of TDD!

    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • LOL Guru!

    I think Alex is trying to tell me the same thing, although he's being much nicer about it than you!


    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply