Home Uncategorized No, stored procedures are NOT bad

    No, stored procedures are NOT bad

    477
    14

    I recently found a rather old post from Frans Bouma‘s blog, “Stored procedures are bad, m’key?”.

    Since comments are closed (he posted it almost a year ago), I have to reply here.

    I’ll state my bottom line at the top: Stored procedures are not only not bad, they’re necessary for maintaining loosely coupled, application agnostic, flexible databases. And they’re also necessary for maintaining loosely coupled, database agnostic, flexible database-based applications.

    Mr. Bouma’s argument centers around three claims: Ad-hoc queries are easier to construct in application code, stored procedures don’t provide greater security than the application already does, and in certain cases stored procedure execution plan caching can be detrimental to performance. And all three of these arguments are true! But none of them has any bearing whatsoever on the real benefits of stored procedures.

    Let’s have a quick refresher on Computer Science 101, as it applies to object-oriented design: Loose coupling and high cohesion. What does that mean in plain English? Each module, or object, should have one very specific job, which it can perform for a VARIETY of other modules or objects on request (high cohesion). Furthermore, no module or object (or as few as possible) should depend upon the inner workings of another module or object. This latter problem is known as tight coupling, and its consequences are dire. Change one piece of code, end up changing every other piece of code that uses it.

    News flash for Mr. Bouma: This is the reason object-oriented programming was invented. We try to separate components from one another so that their functions can be re-used and re-applied to other modules, other objects, and if we’ve done our job really well, even other applications entirely.

    So how does this apply to the relationship between a database and an application?

    The database, undoubtedly, should be completely application unaware. The database is serving up data. It has no idea what application is requesting the data, or whether an application is requesting the data. It needs to have no such idea. All it needs to do is keep serving and all is happy. Furthermore, the database doesn’t care if multiple applications request data, or if those applications request the same data. The database is set up to provide a single point of access for all data requests in its domain of knowledge (i.e. the data in the database being queried).

    And now to the other side, the application itself. I am an advocate of the application being as database agnostic as possible. I don’t believe this is entirely possible, but it is certainly a goal to which we can aspire. Applications should request data from the database using standardized, documented interfaces, after which that data should be composed as quickly as possible (at the lowest level) into native objects. This allows for changes in the data interface to have as little ripple effect as possible in the application.

    Mr. Bouma makes the claim that, “changes to a relational model will have always an impact on the application that targets that model”. Again, he is correct. Which is why applications should NOT target a model. Applications should have absolutely no knowledge of database schema, including table names, column names, data types, or any other information. This is the role of stored procedures. We can, using stored procedures, completely encapsulate this metadata and provide standardized interfaces into the data.

    Need to change a datatype in the database? Perhaps you won’t need to change the output values that the application receives from the stored procedures. Perhaps you won’t need to change the parameter input values. No change is needed in application code. Need to change a column name? Same thing. Need to re-architect the entire schema? Again, just change your stored procedures. The application will keep running as if nothing changed.

    Contrast this to Mr. Bouma’s suggestion that we drop stored procedures altogether and instead build all queries within application code. Suddenly, any small change in the database needs to be completely regression tested throughout not only one app, but every application that uses the database. You’ve created an extremely tight, perhaps unbreakable coupling between the application and the database. Large-scale changes to the schema will most likely never be possible. Will there ever really be time to re-write all of that application code?

    Now, back to the three central arguments: Yes, ad-hoc queries are easier to construct in application code; but it’s also argued by many database experts that ad-hoc queries are a sign of either poor database design, poor application design, or both. Either way, dynamic SQL isn’t too hard to work with, and I’ve seen plenty of extremely ad-hoc applications in which its use is hardly a stumbling block. Next, security. It’s true that if a user isn’t authenticated in the application, he or she won’t be able to use that application to access the data. So in that case, the stored procedure does not provide greater access control. But the same is certainly not true for every other application that uses the same database. Unless, of course, code is duplicated across every application. The database should be the final word on its data. This includes data security and data integrity. Application code simply cannot do this if the database need ever be shared. Finally, Bouma’s assertions about cache plans are simply not worth touching. The WITH RECOMPILE option has been around long enough that DBAs and developers know it’s there and know how to use it.

    Now that we’re at the bottom of this post, I’ll re-state my conclusion: Stored procedures are necessary for proper object-oriented database-based application development. Not using them will force code duplication, tight coupling between applications and databases, and greatly increased man hours for every change made to either application or database.

    Previous articlePattern-based replacement UDF
    Next articlePaging in SQL Server 2005
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    14 COMMENTS

    1. There are 2 main reasons I don’t like to use stored procedures that Adam Machanic didn’t cover. Stored procedures are storing to much business logic. Aren’t OOP tries to separate business and data layers? And stored procedures definitely don’t handle business logic as good as application. Secondly, stored procedures is way to hard to debug compare with application.

    2. Iceteh: Thanks for your comment. Naturally, I disagree with both of your points.
      You assert that stored procedures are "storing to [sic] much business logic," but you’ve not defined the term "business logic." And if indeed the procedures have been tasked with too much, said logic can be moved out to another tier. This is not an argument for or against stored procedures; it’s simply a question of where to put things. If you’ve put too much in the procedures, that’s your fault as a developer, not a problem with procedures themselves.
      Second, you mention that stored procedures are more difficult to debug, and I can’t agree here at all. I would say that they’re equally difficult to debug, and require a different debugging methodology. This is of course purely subjective, and so I doesn’t really apply to a debate on use of stored procedures. A SQL Server professional with zero application development background is going to have a much easier time debugging a stored procedure, whereas a developer with little SQL Server experience will have a much easier time debugging the application code.
      Your application will still require use of SQL either way–be it generated or otherwise–in order to pull data from the database. And at some point you might need to debug this SQL. So whether it’s in a stored procedure or not seems to have no bearing on this discussion. Assume the same exact SQL can either live in stored procedures or in the application layer–then read my arguments here, and at Simple Talk and they’ll make more sense.
      http://www.simple-talk.com/sql/t-sql-programming/to-sp-or-not-to-sp-in-sql-server-an-argument-for-stored-procedures/

      • Old (ancient) post but SQL has given me a career of 25 years. So what’s so bad about that?

    3. Sipununu: Me too. I’d rather spend my days sunning myself on the beach and dating supermodels. But alas, we play the cards we’re dealt, and at least SQL pays the bills. Cheers!

    4. With the use of IoC, I am able to create a separate data access layer that is never referenced by the Business Logic Layer.  The coupling between the application and the data access layer is completely removed.  Now that we’re no longer using SPs, we can now have all of the data queries, inserts, and updates under unit tests.  
      In your example, if you change the entire database schema and add/remove certain properties… there is no way in knowing that your database SPs are still working correctly without a manual test of said SP.  Without using SP’s, we are now able to have an assembly or two control everything that SP’s normally would, but everything is tested.  This has saved us countless hours in which we used to waste debugging SPs.

    5. Surreal:
      How does decreasing coupling between your business layer and data access layer have anything to do with using stored procedures? Your data access layer is still tightly coupled to the state of the database at any given time. It doesn’t really matter how you’re populating your business objects. (Although I certainly agree that reduced coupling between business and data layers is highly desirable.)
      It’s odd that you feel that stored procedures require manual tests any more so than your current data access layer would. Can your unit test framework not handle a database call? A standard unit test should be able to do the job, and if that’s not enough there are several testing frameworks for stored procedures. Google around for them. At the end of the day your data access layer is building SQL calls, and stored procedures make SQL calls, so you’re essentially testing the same thing, just getting there via a different path.
      What you’ve done is moved the logic to a place you’re more comfortable working with — and that’s fine if that’s what works for you — but to be clear, this doesn’t change the dialog nor the debate in any way.
      –Adam

    6. It has a lot to do with Stored procedures.  For example, in my scenario I would have to recreate a new concrete class that is meant specifically for updating data through a stored procedure in my application layer.  If I use Linq2Sql or Entity Framework, this can all be done in the data access layer which is not referenced in my application layer at all.
      A stored procedure query is read only, while a Linq2Sql or an Entity would simply need to alter whatever it needs to alter and then it just needs to be updated.  I also would need to have a concrete class specifically for the data model and then a separate class that would handle all of the CRUD operations.  I don’t want a read-only concrete class to be able to update itself, because it can’t.  In essence, I would need 3 classes just to use a stored procedure on a business object.  That makes things extremely complicated compared to the one class if I were to use Linq2Sql or EF.  
      I’m not saying Stored Procedures are completely bad.  They have their place, but you can’t say Stored Procedures are a MUST and in many cases inhibit the ability of the application to change.  I usually use Stored Procedures for specific reporting.  I avoid them when possible because of the complexity it brings.  
      That and when I see a database with 500+ stored procedures I cringe because I know something is duplicated or just a variable difference in a query.

    7. "I’ll re-state my conclusion: Stored procedures are necessary for proper object-oriented database-based application development. Not using them will force code duplication, tight coupling between applications and databases, and greatly increased man hours for every change made to either application or database."
      Using stored procedures will force duplication since many queries are only slightly different and can slightly alter the read-only data of the business objects.  Higher level business objects may only become partially filled.  
      There is no tight coupling between applications and databases.  My applications don’t even know a concrete Data Access Layer class is ever created because of IoC.  They just know of their business objects.  How they get populated and updated is of no concern to the application.  A change in business objects will require every application to be updated that is shared.  That is true like you have already stated.  Same would be true with Stored Procedures.  To say SPs are necessary is not true, since any change to a database schema like changing a value from an integer to varchar, for whatever reason, would only affect my one data access layer.
      If my company were to switch from MSSQL to MySQL, I would need to rewrite a data access layer like you would have to rewrite SPs.  My applications wouldn’t know anything ever changed.  The IoC would simply get pointed to the correct assembly.
      Stored Procedures are NOT necessary.  That’s all I’m saying.

    8. Surreal:
      I’d forgotten that I was quite so rigid in my conclusion. Keep in mind that I wrote this almost 7 years ago. I will agree that today’s ORM solutions do make it far easier to create a robust solution without stored procedures than it used to be. I think were I to write this today it would be a very different article.
      These days I only very rarely use stored procedures for data access anymore, but I do make heavy use of inline table valued functions in their place (which I think of as having many of the same benefits, with some added bonuses such as output contracts). I still heavily use stored procedures for data updates; but in my world updates are not usually simple CUD, and require fairly significant updating data
      One thing that I think is special in your case is that you have only one data access layer for the database. In the project I’m currently working on, for example, we have at least 5 different applications, written in different languages and running on different platforms, all sharing the same data. It would be impossible to create a single data access layer. Maybe an enterprise service bus or fabric model could be used, but that has its own headaches. The TVF layer I’ve created works nicely and allows me to shield all of these apps from changes when I need to make them in the database.
      I do not agree that lots of stored procedures means lots of duplication. That is certainly not the case everywhere, but I know that there are a lot of really bad databases out there. I suspect that a lot of the developer disdain for stored procedures has to do with the fact that a lot of people write them who really have no business doing so.
      –Adam

      • AM: I suspect that a lot of the developer disdain for stored procedures has to do with the fact that a lot of people write them who really have no business doing so.

        Which frankly is most developers, who know jack about databases and how they actually function. In 25 years (yes, 25, and I did start at Microsoft SQL Support, so I do know what I am talking about) I can count on both hands the number of people I have worked with who ‘got’ databases. Most developers know little to nothing about them, as evidenced by the countless bad databases I’ve dealt with in my career. So yeah. ‘Surreal’ is way off base here IMNSHO. Truth hurts.

    9. I remember reading this response way back when it was written, and thinking how wrong you were.  It’s nice that after seven years, you can finally (sort of) admit it.

    10. @Phil:
      I wasn’t wrong then. Technology improved, methodologies changed. You haven’t changed at all in 7 years? That’s sad.

    Comments are closed.