Why Don’t You Unit Test Code?

First, I’ll apologize to those that do use formal, automated tests in advance. I’m not really writing for you, and I’m sure you can teach me more about testing. Perhaps you can comment about how and why you do this, or even write some articles to help others.

For everyone else, why don’t you test in a repeatable fashion? Let me know with a comment today.

One thing I’ve noticed as I’ve talked DevOps and testing for a number of years is that so many developers agree testing is a good idea, but claim they have no time, no support from management, or no need to repeat tests once code is written. Sometimes I hear all three excuses at the same time. The same people will also admit that they have problems with code at times, and that quality could be improved.

Perhaps it’s me, but I’ve never had my boss sit there and watch me code. He or she doesn’t know how I might write code. I certainly write a query, run it, and look at a bunch of rows and see if I think the code is correct. Perhaps I need to run other queries to verify values in the results. However, I could also build a quick test with something like tSQLt, like the one I set up for checking COALESCE that does the same thing. With my unit test, I can run it over and over, modifying the test as I discover new edge cases to validate. Which way do you want to write code?

Even if I have a set of data I need to assemble, it doesn’t take much longer for me to write a test, especially when I need to also solve the problem. I find that putting a test together forces me to slow down and think about the rules for my code. Since I need to come up with some result set, building that into a test of some sort is fairly easy and let’s me double check how I think data will change. I can then repeat the test over and over as I modify code. I can even grab sample data from production and use that as the basis for my unit test if I need to check a specific entity’s values.

I know many things I write in SQL might not be worth a test. Often the simple CRUD queries and basic aggregations aren’t things I’d test, but when my query becomes complex, includes APPLY or outer joins, and logic to decide how to filter or format data, wouldn’t a unit test make sense? What about if you want to be sure you’re processing the correct rows from a large CUBE or ROLLUP operator? Certainly if I’m fixing a bug, writing tests makes sense, at least I think so.

Let me know what you think today.

Steve Jones

The Voice of the DBA Podcast

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

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.

6 Responses to Why Don’t You Unit Test Code?

  1. Brent Ozar says:

    The answer is in your post:

    “I find that putting a test together forces me to slow down and think about the rules for my code.”

    I find that most folks are just under heavy time pressure from the business, and the business wants them to move faster. The business doesn’t usually see the value in unit testing. (I do, but just echoing what I hear from most folks.)


    • way0utwest says:

      Yeah, I Agree as well. They get under pressure, but they’re not helping themselves. I’m in this boat, and I sometimes think that way. However, when I watch myself do:

      select x …

      — run something

      select x …

      — change code
      — run

      select x …
      — stare are results

      — change code
      — run
      select x…
      — stare at results

      I realize I’m wasting time. If I put results down in a quick test (use a template/SQL Prompt snippet), then set a test, I’m usually no worse on time. THe business doesn’t care, nor should they. We should care.

      I probably did a crappy job explaining that last sentence.


  2. Bryant McClellan says:

    Our business does see the value, perhaps because we have communicated it well. After the initial hurdle we spend about the same amount of time writing code if you include the ‘think time’. That think time results in better code as Steve alluded to.

    There is a lot of value in writing tests for that ‘simple’ code you dismiss. Verifying the schema of a simple GET stored procedure means you can make changes without trashing some other developers’ EF work downstream…causing more thrash and time spent.

    There is definitely a learning curve and cost involved but over time we have found it to be absorbed by implementing better code in the beginning.


  3. Benjamin Cuningham says:

    1. My boss does not care. He has never created unit tests and has no plan to start now.
    2. I do not know how. I spent a few hours last week adding a unit test to a practice project, but I have no idea where to start with an old code base that features functions thousands of lines each.

    I really want to start creating and using unit tests. I really want to have a CI pipeline. I really want to have centralized issue tracking with bite sized chucks of work. The question is, where to start and how to convince my team that these changes are worth the upfront coast.


    • way0utwest says:

      Start writing your own tests. Even if no one uses them, write for the things that you need to test. Not everything, but the things that are complex, or require a few cases. My testing Coalesce gives one pattern.

      See if this helps you code better. Do you get code done cleaner? Do you slow down a bit and think through requirements?

      There is barely an upfront cost. Start writing a test for something you do now. Don’t start a long project that tries to build a bunch of testing.Adding one test to a complex proc/method/query today is valuable and make your code better tomorrow than it was yesterday. Make that the goal. Get better.


    • Bryant McClellan says:

      Our SOP is ‘You are not responsible for testing everything that went before, only the changes you are making. On the other hand, if you have time…’

      As Steve says you simply have to start. We use the tSQLt framework. It does a lot of things for you so you can concentrate on the specifics of what you are testing. Writing tests first forces you to think through the development scenario before you commit any code. The end result is your boss will see that you deploy more dependable code and deploy fixes much less often.

      A somewhat more tangible long term benefit for your boss is the library of regression tests you will build. Every subsequent change also relies on those original tests passing. Generally you develop each test once and tweak it now and then. But it lives as long as the database does and yields the same testing benefits for years to come.


Comments are closed.