THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Database unit testing is now available for SSDT

Good news was announced yesterday for those that are using SSDT and want to write unit tests, unit testing functionality is now available. The announcement was made on the SSDT team blog in post Available Today: SSDT—December 2012. Here are a few thoughts about this news.

Firstly, there seems to be a general impression that database unit testing was not previously available for SSDT – that’s not entirely true. Database unit testing was most recently delivered in Visual Studio 2010 and any database unit tests written therein work perfectly well against SQL Server databases created using SSDT (why wouldn’t they – its just a database after all). In other words, if you’re running SSDT inside Visual Studio 2010 then you could carry on freely writing database unit tests; some of the tight integration between the two (e.g. right-click on an object in SQL Server Object Explorer and choose to create a unit test) was not there – but I’ve never found that to be a problem. I am currently working on a project that uses SSDT for database development and have been happily running VS2010 database unit tests for a few months now.

All that being said, delivery of database unit testing for SSDT is now with us and that is good news, not least because we now have the ability to create unit tests in VS2012. We also get tight integration with SSDT itself, the like of which I mentioned above. Having now had a look at the new features I was delighted to find that one of my big complaints about database unit testing has been solved. As I reported here on Connect a refactor operation would cause unit test code to get completely mangled. See here the before and after from such an operation:

20110516 Refacoring preview changes

FROM    bi.ProcessMessageLog pml
INNER JOIN bi.[LogMessageType] lmt
ON    pml.[LogMessageTypeId] = lmt.[LogMessageTypeId]
WHERE    pml.[LogMessage] = 'Ski[LogMessageTypeName]of message: IApplicationCanceled'
AND        lmt.[LogMessageType] = 'Warning';

which is obviously not ideal. Thankfully that seems to have been solved with this latest release.

One disappointment about this new release is that the process for running tests as part of a CI build has not changed from the horrendously complicated process required previously. Check out my blog post Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude] for instructions on how to do it. In that blog post I describe it as “fiddly” – I was being kind when I said that!


Published Friday, December 14, 2012 1:47 PM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



sahas said:

I dont see "Create Unit Tests dialog box" with the latest SSDT. I checked SQL->check for updates.. it says "your installation is upto date" ...

December 25, 2012 9:26 PM

nojetlag said:

same here, the VS Integration is a complete mess.

January 31, 2013 11:01 AM

Ian Ceicys said:

Does this work with Visual Studio 2012 Update 2 \ SSDT 2012?

April 25, 2013 10:31 PM

jamiet said:

Hi Ian,

Yes, it does.



April 26, 2013 2:02 AM

Jesse said:

Hi Jamie,

Can I ask how, using SSDT, you are seeding your database with test data prior to running your database unit tests?

In VS2010, there is a setup script option under Test > Edit Test Settings > Local > Setup and Cleanup Scripts and our team typically put a .BAT file here that ran a large set of .sql files to clear test data and then reload it prior to running the tests.

I don't see that option in SSDT 2013 and so I was wondering how you are dealing with it. Is there a way to automate execution of a series of .sql scripts prior to running any unit tests in SSDT?

October 1, 2013 7:39 PM

Leave a Comment


This Blog


Privacy Statement