I wrote my first tSQLt test in late 2012. Three years later, I've used it across multiple projects, introduced it to teams that had never tested SQL before, and hit most of the sharp edges the framework has. It's time for an honest assessment.
What tSQLt Got Right
The transaction isolation model is the right design. Rolling back every test eliminates test interdependency, removes cleanup code, and makes the suite idempotent. Every other approach I've tried to SQL test isolation — explicit truncates, test databases you reset between runs — is more fragile and more work. This single design decision makes tSQLt the right choice for SQL Server unit testing.
FakeTable is the right abstraction. Replacing a table with an empty schema-compatible stub gives you complete control over test data without touching real tables. The constraint removal by default is correct — you're testing procedure logic, not database integrity enforcement. Most developers internalize the pattern quickly and it becomes second nature within a few sessions.
AssertEqualsTable failure output is genuinely useful. A diff showing exactly which rows disagreed and on which columns is what you need when debugging a failing data transformation test. I've worked with frameworks that give you "expected 3 rows, got 4" and nothing else. tSQLt tells you which row and which column. That's the difference between a test that helps you fix the bug and one that just confirms you have one.
Tests as stored procedures is the right model for SQL developers. Tests live in the database alongside the objects they test. They're first-class SQL objects. They're in source control via an SSDT project. They're runnable from SSMS without installing anything. The learning curve for a T-SQL developer is close to zero.
What tSQLt Gets Wrong — or Makes Hard
The CLR dependency is a real barrier. Requiring CLR enabled and TRUSTWORTHY ON blocks adoption in shops with strict security postures where CLR is disabled by policy. This isn't a workaround situation — if CLR is off, tSQLt is unavailable, full stop. It's the most common reason teams evaluate tSQLt and don't adopt it.
Procedures with many side effects produce complex tests. If a procedure writes to three tables and returns a result set, you need FakeTable on all three tables and assertions on all three. At some point the test is nearly as complex as the procedure. The usual answer is "your procedure is doing too many things" — but that's a refactoring conversation the tests force, not a framework failure. The tests are right to be painful; the pain is real information.
Integration tests are out of scope. tSQLt is a unit testing framework. It doesn't help you verify that your ETL pipeline runs correctly against real data volumes, or that your indexes perform well enough under load. Those need different tools and different runs. Teams new to testing sometimes expect tSQLt to cover everything and are confused when it doesn't. It covers unit behavior; integration is your problem to solve separately.
Schema evolution creates maintenance overhead. FakeTable rebuilds the fake from the current schema, so adding a column doesn't break the FakeTable call. But your test data inserts may need updating. FakeFunction calls need updating when you rename a function. This is proportional to how much your schema moves — manageable as discipline, not free.
Net Assessment
If CLR is available in your environment, tSQLt is the right choice for SQL Server unit testing. The alternatives — ad-hoc test scripts, manual verification in SSMS, "just run it in dev and see" — are strictly worse for team confidence and long-term maintainability.
The test coverage worth building: every stored procedure with branching logic, every trigger, every function used in critical calculations. Skip testing simple CRUD procedures where the logic is just "INSERT this row" — the value isn't there. Focus coverage where bugs actually hide.
Three years in, tSQLt is on every project I touch where CLR is available. I've caught regressions that would have reached production. I've refactored stored procedures I wouldn't have touched without a test safety net. That's the return on the investment. As always, I'm here to help.