Voice of the DBA

Tag: software testing

  • No Time for Testing

    There is a bit of a rant from John Welch on testing your data manipulation that I like. I think some of the impact is lost because the end shows an advertisement for a product that helps here, but the points made are good. We all want to test, we think it’s hard, we don’t have time, and our businesses live with the issues from limited testing.

    I’m not a fan of Test Driven Development, as John is. Usually this is because I’m not always 100% sure of the results I want or have been given. I’ve often been given a request to do x and as I get involved, I find that the requirements might be incomplete, or even wrong, and they’ll change. As a result, I like to write a little code, get some idea of what I want to return or change, and then write a test that verifies what I’ve done is correct.

    It’s a subtle difference, and maybe I’m doing TDD in the wrong order, but I like to get code, test it, then think about potential issues (which I might find as I write code) and write a few tests for the things that I’ve missed.

    However I do believe we need to test our code. We all do test our code, even if it’s with a few before/after queries. What I don’t get is why we don’t just mock up a quick test that we can run in an automated fashion. It’s not much more work, and then we can more easily re-run the test later to ensure any refactoring or optimizations we make continue to work as expected.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • Is Unit Testing Important?

    This editorial was originally published on Nov 10, 2009. It is being re-run as Steve is on vacation.

    I saw a question on the SQLServerCentral ASK site the other day asking about unit testing for T-SQL stored procedures. There weren’t many responses, but they were interesting to me.

    I wondered how many people bothered to test their stored procedures as a unit. It seems that the idea of test driven development, or even of comprehensive testing of SQL code isn’t very mature. If a query runs and appears to return the data we want, do we bother to test for edge or unusual data conditions?

    Or do we even know how to write those kinds of tests?

    Testing your code as it’s being written is a well known way to reduce your technical debt and ensure better code when it’s released. It’s also a way to lower the cost of maintaining your software over its lifetime. However it slows down development as well.

    That’ s not why most people don’t test their code well. The big reason is that it’s B-O-R-I-N-G.

    But does it matter? Is there really an issue? After all most T-SQL queries are built to scale. If the data is correctly assembled into sets with a small set of data, it will work correctly on large sets of data. Maybe not efficiently, but it tends to work. So is unit testing of T-SQL code important?

    My guess is that it is not all that important. At least not as important as performance tuning. If you have extra time to spend on a query, I think most DBAs would prefer to spend it on making the SQL more efficient, not more tested.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • Runtime Code

    I was talking with someone recently who was building a dynamic process that generated its own code at runtime to process ETL files based on a set of rules. It was an impressive system, and reminded me of how BIML can dynamically produce code and build packages.

    However then I started to wonder. If you produce code at runtime that then runs, how well is that code tested? I suspect most people would test the code generation process. Is this producing the code I expect, but how many would then write additional tests for the generated code as it executes? I suppose people might also just test the generated code, but if that’s the case, are they really testing the generation process extensively?

    I’m not sure there’s an easy answer, and not sure if there’s actually a strong testing framework that exists to help here. As BIML becomes more popular and even ETL frameworks are used more and more in our software processes, I hope that we develop new testing methods to better ensure we are building quality code. I know some SSIS test frameworks exist, and there has been some writing on the topic, but I wonder how many SSIS developers bother to perform anything more than cursory tests? My suspicion is very, very few.

    Ultimately I think we as an industry need to learn to build better software by improving our techniques, samples, skills, and testing.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 15.2MB) feed

    MP4 iPod Video ( 17.9MB) feed

    MP3 Audio ( 3.7MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Test Coverage

    I’ve never had to work full time in a QA group, but I have had to perform software testing of an application and it wasn’t fun. Even as I worked through the various features, looking for edge cases, common mistakes, etc., I realized that running tests was something that really required more discipline and process than I was giving it at the time. Too often I ‘d realize that my regressions weren’t 100% duplicates of previous executions because I’d allowed too much “human effort” into my process.
    These days most testing of software is automated. I know QA groups still exist, and they need to handle some of the manual checks that are very hard to automate. However more and more testing is being pushed back onto developers to handle, with frameworks like NUnit and JUnit. In the database world, we haven’t done a great job of including testing into the code we write, but there’s a great testing framework we can use.
    TSQLT is a framework written by Sebastian Meine and Dennis Lloyd and it’s free. It’s been developed to help you write tests that can exercise your T-SQL code to determine if it’s doing what you expect. I’ve used it a little, and while I see the potential, I also realize that this will take some practice to learn how to more easily write tests that can cover the various potential places where bugs can be introduced.
    However, if you use version control, and you should, then you can easily spread the load of writing tests to all of your developers. Since the tests are stored procedures, they can be included in your VCS project and shared by all your developers. With a little effort from each member of your team, you might be surprised at the code coverage you can achieve with this framework.
    Testing is important, as we see over and over again as software is released and doesn’t work as expected. I think much of that is our fault, as software developers, for not improving our testing skills and discipline.
    Steve Jones
    If you want more automated ways to easily share tests between developers and execute them, take a look at SQL Source Control and SQL Test from Red Gate Software.

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 20.9MB) feed

    MP4 iPod Video ( 24.8MB) feed

    MP3 Audio ( 4.9MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center