For what it’s worth, we implement tests in the client language (C# in our case) and just call the stored procedures/views (in SQL Server) as any client would. M

For what it’s worth, we implement tests in the client language (C# in our case) and just call the stored procedures/views (in SQL Server) as any client would. Most tests are written so that they rollback after execution, and to tolerate any residual data that might have been left after the tests that must commit. A very small number of tests require a clean database, which we accomplish by traversing INFORMATION_SCHEMA and just emptying all the tables (we also temporarily disable constraints and triggers). These tests are just normal “unit” tests from the perspective of Visual Studio, which brings one enormous benefit: you can put a breakpoint in T-SQL code and just right-click on the test and choose Debug, and the breakpoint will be hit! That’s possible because Visual Studio debugger can switch from C# to T-SQL seamlessly. This is a fantastic way to debug in exactly the right test case which exposes the bug or behavior that you want to test.