Post reply

How to Unit Test SQL Changes

  • August 23, 2007 at 12:53 pm

    #118086

    Hi,

    I'm wondering if there is any special tool(s) which some of you guys are using to unit test SQL Server changes such as a change to stored procedure, UDF etc...

    I know some folks are using NUnit for this purpose but apart from this tool is there any tool that you guys are using or recommend me to use?

    Thanks,

    Ignas Chilewa

    MCP, MCDBA

    Columbus, OH

  • August 27, 2007 at 1:34 pm

    #729625

    Does it mean that people don't do unit testing or rather they decide to keep quite as they are not comfortable with this topic..

    Someone come please, give me your experience on this.

    Ignas

    MCP, MCDBA

    Columbus, OH

  • August 27, 2007 at 2:11 pm

    #729639

    Big topic this one. The methods used may depend on what the objective of the testing is. Are you testing to check if the results are accurate and the query returns the results you expect or are you testing to see if the query is efficient under different senarios. Are you are looking to automate this so you can regression test and test your queries regularly or just a once off way manually test the query.

    In order to do either, a good set of test data is important. A copy of your production db is best but if you cant have that then you will need a test db containing all the senarios you want to test. Before you run a test you need to be able to predict what the results should be so you either need the results from a previous query or you need to run a few manual queries so you know that your unit test are accurate.

    We have web apps and use selenium scripts to test the front end which in turn tests the back end so we can check the results of the queries are accurate and return everything the front end needs. This is very useful as we can script out pretty much every senario and throw multiple requests at a query testing it for sorting and a variety of parameters. It takes a bit of time to get used to this and build the scripts but it's great when you can regression test your entire application in a couple of hours. Something that takes 2 weeks if you do it manually and you don't get half the coverage that the well build selenium scripts can cover. Selenium is open source.

    If I'm working on an existing query, I will make a copy of it. When I'm ready to test, I will create a script to execute the old query several times outputting the results to a test file. I will do the same on the new query and then compare the two text files to ensure my new query returns the same results as the old query. I can test various senarios with various usernames using EXECUTE AS LOGIN which allows you to run a query as if you were a different user.

    Beyond that you get into running queries and watching the execution plans and sql profiler to ensure they are efficient and don't use more resources that they need to.

  • August 27, 2007 at 3:27 pm

    #729658

    Everyone's favorite answer....it depends.  Roger has given you some very useful info here, but much of what you are after depends on what you are trying to accomplish.  You say unit test, but that could mean 10 different things to 10 different people.  Can you give us an examle of what kinds of scenarios you want to test?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • August 27, 2007 at 10:56 pm

    #729692

    No... I'm just shocked that someone who boasts the MCDBA in their signature line is asking such a question.

    There is no magic to this... you need to know what the original code was supposed to do, test it before you change it with some basline tests that you write and verify, then, do the changes and see if the meet the tests, which you have also written, for the new requirements.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 28, 2007 at 3:35 am

    #729717

    I've used version numbers on the stored procedures to help with the unit testing problem.

    sp_getWidgetsByProductId1(int @productId)

    sp_getWidgetsByProductId2(int @productId)

    Scott

     

    Kindest Regards,

    scoinva

  • August 28, 2007 at 8:39 am

    #729800

    Jeff maybe we should link this question to the certification debate

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • August 28, 2007 at 10:10 am

    #729832

    Thank you Roger your detailed response.

    It is true that I wanted to see how people automate their unit testing strategies.

    I've been testing my changes be in a stored procedure, trigger, UDF etc using Scott's  way.

    But I've read some articles and know that there is so many ways to automate testing using NUnit, TSQLUNit, DBUnit, Visual Studio 2005 Team Edition for Database Professional, Selenum etc etc...

    So the main objectives of my question was to hear from your experience which one is your favourite and if possible to know when it is very useful.

    Remember the changes which am talking about relate to enhancement, defect fix etc... I want to test for accuracy of results as Load/performance testing is under control so far. And being able to do regression testing whenever possible.

    Thanks,

    Ignas

    MCP, MCDBA

    Columbus, OH

    PS: I here to learn and open to learn as much as I can..

  • August 28, 2007 at 9:48 pm

    #729964

    quote

    PS: I here to learn and open to learn as much as I can..

    No doubt and good attitude!  I'm sorry if my comment sounded as if it were directed at you, Ignas.  It kinda was but not for the reason it may have sounded... I'm just a bit ticked at Microsoft for the, in my opinion, poor training they or their constituents do...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 28, 2007 at 9:49 pm

    #729965

    Heh... doesn't matter, Rudy... folks can always buy hardware to make up for the lack of knowledge

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 29, 2007 at 10:08 am

    #730178

    ... ahh yes ... the old 'tune it up with money' trick ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

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