This post might, more appropriately, be named “using tests effectively in SSDT,” but I’m going with the cloning thing because that’s probably what you think the problem is and what led you to read this post. Let’s start with some unit test basics and then get into what MSTest has to offer for database developers.
What’s in a Unit Test Framework
SetUp and TearDown methods execute once before and after each and every test method in a class, respectively. It’s a little bit magical, because the framework handles calling these methods, rather than each test calling them explicitly. In fact, it is probably better to avoid them if you can, for a few reasons:
- You have to look in (up to) 3 places to find out what is happening in a test
- These methods often become a dumping ground for the Arrange portion of a test, containing things that do not pertain to every test in the class
- For the reason above, this can affect test isolation, because the state of one test can be influenced by the needs of another.
- TestFixtureSetup and
These methods run only once before or after the whole test set in the class. Again, it is kind of a confusing behavior because the code is separate from the tests and nothing calls it explicitly. Additionally, there are probably not to many sensible Use cases for either of these methods.
What MSTest has for Database Development
- only use the Test pane, to keep the test elements in one place
- Create your own assert procs to raise an error for failed tests
- Put the whole test in a try..catch block so that errors can be rolled back and reported
The final template should be something like this:
So how do you “clone” a test? At this point, you can just copy paste into a new test. It’s really that simple, because you’ve kept it that simple and not used all those cumbersome windows.
What you get for MSTest is a low barrier to entry. Anyone comfortable with SQL can crank out tests pretty quickly, writing SQL in some window and self-asserting their tests. What you will find missing is a bit of important framework code:
- The ability to Fake a table
- The ability to Fake a view
- The ability to Fake a function
- The ability to Fake a proc
- Asserts of all kinds
- Asserts for error conditions
- The ability to run tests in a single transaction from the test runner and auto rollback
- Code coverage analysis
- Parameterized Tests
In essense, you get a test runner and the means to quickly put your code into a continuous build process. But what was that quote… “You ask for a banana, instead you get a gorilla holding a banana and you get the whole jungle too.” There are loads of ways to hurt yourself with this poor templating. The truth is, hardly anyone writes automated database unit tests, and this is not an area that will receive much attention until more of us do so.
For a more serious approach to database unit testing, check out tSQLt by Sebastian Meine and Dennis Lloyd (which is free). Redgate has made a test runner for this suite that can easily be incorporated into a CI process (the tool costs a nominal amount, but like anything redgate, provides huge value).