SQL Unit Testing

  • Jonathan Starr-211380

    Old Hand

    Points: 328

    This is interesting - but it's not generating unit tests - it's generating integration tests which are not diagnostic, and do not run quickly, or cover all functionality.

  • Ale Pelc

    SSCrazy

    Points: 2421

    Hi Jonathan,

    I'm confused about what you mean with integration tests. Why you think this is the case ? It's true that I used it to test different systems, as mentioned in the article, but the hole idea is to test SQL SPs. I'm not making any unit test on reports, etc.

    Perhaps I'm missing something, but I think the tool is about unit testing and depending the way you use it, you'll have good unit tests or just simple tests.

    Thanks,

    Alejandro

    Alejandro Pelc

  • Jonathan Starr-211380

    Old Hand

    Points: 328

    Unit tests are specific in nature. A unit test determines whether a unit of code meets a certain specification. When unit tests fail they should point to a single line of code that is improperly written. Typically each unit of code will have mutiple unit tests to do proper testing.

    What you are doing is running a load generated by Profiler against a database. You are running integration tests (or smoke tests) in this case as this test is not specific in nature, does not point to a single line of code when it fails, etc.

    I am primarily a C# / ASP.NET / Ruby developer. If you want to do true unit tests for SQL I suggest you read some Ambler. He's good in this area.

  • Ale Pelc

    SSCrazy

    Points: 2421

    Hi Jonathan,

    I see your point, I misunderstood the "integration test" line. Actually, it's a good observation, and I partially agree with you. If you just run the test as if, then it's more like a smoke test than a unit test. But you must consider that SQL is different that C# or .NET (I'm far away of being an expert in those areas) on the flexibility that unit test provides.

    If you have a stored procedure that fails, and you execute it in Management Studio or other SQL client, you'll get the error and the line that fails. To have the same functionality on the unit tests, you must catch the error on the C# code because if not you'll only get the line that errored in the unit test, not the one in the SP. Using the try catch will give the test the exact line in the SP that failed, even if your SP built a dynamic query.

    I'm sure that there are some other tools down there that do the same or even more than this, and I'll see if I can get my hands in some of them and make a comparison. By the way, thanks for the tip. I'll try to read some Ambler.

    Thanks,

    Alejandro

    Alejandro Pelc

  • RichB

    SSCrazy Eights

    Points: 9651

    WilliamD (4/9/2009)


    1. Either use the Database Snapshot and restore from that: really fast, because only the changes

    ...

    2. Use SQLBackup from Redgate, we use it and it is screaming fast compared to native backup and

    Aye - once we get off 2000... we use Quest at the mo - still an 11 hour restore on our slow dev boxes!

  • w.durkin@online.de

    Hall of Fame

    Points: 3943

    Hi RichardB,

    that is not Quest's fault, it will most likely be down to slow proc. or I/O problems (you did say they are dev boxes).

    I don't think there is much difference between SQL 2000 and 2005 with respect to using Quest/SQLBackup etc. They just use the VDI and pretend to be a tape system and consume data much faster than a tape could.

    Would be interesting to see if there is much difference if you move your dev boxes to 2005.

    Regards,

    WilliamD

  • RichB

    SSCrazy Eights

    Points: 9651

    WilliamD (4/27/2009)


    Hi RichardB,

    that is not Quest's fault, it will most likely be down to slow proc. or I/O problems (you did say they are dev boxes).

    I don't think there is much difference between SQL 2000 and 2005 with respect to using Quest/SQLBackup etc. They just use the VDI and pretend to be a tape system and consume data much faster than a tape could.

    Would be interesting to see if there is much difference if you move your dev boxes to 2005.

    Whooomph.

    Way to miss the point.

    A 1.5TB database takes a long time to restore. Especially on a low spec box.

    The point about 2005/8 as GerbilDBA mentioned is that it gives us the snapshot option.

  • w.durkin@online.de

    Hall of Fame

    Points: 3943

    Hi RichardB,

    I was only making it clear to anyone reading the rest of the thread that SQLBackup or Quest are not the bad guys when it comes to a slow restore. Of course 1.5 TB will take a while to restore, but the two tools will restore this volume quicker than a native restore.

    It is also quite important to remember that Database Snapshots are dependant on Enterprise Edition or Developer Edition, as I mentioned in a previous post, whereas 3rd Party tools can help for the other editions.

    P.S. I am the original poster that suggested the use of database snapshots. I just changed my username on the forums from GermanDBA to WilliamD a few days after posting to this topic. My quoted responses remain with the title GermanDBA though.

    Regards,

    WilliamD

  • RichB

    SSCrazy Eights

    Points: 9651

    Haha fair enough, sneaky ninja action win.

    :hehe:

  • dbunittest

    Valued Member

    Points: 71

    Hi, I would like to share another database unit test tool-AnyDbTest

    AnyDbTest is one outstanding DB unit testing and data validating tool.(Official website, http://www.anydbtest.com).

    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 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.

Viewing 10 posts - 16 through 25 (of 25 total)

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