Visual Studio Database Projects have been available for a few years. They have been used for version control, and data change migrations. One capability I have been reviewing is the ability to have automated unit testing of stored procedures and functions. Microsoft has a two layer approach to database unit tests that is quite ingenious.
Their testing methodology has two layers of implementation. First, you create a wrapper around the object you wish to test, a stored procedure or function. This is made easy to get started, because you right click on the object in Visual Studio, and select create unit test. When you select create, two files are generated on your behalf. The first is a valid SQL script executing your custom function or procedure. The second is a stub of an MS Test unit test in Dot Net code, mine was in C#.
The MS Test code calls the SQL Test enabling them to be run from a central routine already built into Visual Studio.
In the SQL Test you have the ability to define different assertions such as what is the expected return type (Table, Scalar, etc.) from the execution, and even what value may be contained in the returned data. I have a function that converts an Int to a Hex string which I used to define my first database project unit test. The experience was quite simple and pleasant.
Having written my own SQL Unit Test framework for NUnit, I really have an appreciation for what Microsoft has delivered here. I found this implementation to be friendly, easy to understand and learn. My NUnit framework was more powerful in that it would compare entire sets of data. However, set comparison is not always needed to validate the accuracy of a function or stored procedure.
I also liked the ability to integrate transactions and rollback results of execution. This allows you to fully exercise your code without corrupting your database.
You can jump start your testing by integrating the publishing of database migrations with your test suite. This integration of migrations with testing allows you to be sure that when your tests execute against a database, it has the latest code available.
This database testing capability in Visual Studio is something really worth digging into.
Cheers,
Ben