A T-SQL Code Testing Guide

  • Comments posted to this topic are about the item A T-SQL Code Testing Guide

  • Is there an inflection point for the utility of running the tests versus how many times you will use the script?

  • Having done unit testing for 14 years using standard frameworks and about a further 5 years using handcrafted frameworks, I find it rather restricted, limited and backward that I do not apply the same techniques when coding for RDBMS'. This is in part due to the lack of tools (there are some out there as the article highlights), lack of support from the DBA teams (there will be exceptions, however, this is my experience) and/or lack of authority to add any SDLC tools, be they homespun or third party.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • We, as T-SQL developers definitely do need to get better and formalize our testing, I blogged the other day about how to test constraints for example:

    http://sqlserverfunctions.wordpress.com/2014/10/05/unit-testing-constraints-in-sql-server/

    I believe unit testing basically comes down to separating your code into small units (i.e. Lets get away from thousand line stored procedures) and deciding what the code unit should and also inversely shouldn't do!

  • My idea of testing begins at the requirement. You need to have the test data in place and the input and output defined for each phase or unit of testing before you start coding. This allows you to test each section of code as you build. Once you have the code completed (you think) you can test against the expected results and for performance. It is critical to have enough data to test all scenarios of input and output and performance. I think it would be great to have some automated way to do this testing but I can't think of a way that wouldn't require almost as much work to set up as it does to just test as you go. With regard to testing code you are changing, it becomes even more important and difficult to test someone else's code since you may not have the full details of what it used to do only what you have been asked to make it do now which can lead to missing tests that should be done to make sure it still does what it needs to as well as what you are making it do. Testing is hard and I know I have not done an adequate job at times do to a lot of factors. Hopefully I am getting better with each assignment.

  • dean.giberson 64357 (10/7/2014)


    ...I think it would be great to have some automated way to do this testing but I can't think of a way that wouldn't require almost as much work to set up as it does to just test as you go...

    Once people started using unit testing tools this very issue was resolved by the tools providers (amongst others). They "stole" ideas off of each other and drastically lowered the time it takes to write the tests. Also, tests being continuously run ensures that even after you leave your code your tests remain in place providing continuous value.

    It just needs a push by a subsection of DBAs to drive this into common practice.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • I've yet to come across a way to test for everything. So no matter how much we test, and the more the better, there will always be that chance that something slips through, some error you just can't find until it goes to production.

  • Fortunately for me, my mistakes won't cost someone their life.

  • crussell-931424 (10/7/2014)


    I've yet to come across a way to test for everything. So no matter how much we test, and the more the better, there will always be that chance that something slips through, some error you just can't find until it goes to production.

    There are techniques, tools and languages that can afford this but it requires a prohibitive amount of time. Prohibitive due to the distinct lack of life threatening risk as you say:

    crussell-931424 (10/7/2014)


    Fortunately for me, my mistakes won't cost someone their life.

    (I am in a similar boat i.e. if I mess up no one dies - unless I believe the PM that they will kill me ;-))

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Robert Sterbal-482516 (10/6/2014)


    Is there an inflection point for the utility of running the tests versus how many times you will use the script?

    Probably, but I'm not sure. I (personally) think the value is mostly in the number of times I may change something. I think these unit tests are most valuable for preventing regression issues as I evolve things than testing the happy path I expect.

  • Iwas Bornready (10/7/2014)


    I've yet to come across a way to test for everything. So no matter how much we test, and the more the better, there will always be that chance that something slips through, some error you just can't find until it goes to production.

    Absolutely, and I wound't try to test everything.

    I'd test common problem patterns (nulls, blanks, 0s, negative, etc), and then I'd write tests for bug that come back from production so I didn't let the same thing through again.

  • Steve Jones - SSC Editor (10/8/2014)


    Robert Sterbal-482516 (10/6/2014)


    Is there an inflection point for the utility of running the tests versus how many times you will use the script?

    Probably, but I'm not sure. I (personally) think the value is mostly in the number of times I may change something. I think these unit tests are most valuable for preventing regression issues as I evolve things than testing the happy path I expect.

    Most people forget that we should consider the tests as a baseline. Just as with performance testing you need to measure (test) BEFORE changes are made. If the tests are not produced during initial development then rarely are they an expense allowed during a mere change.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

Viewing 12 posts - 1 through 11 (of 11 total)

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