I'm working on developing a big 3-tier application.

Database has a lot of code (stored procedures, functions, triggers, etc). I'm looking for a valid approaches how to implement unit testing for the database code.

  • "Should I use a separated database to run unit tests?"
  • "How should I implement stored procedures to be ready for unit tests?"

Any help, tips, tricks are welcome!

  • What environment are you using? There are some special tools available for some environments. Commented May 25, 2011 at 17:45
  • @Ethel Evans windows, sql-server 2008, visual studio 2008
    – garik
    Commented May 25, 2011 at 23:53

6 Answers 6


You can also check tool - TST - TSQL Test Tool. Tool is available in codeplex http://tst.codeplex.com/

For DB test automation below parameters pass for TST - TSQL unit

  • Ability to run suite of test cases
  • Ability to run individual test case
  • Handling Assertions without any extra coding
  • Data Driven Testing can be implemented; this approach involves QA writing TSQL Code

I would recommend that you use the Database unit testing features of Visual Studio 2008 Database Professional aka "Data Dude"

There is a great in-depth blog post about it here, which includes a full step by step walkthrough.

  • I understood. It's a standard solution. I don't think it will help me. Any way I thank you for link and answer.
    – garik
    Commented May 26, 2011 at 12:30

"Should I use a separated database to run unit tests?"

Yes! Effective testing requires control of the environment.

  • Indeed. Without the control, how your expected and received result will match? And if no match, how you decide if difference is benign or a bug? Commented Jul 9, 2014 at 16:21

Try DBFit. I am sure you are going to love it. You can do ATDD for your database the same way you can do it for you application.

The following link will give a lot of information on how to get going including some background on Fitnesse and Dbfit (follow the links in the post).



I posted this on SO a while back. There was a series of articles on closing the loop holes in your databae testing. In the example in the article given He used C# and NUnit. Close those Loopholes - Testing Stored Procedures So this may be of assistance. I have done similar things writing small execution wrappers in C# that do very little but execute the data layer.


I assume you are referring to automated unit tests rather than tests performed by hand. I approach automated unit tests as a means to an end rather than an end in themselves. Some developers get a lot of value out of automated unit tests (I did when I was a developer). Others write great, high-quality code without resorting to automation. You know your own work well enough to know where you are likely to introduce bugs. If you think you are likely to write buggy stored procedures, functions, or triggers, by all means write some unit tests. Otherwise I might test at the persistence layer instead. (Or not at all -- again, you can judge for yourself.)

Another reason to unit test at the persistence layer would be if you expect the database to be queried manually, i.e. by typing in some ad-hoc SQL.

  • I have tested my code by hand. It works in general situations (business logic is big enough). A big issue is a management of changes in database code. I should be aware that old code is still working and ready to external "influences". I want to test business logic, so I am building a business model and my code at the database layer
    – garik
    Commented May 25, 2011 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.