This has had me scratching my head a bit, as I can't see how this is going to help tackle the insidious dynamic errors, such as deadlocking, that happen in a working database. Unit testing of TSQL code is something we do already- nothing radical here: in fact it is difficult, almost impossible, to write good TSQL without unit-testing everything as you go. It could be that the title of the article led me to expect a more general exposition on Unit Testing of TSQL, rather than a description of one particular test harness.
I liked the approach of doing everything in TSQL rather than using a C# test-harness, though I'm not yet sure how far one can get with this technique.
The most telling sentence in this article for me is 'In my opinion, it is much more difficult to refactor SQL code than it is to refactor .NET or Java code because of the lack of tooling (i.e. ReSharper for Visual Studio, etc) and the lack of object-oriented design of code modules within T-SQL code.'. For an experienced database developer, this is a strange sentiment. It is as odd as saying that C# is hard to refactor because it isn't relational!
For a good grounding in the techniques of Unit testing of databases, including the testing of their dynamic behaviour, I would recommend Alex Kuznetsov's series on Simple-Talk.