Personally, I am a huge fan of the DbFit tool (hence: http://www.sqlservercentral.com/articles/Testing/64636/). I like it because it not only provides repeatable, automated testing (both from GUI and from command-line), but it is a very flexible framework that allows for most situations. Granted, to be clear we are talking about functional testing, not performance testing; Unit testing does not deal with performance. But compared to the *Unit (or would that be %Unit ;-)) frameworks, DbFit does not require any modifications to your DB in order to test. You do not need to create any test procs nor any setup / tear-down procs. All of that is contained within the test itself which by default is a transaction that gets rolled-back at the end (although you are allowed to break out of the automatic transaction for more control). We use DbFit quite a bit where I work as well as regular FitNesse for our Continuous Integration and it works quite well. And again, our database (both schema and procs) has no knowledge of the DbFit tests even existing; they are completely isolated. Just yesterday I created a DbFit test page that connected to one database and ran a query while storing the result set in a variable, and then on the same page it connected to another instance and ran another query against our data warehouse and stored that result set and then at the end of the test I compared both result sets to make sure that the source data was being moved to the warehouse server and aggregated correctly. This was quite easy to do and exists entirely in a single DbFit wiki page. I am even able to create temp table and temp procs in the tests for the purpose of the test that simply go away at the end of the test.

And the framework is so easy to use that in most cases our QA folks create the DbFit tests themselves since it requires just basic SQL knowledge. They have set up some elaborate tests that pass variables from a top-level page down to sub-pages, etc.