Last week I published a post titled "Why I hate stored procedures" where I talked about -- well -- my intense skepticism of stored procedures in the context of modern .NET development. That post got an amazing amount of engagement and comments. Some people agreed with me and -- uhhh -- some people didn't agree.

One of the big themes on the 'disagree' side was that if I'm not using stored procedures, then I'm going to be dealing with the ugly and sometimes non-performant SQL from EF Core. That inspired a post on why EF Core's ugly generated SQL is a distraction. (Disclaimer: I don't think that EF Core's SQL is especially ugly and even if it were, I'm fine with it.)

The next big objection that I got was about testability. Specifically about my assertion that you can't unit test a stored procedure. But you know what? You pretty much can't unit test any kind of SQL query. At all. The end.

The testability issue isn't a stored procedure problem -- it's a relational database problem. The non-testability problem applies to Dapper, raw SQL strings, EF Core, NHibernate, LINQ-to-SQL (remember that one?) and stored procedures equally. If you make a call to a database, you've got this testability problem. It's fundamental: database 'stuff' is hard to test regardless of how the SQL gets there.

But stored procedures make it the worst because stored procedures encourage you to put your business logic in the one place you can't test it. In that stored proc case, you're not merely unable to test query logic or CRUD logic, you're putting important business logic in there. That's business logic that is now really hard to unit test. That affects your 'shift-left' quality efforts. It affects builds. It affects releases. Heck! It affects the setup of your local development machines.

Unit Test != Integration Test

There's a very important distinction that I want to make. Unit tests and integration tests are not the same thing. Integration testing of stuff in SQL Server (or a relational database) is entirely doable...it's just a pain. Unit testing -- unless I'm missing something really important -- is completely impossible.

So I'll say it again: unit tests and integration tests are not the same thing. They have different goals and different philosophies.

Unit Tests vs. Integration Tests

"Alright, Mr. Nitpicky von Smartypants. What's the difference?"

A unit test exercises a small piece of functionality in isolation. No dependencies. Just the bit of logic that you're looking to exercise and validate. That allows you to have a super-focused, easy-to-write, quick to run test. But that means no database. No file system. No network. No external dependencies. It runs in milliseconds. You can run a thousand of them during a build and nobody notices. The boundaries are drawn tight, the inputs are controlled, and if it fails, you know exactly what broke. You know exactly what that test did, exactly what was in-scope for that test, and (in reverse) exactly was wasn't in scope for that test.

An integration test exercises code that has dependencies. You're testing something that has integration points. These tests often interact with external systems — quite often, that's a database.

So unit tests need next to nothing to run. Integration tests usually need quite a lot in order to run. It requires infrastructure to be running (aka SQL Server). In the case of database tests, it requires user accounts and deployed schemas. It requires test data to be set up. It requires cleanup after the test runs. It's slow relative to a unit test. It's flaky relative to a unit test. And when it fails, you're not always sure if the problem is your code, your data, or your infrastructure.

Integration tests require real effort to write, run, and maintain. That effort results in something called 'test friction'. Test friction describes all the stuff that you have to do in order to make a test case happen. Basically, all the stuff you've got to do and got to set up and got to tweak and debug in order to just get your test case to run.

Test friction results in discussions of ROI (bang for your buck) on writing these tests. Is it a monster to write? Are we getting real value out of this test? No? Not really? Not sure? Well that pretty quickly turns into tests that get abandoned -- or tests that just don't get written in the first place.

Every single thing in your app that doesn't have an automated test requires either a human to test it or for you to accept that it hasn't been tested before going to production. It's a calculated risk. Require a human tester → slower release and feedback cycle. Skip the testing → potentially more defects in production.

Both types of tests are valuable. I write integration tests ALL THE TIME. But they serve different purposes, they have different economics, and the ratio matters a lot. I often have hundreds of unit tests. Integration tests? Maybe a couple dozen. Way less.

Unit Testing and Stored Procedures

Here's the thing: you cannot unit test a stored procedure. Period. By definition. The moment you need a running SQL Server instance to execute your test, it's an integration test. You've crossed the line. Your test now depends on infrastructure, on state, on setup, on cleanup. It's no longer fast, isolated, or repeatable in the way unit tests need to be.

(Yes, there are T-SQL testing frameworks out there that label themselves "unit testing" frameworks. They still require a running, configured SQL Server instance with your schema deployed. That's an integration test by any standard definition.)

And here's the broadening that matters: this is also true for any SQL. Dapper? Integration test. Raw SQL string in your repository? Integration test. EF Core LINQ query that you want to verify actually generates the right SQL and returns the right data? Integration test. The moment you need a database, you're in integration test territory.

The database is inherently hostile to unit testing. That's not a stored procedure problem. That's a database problem. But stored procedures make it catastrophically worse because they put your business logic in the database. If the only way to run your business rules is to execute a stored procedure on a live database, then the only way to test your business rules is an integration test. Every time. For everything. (Even if you use a container.)

The Setup/Teardown Nightmare

Ok so let's say you accept that testing stored procedures means integration testing and you decide to do it anyway. How hard can it be?

Let’s walk through the test friction. All those referential integrity rules that you’re using to guard your data quality, they’re now a liability. It turns into a test friction factory.

The Revenge of Referential Integrity

You've got a stored procedure that inserts an order. The Order table has a foreign key to Customer. Customer has a foreign key to Address. Address has a lookup to State. The order has OrderLineItems, which reference Products, which reference ProductCategories. Some of those tables have audit columns that default to GETDATE() and SYSTEM_USER.

To test your stored procedure or your query, you not only need to deploy the schema but you also you need to set up all of that data. You need a State row. An Address row. A Customer row. Product Categories. Products. And THEN you can insert your Order and your line items. Your test setup code is now 40 lines long and touches six tables — and you haven't even gotten to the assertion yet.

Now multiply that by every test. You’re very likely to need to test the same code using different data scenarios. It’s virtually guaranteed that each test in your suite needs slightly different data. Some tests need conflicting data. Every test needs to clean up after itself so the next test starts clean. And for query tests, you’ll probably want to add data that shouldn’t come back in a SELECT. Data, data, and more data…that has to be added and cleaned up…for each test. And if you can’t guarantee what data is there, you probably don’t have a good test.

Now here's where it gets really fun. Let's say you've got a self-referencing table. Person with a ParentId that points back to Person.Id. Maybe it's an org chart. Maybe it's a category tree. Doesn't matter. To clean up after your test, you can't just DELETE FROM Person WHERE Id = @TestId because the child rows have foreign key references to the parent rows. You have to delete bottom-up, which means you need to know the hierarchy, which means you're writing recursive cleanup logic. Or you disable the foreign key constraints, truncate the table, and re-enable them.

And the moment you disable foreign key constraints for test cleanup, your test infrastructure is actively lying to you about whether your constraints work. You're testing your business logic in an environment where the referential integrity rules are turned off. Some T-SQL testing frameworks actually make this their feature — replacing your real tables with constraint-free copies so setup is easier. Your test passed. Great. Does the code actually work in production where the constraints are enforced? Who knows! You turned them off!

That's not testing. That's theater.

It’s also a WHOLE lot of work for that integration test. And these tests tend to be buggy because there are so many moving parts and little fiddly bits that have to be created and deleted in just the right way.

The Test Setup Code Becomes Its Own Project

This is the part nobody talks about: the test infrastructure.

When you're integration-testing stored procedures at any kind of scale, your test setup code starts to rival your production code in complexity. You need helper methods to insert test data into every table. You need cleanup routines. You need connection management. You need transaction scoping so tests can roll back. You need seed data scripts that run before the test suite. You need to handle the fact that some tables have identity columns and some don't. You need to deal with computed columns and triggers that fire during your setup and mess with your expected state.

And every time the schema changes — every time someone adds a NOT NULL column or changes a foreign key or renames something — your test setup code breaks. Not because your tests were wrong. Because the infrastructure for your tests was wrong. Now you're debugging test infrastructure instead of debugging your application.

If the test setups are so complicated that you need a couple PhDs and the gift of precognition, then those things are going to get ignored. And that brings us to the quiet part.

How Testing Cultures Fade Away

Nobody holds a meeting to announce "we've decided to stop testing." That's not how it happens. Test friction doesn't kill testing all at once. It kills it slowly. It’s hard. It’s a pain. It’s buggy. It’s tedious. No one wants to touch it because if you breathe on it funny, everything breaks.

Here's the process. A developer writes an integration test. It works on their machine. It fails in CI because the test database doesn't have the right seed data. They spend an hour debugging it. They fix it. It passes. Two weeks later someone else's test changes the data their test depends on and it starts failing again. They mark it [Ignore] with a comment that says // TODO: fix this. It stays ignored for six months. Then a year.

Meanwhile another developer is writing a new feature. They look at the existing test suite. Half the tests are marked [Ignore]. A quarter of them are failing for infrastructure reasons unrelated to the actual code. The suite takes 12 minutes to run because it's standing up and tearing down databases. They think: "I don't have time for this. I'll just test it manually and ship it."

That’s a LOT of technical debt that’s sitting in your test suite. It’s not even tech debt in your feature code. It’s the stuff for validating the feature code. That’s the kind of tech debt that never EVER gets paid off.

And the testing culture quietly, without ceremony or fanfare, just... disappears.

Nobody made a decision. Nobody signed off on it. It just eroded. The friction was so high and the feedback was so unreliable that people stopped trusting the tests, stopped writing new ones, and eventually stopped running the existing ones. The test project is still in the solution. It still builds. Nobody runs it.

I've seen this pattern at dozens of companies. And the root cause is almost always the same: the test friction was so high that developers rationally chose not to do it. They weren't lazy. They were responding to incentives. The architecture punished testing and rewarded just shipping it.

“We’ll catch it in QA.” Yep. And how’s that ‘shift-left’ thing going for you? Not great.

False Passes Are Worse Than No Tests

Here's a fun one. Let's say your integration test passes. Congratulations. What does that mean?

It means the test passed given the current state of the test database. But is that state representative of production? Did another test run first and leave behind data that made your test pass when it should have failed? Is there a trigger on one of the tables that fires in production but got disabled in the test database? Is there an index that exists in production that changes the query behavior?

If you can't be sure the test data is clean, you can't be sure the test result is real. And a false pass — a test that tells you everything's fine when it isn't — is worse than not having a test at all. A test that doesn't exist doesn't lie to you. A false pass actively deceives you into shipping broken code with confidence.

The CI/CD Dealbreaker

Here's where this stops being a preference and starts being an architectural constraint. So far we’ve assumed that you have your database schema completely under source control and in a format that can be deployed with minimal effort. Do you even have that? Let’s assume that you do.

Modern software delivery depends on continuous integration and continuous deployment. CI/CD pipelines need to give you fast feedback — ideally within a few minutes of a commit. If the pipeline takes 45 minutes, developers stop paying attention to it. If it takes 2 hours, it's useless.

Fast feedback requires fast tests. Fast tests means unit tests. A well-structured unit test suite runs in seconds, maybe a minute or two for a big one. An integration test suite that's hitting a database can easily take 10, 20, 30 minutes. And it's flaky. And it requires infrastructure. And it fails for reasons that have nothing to do with the code someone just committed.

Now follow the logic:

Continuous deployment requires fast feedback. Fast feedback requires fast tests. Fast tests means unit tests. Stored procedures can only be tested with integration tests. Therefore stored procedures are architecturally incompatible with continuous deployment. And if you only run your database integration tests during your nightly build, for example, there's a real chance that the team has gone a long way in the wrong direction before they get that “it’s borkened” feedback.

This isn't a preference. This isn't me being dramatic. It's a real official Eliahu Goldratt Constraint™ constraint. If the bulk of your business logic is in stored procedures, you have structurally prevented yourself from building a fast, reliable CI/CD pipeline. You can have stored procedure-driven architecture or you can have fast, reliable continuous deployment. Pick one. You can't have both.

I've seen this play out. The company has 2,500 stored procedures. Some of them are over a thousand lines of code. The business logic is entombed in T-SQL that nobody can fully test, nobody can confidently refactor, and nobody entirely understands — because the developer who wrote the gnarly ones left the company in 2018.

The testing pyramid is inverted. Thousands of slow UI tests at the top. Minimal unit tests at the bottom. A two-week manual regression cycle before every release because nobody trusts the automated tests.

What kind of pizza do you order on deployment night? 🍕

(If you're ordering pizza on deployment night because deploys are scary and take all evening, that's not a team culture problem. That's an architecture problem. And it probably started with a decision about where to put your business logic.)

So What Do You Do About It?

So far in this series, I've been heavy on the diagnosis and light on the prescription. That's about to change.

Next I'm going to talk about what I actually do about all of this. How I structure my applications so that the important logic is testable, the database stays dumb, and the test friction stays super-low. I'm going to talk about layered defense, calculated risk, and — fair warning — I'm going to say something about my own testing practices that will probably make some of you very uncomfortable.

Stay tuned.

-Ben