Automating tests for T-SQL code

  • ladi.molnar

    SSC Enthusiast

    Points: 153

    Comments posted to this topic are about the item Automating tests for T-SQL code

  • hakan.winther

    SSCertifiable

    Points: 5827

    I have not tried this tool yet, but if it works as mentioned, then this is what I am looking for. 🙂

    I will try this as soon as possible and get back to this forum, to share the experience.

    /Håkan Winther

    /Håkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • Andy Leonard

    SSCrazy Eights

    Points: 9865

    I like this idea a lot, Ladislau! I will dig in as soon as I can.

    I'm writing a series on database development. One of the things I'm covering is the evolution of database testing. This looks like highly evolved database testing indeed.

    Great work, and great thinking!

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • kramaswamy

    SSCoach

    Points: 18135

    looks very cool ... definitely considering trying this out

  • Charles Kincaid

    SSChampion

    Points: 13593

    We are doing the same thing with Visual Studio Team Server. The Test Driven Development thing is being integrated into our methodology.

    Your tool looks to be a more affordable alternative. Great job.

    ATBCharles Kincaid

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    Very interesting. Yet this raises a lot of questions:

    1. IMO a very big problem with database unit testing is its sheer slowness:

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/23/controlling-the-number-and-size-of-unit-tests.aspx

    How does an SP call

    EXEC dbo.TestEquals 1.5, @MyVar

    perform compared to an inline expression such as

    SELECT CASE WHEN 1.5 = @MyVar THEN ... ELSE ... END

    2. I see that 11 tests ran for 1 second. How much of that was setup/teardown? What time would it typically take to run 1000 tests?

    3. When you cannot rollback, have you considered restoring from a snapshot:

    http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---testing-database-modifications/

    4. To separate production code from test, have you considered moving test code to a separate schema and or separate database?

  • adrlinux

    SSC Journeyman

    Points: 85

    Hello,

    It sounds very goog ! but is it works with sql server 2000 ?

    Thanks for your answer.

  • dbunittest

    Valued Member

    Points: 71

    I am glad to share one database unit testing tool. It is named as AnyDbTest (http://www.anydbtest.com). After expanding its functionality in previous releases, AnyDbTest 2.2 brings a wide-ranging set of improvements.

    Writing unit and component tests for objects with external dependencies, such as databases or other objects, can prove arduous, as those dependencies may hinder isolation. Ultimately, effective white-box tests isolate an object by controlling outside dependencies, so as to manipulate its state or associated behavior.

    If you are an app developer, you can utilize mock objects or stubs to for controlling outside database dependency. But if you are a DB developer, these mock and stub frameworks cannot give you any helps.

    AnyDbTest (Official website, http://www.anydbtest.com) provides an elegant solution for controlling a database dependency within applications by allowing developers to manage the state of a database throughout a test. With AnyDbTest, a database can be seeded with a desired data set before a test; moreover, at the completion of the test, the database can be placed back into its pre-test state.

    Automated tests are a critical facet of most successful software projects. AnyDbTest allows developers to create test cases that control the state of a database during their life cycles; consequently, those test cases are easily automatic, as they do not require manual intervention between tests; nor do they entail manual interpretation of results.

    The best way to see how AnyDbTest works is to read Quick Start.

  • ladi.molnar

    SSC Enthusiast

    Points: 153

    No, it will not work with 2000. It uses features that are new in 2005 like TRY/CATCH.

  • ladi.molnar

    SSC Enthusiast

    Points: 153

    Alexander, here are the answers to the 4 points that you raise:

    1. Regarding database slowness.

    You touch a good point; yes database slowness is an issue that has to be considered. I would go even further and say that SQL has a lot more limitations when it comes to unit testing. The entire unit test game must be viewed differently when applied to SQL compared with languages like Java / C#. There are a few core principles that remain intact but there are big differences.

    Regarding the SP calls to something like Assert.Equals I am going to give more details at point 2.

    2. Performance numbers.

    I would like to know more about what were those 11 tests you are mentioning.

    In my tests 1000 simple test procedures took just a few seconds.

    On my machine, after a server restart, the first three runs of 1000 tests (see below) took 5233, 3740 and 3830 miliseconds.

    My system is a dual core (AMD 64 5200+ 2.7 GHz) with 2GB RAM memory.

    The test I did was meant to give an idea about the overhead that the TST API has. Here is the test:

    a. I created a database with a simple function (TFN_DoubleInt) that simply multiplied the input parameter with 2 and returned the resulted value.

    b. I added 1000 different test stored procedures. Each looked something like this:

    [font="Courier New"]

    CREATE PROCEDURE SQLTest_PN

    AS

    BEGIN

    DECLARE @ActualValue int

    DECLARE @ExpectedValue int

    SET @ActualValue=dbo.TFN_DoubleInt(N)

    SET @ExpectedValue=2 * N

    EXEC TST.Assert.Equals 'SQLTest_PN', @ExpectedValue, @ActualValue

    END

    GO

    where N is a number between 1 and 1000.

    [/font]

    3. Regarding rollback & restoring from a snapshot

    At this point I think that the cases where the auto rollback will not be able to perform will be fairly rare in practice. Also, when that happens it is a consistent thing not an error like event. TST does a good job of detecting those cases and there is no risk that it will simply let them slide. In that case the user can with a little work do the clean-up himself in a TEARDOWN. But it all depends on the feedback I will get. I am open to that if it becomes a concern and is requested by enough people.

    4. Separating production code from test. Separate schema and or separate database.

    Yes, definitely you can do any of those. You can isolate all the test procedures in a separate database if you wish. Also you can place them in the production DB but isolate them in their own schema.

  • shenoy.roopesh

    Valued Member

    Points: 61

    Hello,

    I just wanted to highlight an article that I had published on InfoQ on how to test with TST - more than dealing with details on TST API, it encourages BDD principles to breakdown tests into meaningful and reusable steps and making them maintainable.

    http://www.infoq.com/articles/tst-sql-server

    Wondering whether you could read and give your thoughts!

    Best Regards,

    Roopesh

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

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