Unit testing SQL Server

Finally had an Ahha! moment today with the implementing of SQL Server unit tests to start off the next change…

OK, so you need to have a sql project in place (SSDT), but once you have that, then you have the means to start testing you DDL objects (for us, stored procs to begin with).  In the Sql Server Explorer window, you get to see both the databases and the projects.  It is against the project (not the database) that you can add a test to.  For this change, it was only about changing some reference data that we maintain under source control using MERGE scripts, but we are doing TDD and we want to document the fact that we need to make this change.

The reference data is accessed via a “GetAll()” stored proc that becomes the target of our test.  The tests I wrote were to:

1. ensure that we would fetch the expected number of rows
2. ensure that the row that I was changing did in fact have the changes that I was expecting

For the first test, there is the built-in “rowCount” test and for the second test there is the built-in “checkSum” test.  The tooling means that there is NO code that needs to be written.  It is so easy and EVERY database change from here on in will need a corresponding SSDT test.

Here’s the link that kicked it off for me: Good intro to SSDT database unit testing
If your new to SSDT then this MS Ignite presentation is for you:  Hannah (Xero) on SSDT at MS Ignite

Oh, were running VS 2013 Premium and SQL Server 2012.  I think I have the most up-to-date version of the SSDT installed.