Red Gate adds TSQL unit testing to SSMS

Scarlet & Scary - Unit Testing TSQL

"I went through a period of time where I spent a lot of effort figuring out how to set up unit tests for TSQL. It wasn't easy. There are a few tools out there that help, but mostly it involves lots of programming. well, not as much as before. Thanks to the latest Down Tools Week at Red Gate a new utility has been built and released into the wild, SQL Test.
Like a lot of the new tools coming out of Red Gate these days, this one is directly integrated into SSMS, which means you're working where you're comfortable and where you already have lots of tools at your disposal. After the install, when you launch SSMS and get connected, you're prompted to install the tSQLt example database.  
...
The concepts are pretty straight forward. There are a series of CLR commands that you use to configure a test and the test assertions. In between you're calling TSQL, either calls to your structure, queries, or stored procedures. They already have the one things that I always found wanting in database tests, a way to compare tables of results. I also like the ability to create a dummy copy of tables for the tests. It lets you control structures and behaviors so that the tests are more focused. One of the issues I always ran into with the other testing tools is that setting up the tests might require potentially destructive changes to the structure of the database (dropping FKs, etc.) which added lots of time and effort to setting up the tests, making testing more difficult, and therefor, less useful.

Functionally, this is pretty similar to the Visual Studio tests and TSQLUnit tests that I used to use. The primary improvement over the Visual Studio tests is that I'm working in SSMS instead of Visual Studio. The primary improvement over TSQLUnit is the SQL Test interface it self. A lot of the functionality is the same, but having a sweet little tool to manage & run the tests from makes a huge difference. Oh, and don't worry. You can still run these tests directly from TSQL too, so automation has not gone away.
..."

Red Gate - SQL Test

SQL Test is a unit test plug-in for SQL Server Management Studio. 

What can SQL Test do for you?

• Start writing and running unit tests immediately, without a complex, time consuming set-up process.
• Effortlessly view all of your unit tests and results in a convenient dockable panel.
• Easily share unit tests with team members for faster bug fixes and enhancements.

Why use unit testing?

• Discover bugs much earlier in the development cycle.
• With unit testing, continuous integration, Agile Development, and test driven development are finally achievable goals.

...
..."

What is that tSQLt thing?
tSQLt.org

"Welcome to tSQLt – The Database Unit Testing Framework for SQL Server

What is tSQLt?
tSQLt is a database unit testing framework for Microsoft SQL Server. tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions.

Main Features
tSQLt allows you to implement unit tests in T-SQL. This is important as you do not have to switch between various tools to create your code and your unit tests. tSQLt also provides the following features to make it easier to create and manage unit tests:

• Tests are automatically run within transactions – this keeps tests independent and reduces any cleanup work you need
• Tests can be grouped together within a schema – allowing you to organize your tests and use common setup methods
• Output can be generated in plain text or XML – making it easier to integrate with a continuous integration tool
• Provides the ability to fake tables and views, and to create stored procedure spies – allowing you to isolate the code which you are testing

..."

Interesting. If a third party is going to do something right in this space Red Gate is the company that would do it...

Note: When I wrote this post, this was a free beta. Now that it's been released it's no longer free (there's currently a 28 day trial though)