Is SQL a good place for business logic?

<TL;DR> No, it isn’t. While SQL is a Turing-complete language and we can use it to encode any business logic we want, placing business (domain) logic into SQL leads to a less maintainable solution comparing to one that uses an OO or functional language. Because of that, I advocate to limit the use of SQL to read-only queries (which can potentially contain business logic, that’s fine) and simple CRUD statements where possible.</TL;DR>

The dispute regarding whether or not one should place business logic in SQL takes place for as long as I can remember. It’s much less active these days, but still exists, so let’s elaborate on the details here.

What is wrong with placing business logic into SQL?

Basically, the SQL itself.

Let’s take an example. Let’s say we have the following model in our application:

SQL business logic: domain model

SQL business logic: domain model

In other words, there are two entities: Student and Teacher, which share some common data (the Name field) and also have some unique traits (AverageMark and Salary).

Now, let’s say we need to create a method that returns a description for a person. Here’s a table we can create to represent this model:



       PersonID int NOT NULL PRIMARY KEY,

       Name nvarchar(200) NOT NULL,

       Type int NOT NULL, — 1 for students, 2 for teachers

       AverageMark float NULL,

       Salary decimal(18, 2) NULL



And here’s how this method can be implemented in SQL:

CREATE PROCEDURE dbo.sp_GetPersonDescription

  @PersonID int


DECLARE @Name nvarchar(200)

DECLARE @Type int

DECLARE @AverageMark float

DECLARE @Salary decimal(18, 2)


SELECT @Name = Name,

       @Type = [Type],

       @AverageMark = AverageMark,

       @Salary = Salary

FROM dbo.Person

WHERE PersonID = @PersonID


IF @Type = 1

  SELECT ‘Student ‘ + @Name + ‘ with average mark ‘ + CAST (@AverageMark as varchar(10))


  SELECT ‘Teacher ‘ + @Name + ‘ with salary ‘ + CAST (@Salary as varchar(10))


— Usages:

EXEC sp_GetPersonDescription 1 — Returns: Student John with average mark 95

EXEC sp_GetPersonDescription 2 — Returns: Teacher Jack with salary 60000.00


Now, compare it to C#:

public abstract class Person


    public string Name { get; set; }

    public abstract string GetDescription();



public class Student : Person


    public double AverageMark { get; set; }


    public override string GetDescription() =>

        “Student “ + Name + ” with average mark “ + AverageMark;



public class Teacher : Person


    public decimal Salary { get; set; }


    public override string GetDescription() =>

        “Teacher “ + Name + ” with salary “ + Salary;


Which one is easier to understand? I bet your answer is not “the first one” 🙂

And that is just a simple method returning some description, nothing more. Imagine how hard it would be to encode this business rule using SQL: “If the person is a teacher and their class is the best in the school, then increase their salary by 20% and assign 2 other teachers for mentorship”.

I’m not saying it’s impossible (it surely is!), but the level of effort required to implement and, more importantly, to support and maintain such a solution is much higher than it is using regular OO (or FP) languages.

The reason for that is simple: SQL is not designed for the work with individual rows. It becomes more apparent if you take into account the fact that the IF statement wasn’t even in the standard initially. It was added later, starting from SQL-99.


There are basically two ways to work with data: OLTP and OLAP. OLTP (online transaction processing) stands for processing individual transactions which usually involve little amount of data. This kind of processing aims at high throughput and low latency for each operation.

An example here might be a typical web application. When you perform an action (say, add an item to the shopping cart), the system processes your request by creating a new product line and, perhaps, logging your action somehow.

On the contrary, OLAP (online analytical processing) is an approach for operating relatively large groups of data. A good example here is a background job creating analysis reports. A search method looking for data given particular criteria is another one (although it’s not a “true OLAP operation” per se).

As you might guessed, SQL is about OLAP and not about OLTP. SQL is the language optimized for operating sets of data.

SQL is a horrible choice when it comes to encoding the business rules of your system. It isn’t flexible enough and we can’t rely on it to express any more or less complex domain model because it lacks the ability to create proper abstractions.

And that is the single most important reason why we shouldn’t use it as a place for business logic.

It’s not about the lack of tooling or support for modern development practices such as versioning control, unit testing and so on. There are quite a few products on the market that allow us to do that. It’s all about the inability to build a simple and expressive domain model. OO (as well as functional) design gives us much more opportunities for that than the procedural design SQL leave us with.

Transaction Script design pattern is all we have with SQL. It works when the application you are developing is simple enough, but blows up right after it reaches some (pretty low) complexity mark.

Ideal use cases for the SQL language

Ideally, we should limit the use of SQL to these two scenarios:

  • Simple select/insert/update/delete (CRUD) queries with no business logic in them
  • Read-only queries with some business logic

Simple CRUD operations mean we should treat our database as a dump bag of data and use SQL just to fetch this data into the memory and save it back to the DB. Data processing should occur in the more suitable place – our application code:

Ideal use case for SQL

Ideal use case for SQL

The other use case for SQL is read-only SELECT queries. Even with the advent of LINQ providers in C# and type providers in F#, it is sometimes hard to write a query of comparable performance in the application code. In such situations, we have to fall down to the ground and use plain old SQL.

It’s not usually a problem because read-only queries don’t mutate data in the database and thus add up little maintenance overhead. Also, this practice perfectly aligns with the CQRS principles. That is the use of separate read and write models for performance reasons.

Not so ideal use cases for the SQL language

A not so ideal use case for SQL is write queries containing some business logic inside. As we discussed earlier, it’s a good practice to avoid such queries but the truth is, it’s not always possible.

For example, we might have a requirement to disable the accounts of all our clients if they pass the due for their payment plan. In this case, it would be much easier to execute a single UPDATE statement for all the clients at once, without loading them into the memory and processing one by one.

My advice in such situation would be: do use such a statement where appropriate, but try to limit their presence and simplify business rules encoded in them as much as possible.


Let’s summarize:

  • SQL is a bad place to keep the business logic in
  • Try to limit the use of SQL in such a way that:
    • SQL queries are simple CRUD queries with no business logic in them, or
    • SQL queries don’t mutate data in the database

Related articles


  • Greg Jorgensen

    Not a great example, and your Person table is not normalized. Building up a description to display is not even what I would call business logic. Some business logic naturally belongs in the database, some doesn’t. Limiting the RDBMS (not SQL) to CRUD operations throws away too much. What you are describing is the opposite of 40 years of enterprise-scale experience with RDBMSs.

    Think about a database that supports multiple applications written in different languages at different times by different teams. The problems with implementing business logic at the application level are more obvious then — violation of the DRY principle, for one.

    • Vladimir Khorikov

      I would argue the necessity to have a single database that is used by multiple applications in the first place. Years showed that the best way to deal with the storage is when every application has a database which it owns completely. Otherwise, you end up with “cementing” your DB structure, moving logic from the application layer to SQL, and other things that decrease the ability to maintain your solution in the long run.

      and your Person table is not normalized

      I used one of the three strategies for mapping a class hierarchy to a table structure. It’s the best choice among these 3 options taking into account performance and simplicity.

      • Greg Jorgensen

        Your experience doesn’t match mine. At large scale trying to coordinate multiple application databases and maintain consistency and agreement across them turns into a big headache. For small applications like web sites that can work.

        If you start with OO design and derive the database schema a from that maybe a solution like your example follows. I would start by modeling the data and legal operations in the database and make the application code conform. In a real business the data is much more valuable than application code, often pre-dates OOP, and will be valuable when the application programmers have moved on to the next paradigm.

        • Vladimir Khorikov

          I agree, my experience is completely opposite to yours.

          BTW, it’s not that I don’t understand your point of view. I myself worked as a database developer for a long time and I can see where your points come from. The discussion we are having now is a separate big topic of data-centric vs model-centric approaches to software development.

          My personal experience showed that the model-centric approach scales better for large projects. I’d also like to mention the book that helped me with this realization:

          • David Raab

            I also like the model-centric more, but i also can understand Greg comments. But it also can be that i lack experience for Greg use cases.

            At least what i want to add. In the past i had one “data store” that in the end should be used by multiple applications. But i ended up creating a REST service. So application and database created one unit and everything goes through the application. It made writing multiple applications with the same data possible. And that was also needed, but no “domain model” in the database was needed. Instead it was easier to do in the code.

            Maybe someone well say creating a REST service is too much overhead, for example someone want to create multiple applications running on the same system. But at least my experience was that we needed multiple applications on different servers. So creating a REST service was not really an overhead as we anyway needed the ability.

            If performance matters someone can also can create other interfaces or its own binary protocol or whatever, but overall it wouldn’t change much that a model and a database is combined like you explained. And multiple applications were still possible.

            But even on a local machine you could re-use the domain model if it is written as its own library. So every application that has to talk to the database uses the library and never directly talks to SQL on its own.

  • way0utwest

    I think your TL;DR is a bit short in that you finish by noting that some business logic makes sense in the database. I’d agree with you at the end, but not the beginning.

    I think your “business logic” isn’t a good example. If someone is considering this display of text with stored data to be “logic”, I think we have a bigger problem.

    What I might look at differently is the need to determine if our business logic to recalculate an average grade should be in the app or database. I would think it depends in that if the data is stored, and we add (or change) a row, then we should perhaps make a call to a method in the database that recalculates the average, since this is an operation on multiple rows. As you noted, SQL is designed for multiple rows, not singleltons.

    However if we are attempting to determine if a student should be able to register for a class since they might have passed or failed it previously, perhaps the application should have that logic since it might need to query and have supporting data for the application.

    If we are checking a teacher’s schedule and looking for an open slot where they could teach a class, then maybe that is in the app, or maybe a well written query is best at determining slots. I think it would depend on the flexibility required by the system. Either the application or the database might do a good job of containing the logic.

    In my mind, the database is often a place where we have limited resources. We can usually scale out clients (or middle tiers) better than a database. If we can use resources efficiently, we are dealing with multiple rows, and we aren’t choosing logic that needs high scale, then the database is a good place for logic. This can be especially true is the logic uses other data stored in the database to build the logic. I acknowledge that “high scale” isn’t defined, and whatever definition you determine may change over time.

    However if the logic is something that doesn’t overload the network with data transfer or require N+1 trips, and involves an algorithm that isn’t easily applied to sets, then the application makes lots of sense for holding logic.

    In either case, I think the craftsman is the one that must decide and
    justify their decision. However I wouldn’t automatically think that logic should or shouldn’t be in the database since many database platforms have implemented very efficient and strong processing capabilities inside of them. What shouldn’t happen is misuse, or the inability to move logic as needed.

    • Vladimir Khorikov

      Fair points. Indeed, the example is probably not the best one, although I think it serves well as a simple demonstration of the differences between the approaches.

    • youzer

      I’m actually contemplating this very issue.

      From the perspective of a legacy database, a table can implement some very basic business logic in the form of constraints, but a client should also mirror those constraints as well to prevent an error when writing to the database.

      So, to me, it seems that having similar logic in the front-end and back-end is necessary even if they duplicate the same effect.

      I suppose what I’m suggesting is that, the logic necessary to complete a task should be delegated to the layer that needs it.

      If the database requires logic to perform an operation, the logic should reside as close to the database layer as possible.

      If the client requires logic to perform an operation, the logic should reside as close to the client layer as possible.

  • Anders Baumann

    Hi Vladimir.

    Interesting read. As always.

    In the beginning of the article you refer to two other articles about the use of SQL. Those are also interesting reads. I would like to hear your opinion on Rob Conery’s view on ORMs? And what about the “Running totals” example by Lukas Eder? To me that sounds like a good place to use SQL for business logic. Would that example be covered by your “SQL queries don’t mutate data in the database”?


    • Vladimir Khorikov

      Hi Andres, thanks!
      Could you give links to these two article? Not sure I read them.

      • Anders Baumann
        • Vladimir Khorikov

          Ah, I though you refer to some other articles.

          Yes, definitely, the Lukas Eder’s “Running totals” is a good example of a complex read-only query with business logic in it. I too think it’s a good use case for SQL.

          Regarding the Rob Conery’s view on ORMs – I disagree completely. Mapping OO approach into relational data is a difficult task, and ORMs are there for good reasons. We can avoid using them is simple cases, of course, but for more sophisticated scenarios, they provide too much value to be ignored. Martin Fowler has an excellent article on this:

          I think one of the misconceptions here is that one could think of an ORM as a replacement for the necessity to learn how a DB works. It’s not true. While ORMs help reduce complexity, they are leaky abstractions and they can’t substitute the need for diving into SQL.

          • Anders Baumann

            Thanks for the quick answer.
            I agree with you about ORMs. I also think they provide a lot of value.

  • Lukas Eder

    You’re using a flawed argumentation based on the fact that SQL is not about manipulating individual rows, then you proceed by mis-using SQL via stored procedures that operate on individual rows. Why not create a view that generates the description info on the entire set? And suddenly, SQL would become the perfect place for said logic, based on your argumentation:

    CASE WHEN [Type] = 1 THEN
    ‘Student ‘ + Name + ‘ with average mark ‘ + CAST(AverageMark AS VARCHAR(10))
    ‘Teacher ‘ + Name + ‘ with salary ‘ + CAST(Salary AS VARCHAR(10))
    END AS Description
    FROM dbo.Person

    Observe: The CASE expression has been in the SQL standards from very early on.

    • Vladimir Khorikov

      I think you miss the point of this post. Partly because the example in it isn’t the best one, a SP mutating data would fit better.

      Why not create a view that generates the description info on the entire set?

      Because, in an OLTP scenario, you don’t need an entire set, you just need a single row. And not just return it with a SELECT statement to the client but perform some logic on top of it. This leads to use of the IF statement.

      Observe: The CASE expression has been in the SQL standards from very early on.

      I very well aware of this fact. Again, the point of the article is not that SQL isn’t a place for business logic at all. Your select statement falls into the category “Read-only queries with business logic” which is considered a good use case for SQL. Such queries aren’t helpful, though, when you try to mutate one or several rows in the database according to complex business rules.

      • Lukas Eder

        I do understand the point you were trying to make. I’m just saying, you didn’t actually make that point. You just displayed that it is hard for someone used to working with general-purpose languages to re-think and start modelling their applications / data flows in SQL. If they were capable of re-thinking, you could perfectly well implement a variety of OLTP scenarios in a SQL-esque way. It’s just something that most people don’t know how to do / aren’t used to do.

        The statement that underlines this argumentative flaw the most is:

        Which one is easier to understand? I bet your answer is not “the first one”

        The “easy to understand” argument is often abused in order to justify personal preference over a certain programming paradigm, i.e. lack of understanding of the alternative paradigms.

        • Vladimir Khorikov

          I think you do miss the point here. It’s not just a matter of familiarity (admittedly, there is some of it, too), it’s a matter of the level of effort you need put into maintaining an OLTP scenario written in SQL.

          Here’s a couple of quotations:

          Imagine how hard it would be to encode this business rule using SQL: ‘If the person is a teacher and their class is the best in the school, then increase their salary by 20% and assign 2 other teachers for mentorship’

          I’m not saying it’s impossible (it surely is!), but the level of effort required to implement and, more importantly, to support and maintain such a solution is much higher than it is using regular OO (or FP) languages.

          Now, are you saying this statement is false and the level of effort required to implement and maintain such a logic in SQL is in fact less than that of implementing it using an OO or FP language?

          • Lukas Eder

            I do get the point. I just think you could’ve done much better with your argumentation. Cheers, mate. 😉

          • Vladimir Khorikov

            I just think you could’ve done much better with your argumentation.

            That is certainly true.

            But you didn’t answer the question. I’m really curious about your opinion here, taking into account that “Fallacy #5” in your article is the only point I disagree with.

          • Lukas Eder

            You want a 0/1, black/white, absolute answer from me? Why? Because I used 1-2 hyperboles in the article?

            That fallacy #5 displayed that this particular kind of calculation was best solved in the DB. Others aren’t. That’s it. There’s no point in cargo culting absolute architecture goals or putting one approach on a higher level than the other…

          • Vladimir Khorikov

            Don’t take it personally, I just asked for your opinion on what you consider the best option in this particular case to figure out the boundaries of the #5 statement you make and see weather our opinions really differ or it’s just the statement itself is presented vaguely.

          • Lukas Eder

            Don’t worry, nothing personal here. I think our opinions don’t really differ.

          • Lynn Pettis

            Here is the problem. Your requirements are vague. What do you mean by “best” in the school. What are the criteria for identifying the teachers to be assigned for mentorship? Does all the data needed for the decision exist in the database or is some of it subjective my the user of the application? Given that all the data exists in the database, the database schema known, and the requirements well defined, then yes I could develop a procedure to accomplish the task. At that point the application requesting the data could be desktop application, a web based application, or even a mobile application, maybe even all three. At which point, the logic is encapsulated in one place instead of three different applications.

          • Vladimir Khorikov

            You’ve interpreted the requirements pretty well.

            The criticism you raise is based on a false assumption that it’s impossible to combine the logic in a single place in case there are several client applications. It is. Just replace stored procedure with some API (REST for example) and all three applications now don’t connect to the DB directly but work with some service owning with this DB exclusively. This solutions is much more flexible and maintainable in the long run.

      • TomtTomtTom

        It appears to me that your experience of OLTP must be very limited; a typical website handling retail needs to process multiple row results frequently – for example if I (a customer) want to see recordings on cd by the group Runrig and choose a couple to buy as a present for someone the web app supporting that had better be able to get that list from its data store, whatever that is. This handling of multiple rows is common in OLTP, single rows is less common. An online music shop insisting on showing only one record at a time would not get very far; it would probably do even worse if it couldn’t list the tracks on a record, or say whether a track is available on other records and if so which.

  • Todd Everett

    A well written article with very good points. As a database professional I try to seek out and read perspectives such as this that differ my own. I think your perspective on what business logic is or is not, and where in the 3 tier architecture it should reside, boils down to the old adage that if your only tool is a hammer every problem looks like a nail. The author has a decidedly OO programming perspective and his position on these questions flow naturally from that. I on the other hand have a decidedly database centric perspective and my position on these questions flow in an opposite direction. My alternative perspective is well summarized by the Oracle white paper “Why Use PL/SQL?” ( and
    Toon Koppelaars’ blog “The Helsinki Declaration (IT-version) ( The “thick database” architecture is very effective in situations where the system in question is at its core a data processing system such as a customer information and billing system. I view the OO perspective as more effective in situations where the system in question is at its core a function processing system, such as a CAD/CAM drawing system. The OO paradigm can also be very effective in the GUI tier of a data processing system should a rich, native OS UI be required. Most line of business data processing systems however require only a web client and a product like Oracle’s APEX eliminates the need to write GUI code entirely.

    • Vladimir Khorikov

      Thank you for writing.

      I worked as a database software engineer for a long time and I’m still involved in a lot of RDB development activities, so your argument about my OO perspective is not correct. I can see where the arguments from both perspectives come from.

      Also, I give examples of the use cases I personally consider good for SQL, so I don’t think your hammer-and-nail remark is suitable here.

      • Todd Everett

        >>your argument about my OO perspective is not correct

        I was not trying to imply that your personal perspective is OO. Instead I was making the observation that approaches such as domain-driven design originate from a programming (and decidedly OO as opposed to structured) and not an information resource management (IRM) perspective which focuses on managing data as a company resource distinct from any software which might manipulate it. This IRM perspective naturally gravitates toward having business rules implemented in the database as from its perspective those rules are an integral part of the data model and must be applied regardless of the number or type of software systems. The RM as defined by Codd included data integrity as well as structure (the table) and manipulation (the relational algebra). IRM sees the rules, the business logic, as belonging to the data, not to the software.

        >>I give examples of the use cases I personally consider good for SQL, so I don’t think your hammer-and-nail remark is suitable here.

        I was using the hammer and nail analogy to observe that the perception those use particular use cases are good or bad comes from a software development (and within that OO) perspective as opposed to an IRM perspective and can be countered by those for whom SQL primary expertise. Other commenters have already provided some counter examples.

        Much of this comes necessarily due to the tremendous level of specialization required in today’s IT world. A software developer must focus all their energies on developing their software engineering skills in specific high level languages and thus naturally has the discipline of software engineering and those high level languages as the hammer. A database designer must focus all their energies on developing their logical database design skills in specific DBMS’ and has the discipline of logical database design and those DBMS’ as the hammer. It is only natural that the specialist will frame all problems to be solved from that speciality. Only by working together in a collaborative manner can the software developer and the database designer arrive upon the best approach for the situation. Since the post presented a view point decidedly against placing business logic in the database I wanted to present an alternate view point decidedly for it along with an example of commercially available technology supporting it. Readers will have to decide for themselves which approach makes the most sense for their particular situation. I appreciate the effort you made here to present a view point and enable discussion as it is this manner we are able to learn and grow.

  • BI Apprentice

    Hi Vladimir,

    Interesting article, and appreciate the points made. I think this statement sums up your main point: “SQL is the language optimized for operating sets of data.” Absolutely, SQL is a declarative language.

    One important point this article does not address, however, is that fact that all modern relational database have a procedural language that is designed to work with individual data items. For SQL Server, that is the procedural extensions of the language, and for Oracle, that is PL/SQL. Modern relational databases also assign a SQL engine to process declarative SQL, and another engine to process the procedural language. So the procedural language performs its procedural tasks (looping, if-conditionals, variable assignment, and the like), and then relies on SQL to retrieve data from and to save data to the database.

    Using an object-oriented language does not change this. The language still relies on SQL to retrieve and save the data. The OO engine handles the OO language, and the SQL engine handles the SQL language.

    What you are hinting at in the article is the conceptual mismatch between any procedural language and SQL. The former uses programming constructs and the latter is declarative and works with sets.

    BI Apprentice

    • Vladimir Khorikov

      Thanks for writing.

      You are right, SQL is a Turing-complete language and we can use it to encode any business logic we want. The point of the article is different, however. The point is that such implementation would be more complex and thus less maintainable in the long run. Because of that, I advocate programmers limit the use of SQL to read-only queries (which can potentially contain business logic, that’s fine) and simple CRUD statements where possible.

      • BI Apprentice

        Hi Vladimir,

        That is a good point that writing logic in a more modern OO language is more maintainable than writing that same logic in the older database procedural languages. I try to follow this for any context where that is directly possible, such as when an application has been written in a OO language to use the database.

        I have seen contexts where using on OO language would add unnecessary complexity to the architecture, and require different kinds of developers than what are available. My primary example of this is when using SSRS to create operational reports. Sometimes we need an advanced transformation of the data which is beyond what SSRS can (easily?) provide, so some business logic is needed for a report. I have developers that are able to write stored procedures, but are not able to develop well in an object-oriented language.

        Technically they could develop the business logic in Visual Studio, create a DLL, and push it into the GAC on all of the SSRS servers (dev, qa, prod) every time there is change But this overcomplicates the architecture in my opinion, and makes pushing a report change quite burdensome. And I would then need to add a developer to the project capable of authoring in a .NET OO language.

        What are your thoughts on using stored procedures in such a context?

        BI Developer

        • Vladimir Khorikov

          I personally consider such examples a good use case for SQL (I call them read-only queries in the article; queries that don’t mutate data in the DB they work upon).

          Basically, the differences boil down to OLAP vs OLTP scenarios. SQL fits better for OLAP. OOP/FP – for OLTP.

          • BI Apprentice

            Good point. Yes, I see now this use-case does line up with your article. Well said!

  • Erwin Smout

    Your usage of the term “business logic” is rather vague.

    It can be interpreted to mean to include the enforcement of
    constraints on the data (aka ‘business rules’). Enforcement of these
    unequivocally belongs in the dbms, period.

    It can also be interpreted to mean to include things like “if a new
    customer arrives, then within a week we send him a welcome letter.”
    Trying to push stuff like this in the data layer is probably a big

  • IDisposable

    If you had included the word ALL, I might agree, but you seem to intimate NONE and that’s just narrow-minded.

  • R6

    Are you sure Student and Teacher are subclasses of Person, i.e. inheritance is the right way in this case? What happens if a Person graduates, so is no longer a Student? You delete the Student instance, deleting also the Person data? Maybe you don’t need it anymore, but what if the Student becomes a Teacher? You instantiate a Teacher, copy Person data from Student, and delete the Student. But now it is a different Person instance, what about the existing references? What if you will need to support any combination of roles Student, Teacher, Manager, etc?

    A proper relational solution does not have this problem. And in fact, implementing business rules independently from applications is the main reason relational databases where invented.

  • gisborne

    Does your analysis change in the case of Postgres, where you can write stored procedures in any of Python, Perl, Javascript, Java, Lua, R, Common Lisp, Scheme and a whole bunch more?

    • Vladimir Khorikov

      It still stands. Domain model should be kept in isolation from any out-of-process dependencies, doesn’t matter what language is used to access the database.

      Important note, though – this is about business logic that changes data. Reading is fine either way.