For the 99th T-SQL Tuesday (that’s over 8 years of blog parties), Aaron Bertrand invites us to write about our #sqlibirum (credit to Drew Furgiuele and Melissa for the term)– our passions outside of SQL Server and technical community.

These days, aside from my family and technology, I’m putting most of my energy into learning more about the privileges I enjoy in society and how to dismantle the systems which provide them. Twitter has exposed me to ideas and realities which I’ve never had to face in the past, and it’s frustrating to learn about how stacked the deck is against so many people. That said, except as those issues intersect with technical communities, I’m not ready for this blog to become overtly political. So I’m going to take Aaron’s escape pod and talk about a T-SQL bad habit.

The practice I’ll discuss is not strictly about T-SQL, and it’s not on Aaron’s comprehensive list. Ask your average developer and they’d probably say it’s a bad idea to put your business logic in the database. But, as with everything in technology, “it depends”. Life is nuance.

If your application involves complex numerical analysis or graphical manipulation, then this advice is not for you. If you’re working on a specialized, mobile-only app, this isn’t relevant. I’m talking about your typical “line of business” application with hundreds or thousands of users– not millions– which collects information, makes decisions, and guides users through tasks. I’m assuming the application will always be connected to some back end database which can think for itself (i.e. not NoSQL). In those cases, not only may you put some business logic in your database, but I think you should put it all there. And here’s why.

First, perhaps to a fault, I abhor duplicated logic. It’s not just wasteful; it’s damaging. It’s only a matter of time before rules encoded in multiple places diverge. Someday, someone will adjust one function but not the other, and the behavior will never be the same again. And that’s assuming you got them both right to begin with. So I empathize with traditional programmers who argue that “leaking” any business logic into the database creates these latent defects. But whether their fear is about mere disorganization– scattering rules among different application layers– or actual logic duplication, they can be eliminated by putting all of it in the same place, the database.

The purported purpose of a database is to persist the important information needed by all aspects of the ecosystem– mobile, web, native, and API. But in addition to the data, they all need the rules. The typical “best practice” is for all of them to operate through some sort of service layer (perhaps the API). But guess who’s not invited to that party– the database itself. Everything can talk to a database, but it can’t talk to anything else. If it needs any logic, it can’t (reasonably, directly) access any sort of an API. So if your database code will ever need to understand a business rule, it must exist only there or be duplicated. And if you’re going to put any of it there, you might as well keep it all together in one place.

But could you keep it all out of the database? Surely you don’t need it there. Perhaps, but as complexity and rowcounts increase, performance requirements will eventually force a compromise. For example, let’s say you want a list of your late orders. That’s a business rule– maybe it’s 30 days after the order date without some sort of completion status. Your ORM can cobble that together for you– but it’s already hitting your orders table and your giant order status events table; hopefully it gets that JOIN right. Now throw in a different concept of late for each customer (some are more equal than others), and different services or products are going to take a different amount of time, right? So now you’ve collected 4 or so tables and you’re rolling the dice on a generated query which still performs well. And even if your tools happen to generate SQL which isn’t naturally inefficient, you can’t take advantage of indexes on persisted calculated columns (e.g. OrderIsLate) unless that rule’s logic lives inside T-SQL somewhere.

Even if you don’t care about performance, keeping your logic outside of your database means it can’t do your work for you. Let’s say you want to apply a discount to all of these late orders. Your normal user application probably won’t be doing that; it responds to user input not chron jobs. You could build a separate, helper app which used your service layer to gather these orders, then looped over them to apply the discount. And then make sure you assign someone or some process to watch over that application to ensure it’s always running at 1 AM when the discounts need to be applied. It’s a pity you can’t just make a database job to find and mass-update your orders with the discount, since it can’t access the service layer to find them.

I’m confident that with a tool like SQL Server, you will eventually want it to do some thinking, not just storing. If you resist, it will still happen, but in dribs and drabs. Managed poorly, you will have a mess. Instead, embrace it. Dive right in. What can go wrong?

What about vendor lock-in, you protest? From my observation, despite the lip service paid to wanting data storage portability, folks generally don’t change their database engine. Once you start using SQL Server, you keep using it for decades. On the flip side, new JavaScript frameworks come out every day. Why not put your crown jewels– the rules and processes that make your business unique– in a platform which changes over generations instead of over lunch?

What about testing, you worry? You can test T-SQL! In fact, done right, you can test more, faster than traditional unit testing. For example, if you separate your business logic into inline user-defined, deterministic functions (e.g. an IsOrderLate function which takes the order date, customer terms, included products, etc.), you can quickly and easily pass it every conceivable combination of dates and settings and compare its output to before you tinkered with it. With a single query, you could generate a spreadsheet of how your logic will behave every possible scenario and get business sign off.

But is it “web scale”? Ok, I’ll admit it. SQL Server can get really expensive. Putting your business rules in T-SQL means you will increase your CPU usage, and Microsoft charges by the core. Scaling a web farm for your service layer is a lot cheaper, especially using open source software (at least form a purely monetary expenditure perspective). I suppose you’ll have to pay for that with the money you didn’t spend porting your logic from one framework or language to another as each fad faded.

There are more neutral and thorough treatments of this subject, but hopefully I’ve cracked your natural aversions and you can approach this taboo with an open mind. And if I have, maybe you’re ready for the next step– putting even display logic in your database, if you have native applications. Why recompile and redistribute your executable when you can change its look and behavior with some feature flags?

When you have a hammer like SQL Server, everything is a nail.