Alexander, here are the answers to the 4 points that you raise:
1. Regarding database slowness.
You touch a good point; yes database slowness is an issue that has to be considered. I would go even further and say that SQL has a lot more limitations when it comes to unit testing. The entire unit test game must be viewed differently when applied to SQL compared with languages like Java / C#. There are a few core principles that remain intact but there are big differences.
Regarding the SP calls to something like Assert.Equals I am going to give more details at point 2.
2. Performance numbers.
I would like to know more about what were those 11 tests you are mentioning.
In my tests 1000 simple test procedures took just a few seconds.
On my machine, after a server restart, the first three runs of 1000 tests (see below) took 5233, 3740 and 3830 miliseconds.
My system is a dual core (AMD 64 5200+ 2.7 GHz) with 2GB RAM memory.
The test I did was meant to give an idea about the overhead that the TST API has. Here is the test:
a. I created a database with a simple function (TFN_DoubleInt) that simply multiplied the input parameter with 2 and returned the resulted value.
b. I added 1000 different test stored procedures. Each looked something like this:
CREATE PROCEDURE SQLTest_PN
DECLARE @ActualValue int
DECLARE @ExpectedValue int
SET @ExpectedValue=2 * N
EXEC TST.Assert.Equals 'SQLTest_PN', @ExpectedValue, @ActualValue
where N is a number between 1 and 1000.
3. Regarding rollback & restoring from a snapshot
At this point I think that the cases where the auto rollback will not be able to perform will be fairly rare in practice. Also, when that happens it is a consistent thing not an error like event. TST does a good job of detecting those cases and there is no risk that it will simply let them slide. In that case the user can with a little work do the clean-up himself in a TEARDOWN. But it all depends on the feedback I will get. I am open to that if it becomes a concern and is requested by enough people.
4. Separating production code from test. Separate schema and or separate database.
Yes, definitely you can do any of those. You can isolate all the test procedures in a separate database if you wish. Also you can place them in the production DB but isolate them in their own schema.