THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

www.SQLServerBible.com

Why use Stored Procedures?

There are as many opinions for and against using stored procedures as there are database-related roles. I believe the position that is most qualified to speak to stored procedures is the data architect’s role. Data architecture is the role that studies the various methods, patterns, standards, and best-practices that result in databases that will, together, best serve the organization now and in the future.

Don’t confuse data architecture with data modeling. Although data architects often do data modeling, data architecture and data modeling are different disciplines.

Data architecture evaluates various database designs and implementations by several criteria: e.g. usability, scalability, integrity, extensibility, security, and availability.

Of those six basic database criteria, there’s one that stands out as the most expensive to refactor or correct later: extensibility – the ability to modify the database to meet changing requirements. I’ve seen organizations with horrible database designs, that desperately need to re-develop that database, but they can’t. The database is so tightly coupled that the idea of making a slight change to the database brings management to its knees in fear. Dozens of applications, hundreds of reports, and a score of nightly ETL and maintenance tasks are tied directly to tables in the database. At one location, it takes a six person, highly qualified team six months to just modify a column. The result of tight coupling is a brittle database.

The cost of a brittle database is enormous to an organization. On the corporate roadmap, the database is the primary roadblock. The organization can’t implement critical changes because the database can’t support those changes. The organization (both internal clients and IT) will try to work around the brittle database with additional databases in an attempt to find features. IT will try to wrap the brittle database with additional layers in an attempt to find loose coupling. But complexity breeds complexity and eventually the organization with multiple completing database solutions will try to apply a master database so there’s a single source for answers. All this takes years and millions of dollars and… never… really… works.

The only real solution is an abstraction layer that fully encapsulates the database. Every database fetch, insert, and update must go through this access layer. Just as SOA provides this encapsulation for processes, the database needs the same black-box API. If the database team wants to refactor the database to improve some feature, it’s free to do so. If a new feature is added, the database team can add that feature and modify the API. It’s very clean, easy to refactor, and the database is now an on-ramp to the corporate roadmap.

If you can agree that a logical abstraction layer is required for database extensibility, the next question is, “Physically, where should the abstraction layer be implemented?”

Many will answer that it’s implemented in the programmer’s favorite language, and unfortunately that’s probably true.

A data architect would ask this probing question, “ What’s the projected life of the data?” When I teach my data architecture seminars, or give conference talks on data architecture, I ask the question, “How many of you have data that’s five years old?” every hand goes up. “Ten years old?” a few hands go down. This progresses until eventually one guy has data that 40-50 years old.

The point is: data has a long lifespan. If data has the lifespan of an elephant, then application languages have the lifespan of bugs. Think back just five years ago, what was the hot application language used to develop DAL layers? Ten years ago? Anybody up for writing a new web page that calls the COM+ DAL layer? Didn’t think so.

The data architect’s answer to why use stored procedures is that T-SQL is only language that you KNOW will last as long as the data. A stored procedure that was written ten years ago is still just as easy to call as one that will be written today, or in the future. Anything less is building a long term solution on today’s fad, and it’s just plain foolishness.

If you'd like to talk with me about this in person, I'll be at TechEd in LA this week, hanging out in the SQL Server pavilion.

Published Saturday, May 09, 2009 11:57 AM by Paul Nielsen

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Karen Lopez said:

Those of us who have worked at utilities work with data that more than a 100 years old.  Many addresses and place names could be 100s of years old, too.  So I'm guess that people work with data much older than they think.  That's why I'm not a fan of tailoring data structures to optimize them for just one application or use.

I believe that "data architects" should be responsible for more than just database issues.  I see the profession of data architecture encompassing, modeling, data quality, reliability, scalability...all the -abilities. I do agree that model <> architecture.

One other reason to use SPs is to keep processing close to the data.  In most cases, this is a significant performance trade-off.

Great post.

May 9, 2009 2:39 PM
 

Eric Friedman said:

T_SQL won't do you much good if you change your database.

May 10, 2009 2:30 AM
 

AaronBertrand said:

Eric, how many times have you switched database platforms?  In my experience this is much less common than the alarmists like Celko want us to believe.

Anyway, whether you use stored procedures or embed your T-SQL code in the application code or middle tier, do you think one requires less work than the other, if you need to port to Oracle or DB2?  The truth is that no matter what platform you choose, you need to decide how you want to trade off ease of portability for the benefits of using the "local" dialect.  I just don't see "use stored procedures" or "don't use stored procedures" as a major bullet item in that decision.

May 10, 2009 2:20 PM
 

alexandrul said:

+1. I have one project where every layer was replaced, except the stored procedures on SQL Server 2005 (and I mean: C# classes + web services + Infopath, ADO.NET + ASP.NET, Linq + ASP.NET MVC)

May 10, 2009 4:50 PM
 

Harry said:

I am against using stored procedures unless (a big unless) your model is very stable, and you have very good people (DBA, Data Architect, ... whatever title you want to call them) that can write them in a maintainable way ...

Why? Because stored procedures in most projects are not easy to change and test, and they are coupled to the structure of your data. Try to modularize a long stored procedure into smaller (and reusable) ones is not a easy one ...

Again, unless you don't plan to change your data schema often, or unless you have people that are really good at maintain (or writing very good stored procedure) ....

Thanks, I think I will pass stored procedure except the very simple ones that I know I can handle ...

May 10, 2009 7:39 PM
 

Alexander Kuznetsov said:

Eric,

In fact, using stored procedures usually simplifies porting. I have a lot of experience with porting between SQL Server and Oracle, and eventually came to this conclusion. Not using stored procedures exposes your code to subtle differences between platforms. Examples here:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/02/24/writing-ansi-standard-sql-is-not-practical.aspx

and here:

http://www.devx.com/dbzone/Article/32852

May 10, 2009 10:20 PM
 

Alexander Kuznetsov said:

May 10, 2009 10:25 PM
 

Daniel said:

May 11, 2009 6:11 AM
 

Harry said:

Alexander,

Actually, I understand what you are saying... Again, "IF" we have good people writing good stored procedures and decompose (divide and compose is our best friend) the stored procedures in a way that make them testable, highly-cohesive, loosely-coupled, I will use stored procedure ...

My friend, you and I knows for normal projects, that is a big BIG IF ...

Unfortunately, I can foresee my boss take this article as proof that we (we don't have real DBA, we write lousy stored procedures) should put ALL data logic in stored procedure ...

May 11, 2009 3:46 PM
 

Mark said:

@AaronBertrand - The main reason people don't switch is because it costs too much - because they used stored procedures and/or embedded db specific code in apps/reports/integration code/etc.  Plus, small dbs are relatively new - before then, it was not possible because there was nothing to move to. Now many companies  have multiple databases. Some of them are not relational.  Even Alexander says he has a lot of experience porting.

I have switched at least twice (where the switch was not planned). I probably am about to do another. I have switched other times but it was planned. This all worked because i used Hibernate/NHiberante.

@Alexander Kuznetsov - it is not as easy as using a good ORM. Change dialect and generate ddl! Anyway, using SPs do not preclude using db specific things elsewhere. And then you have one more place to change things.  I had someone switch to MS Access because it was "free". He used store procs and thus had to recode all of that code. There was no real reason to use them other than "that is what you are supposed to do".

@Paul - I disagree that it is the "data architect" who is in the best position to make the decision. I would agree that a person who has had or has experience as a "data architect" is.  Otherwise you have a single focused person making a decision and that usually ends up screwing up the overall architecture. As soon as you start thinking that the database (NOT the data) is a separate system or as the center of the universe, you've already gone down the wrong road.

Data != Database.

Database != RDMBS

May 11, 2009 9:56 PM
 

ALZDBA said:

I'm convinced sprocs are the means of getting most from your db engine to deliver the functionallity the sproc is meanth to.

You can use dialect, hints (only if strictly needed) ... code that is directly bound to your db engine.

But it is a battle that is being fought over and again, because of "new features" of any used programming language.

Nowadays the new kid in the block is called LINQ .... and off we go again.

May 12, 2009 3:28 AM
 

K. Brian Kelley said:

I've seen applications completely rewritten with new interfaces, functionality, etc., and what Paul speaks of here I have found to be true. I know one app that we re-skinned and rebuilt code for. Complete overhaul... except at the database level. The reason is because we had exposed access to the data through well-defined stored procedures that did the job nicely. We ended up implementing a few new stored procedures to expose some of the hierarchical structures in more details, but the fact of the matter is that while the application code got changed, the database effectively did not.

Harry, you're right that if the database layer is poorly written it really doesn't matter, but an analagous argument is all BMWs are bad because I bought a bad one at Jim's Used Auto Sales and it was a lemon!

May 12, 2009 3:34 PM
 

Joe Celko said:

>>The only real solution is an abstraction layer that fully encapsulates the database. <<

Very good article.  

The application programmers are painfully aware that every few years, they will have a new "Language du Jour" to learn.  I went thru 8 versions of FORTRAN, a little CDC COBOL, B5000 Algol, 17 version of C, 4 versions of Pascal, Progress 4GL, Informix 4GL, SAS, SPSS and then I lost count.  

This is why good application programmers know algorithms and computational models (procedural, block structured, declarative, OO, functional, etc.) so they can adjust to each "Language du Jour" when needed.

I am amazed at how many DATABASE programmers believe that they will never port code.  Anyone looking at the real world will see that data has become global and it is not locked into one version of one release of one SQL product on one server.  This is a no-growth failure for your enterprise.

May 12, 2009 3:44 PM
 

Mark said:

@K. Brian - I can do  the same with an app. In fact, the database could go from RDBMS to something else. The question is how much code less code would have had to change using a DAO type layer?  

May 12, 2009 9:09 PM
 

AaronBertrand said:

Mark said:

>> The main reason people don't switch is because it costs too much - because they used stored procedures and/or embedded db specific code in apps/reports/integration code/etc.

I don't buy it, this seems like a circular reference to me.  If they wanted portability, why did they use stored procedures?  Is portability a goal, a means, an excuse, something else?

>> Even Alexander says he has a lot of experience porting.

And I would bet that he would agree that the majority of his effort was not due to the fact that he used stored procedures.  Unless he used pure SQL-89 and all of his work in porting was changing the outer procedure syntax (hint: highly unlikely).

Recall that I didn't say, "oh gosh, nobody in the world has ever ported."  What I'm saying is that there just aren't many business cases to switch platforms once you have developed on one.  This is independent of whether or not you use stored procedures, as you will have the same problem (in fact even bigger) if you've copied that ad hoc T-SQL query and embedded it in application source code all over the place in lieu of using a centralized stored procedure.  We can't all control our applications, middle tiers, etc. the way we can control the database.

Anyway you can feel free to avoid stored procedures just in case you ever have to switch from SQL Server to Oracle or DB2 at the drop of a hat.  I think you'll have bigger problems in the meantime, as the cost of porting is about re-writing code that uses proprietary features (or uses similar features like indexes in different ways), not about the stored procedure syntax.  

May 12, 2009 9:34 PM
 

Harry said:

I think I am a competent developer. I have worked with other competent developers. I seldom see people who can write maintainable stored procedures. What I mean by maintainable stored procedure is that they are cohesive and loosely coupled (unless you think these two principles are not important) and also can easily be changed if you have a new business need to change your database schema. We developed rules like no method should be more than 100 lines, methods should be small, ... etc in programming. However, I seldom find good guidelines for writing 'maintainable' stored procedures. The only books that I know deal with this are Ken Henderson's books.

Comparing to programming languages, we have design patterns, effective programming books, effective languages books ... these knowlege/paradigms evolved from procedural to OO to aspect-oriented to maybe something else I don't know. That makes general programming easy to maintain and easy to write good code, which I think we don't have that kind of mindset in stored procedure ...

But you will argue, stored procedures are not designed for these problems. I agree. So, let's not recommend people put 'complex logic' into stored procedure. We have better tools and training in general programming languages to handle those complex logics ...

May 12, 2009 11:39 PM
 

Linchi Shea said:

There are two types of users, package vendors who sell application packages that may need to store data in a database, and enterprise users whose business is not writing application software, but who write application software in order to support their main business.

For the package vendors, database portability is often essential (unless they also happen to own a DBMS). And they very much like to be able to switch at the drop of a hat, or more accurately at the request of the prospect customer.

For enterprise users, I agree it would e extremely rare to deveop an application with DBMS portability as one of the key requirements. Whether or not procs are used is rarely, if ever, an important factor in any DBMS switch decision. But then, it would not be uncommon to see that being listed as one of the reasons to justify a switch.

May 12, 2009 11:51 PM
 

Paul Nielsen said:

I’m not at all surprised to read that most application developers find it unreasonable to expect good, high performance, maintainable stored procedures. The mindsets of the application developer and a database developer are diametrically opposed. What makes a good application developer is different than what makes a good database developer. Developing excellent stored procedures requires thinking in sets, which is not a skill that’s taught or encouraged in application developers. I’ve known good application developers who struggled for years to write complex queries and still resort to cursors far too often.

Not having the qualified talent to develop quality set-based stored procedures won’t make your problem of the lack of extensibility go away.

Nulltheless, there’s nothing easier to code against than when a good database developer writes a good database abstraction layer using set-based stored procedures and views. For my application, all the logic is pushed down into the data and handled by set-based queries. The .NET front-end cost less than $4K to have written and the developer told me it was the easiest job he ever had to do. The API was well documented - just make the proc call and display the results.

---

To those who claim that the abstraction layer is language agnostic – any language will do just as well as T-SQL, I ask, is that abstraction layer absolute? Does EVERY report, EVERY ETL process, and EVERY application now AND IN THE FUTURE access the database through the abstraction layer? If not then you’re in trouble own the road.

May 13, 2009 12:48 AM
 

Alexander Kuznetsov said:

Aaron,

>>> Even Alexander says he has a lot of experience porting.

>And I would bet that he would agree that the majority of his effort was not due >to the fact that he used stored procedures.

Yes. I know that the most efficient way to write protable applications is to utilize stored procedures.

Paul,

> The mindsets of the application developer and a database developer are

> diametrically opposed.

Can you please explain why?

>What makes a good application developer is different than what makes a good >database developer.

I do not think so. I think that all the best practices that we have in application programming (aka "What makes a good application developer") should apply in database programming too, "cohesive and loosely coupled" modules, the patterns, unit testing, code reusing and such. What do you think?

May 13, 2009 2:06 PM
 

Mark said:

@AaronBertrand -

>I don't buy it, this seems like a circular reference to me.

That is exactly my point

>If they wanted portability, why did they use stored procedures?

Exactly. They don't want it. The db vendors don't want it either.

> Is portability a goal,

Yes. If you are software vendor, you client might have database A and not want to introduce database B.

>a means

Yes. I this day and age, flexibility is VERY important. Try negotiating a decrease with your db vendor when they know you can't switch

>And I would bet that he would agree that the majority of his >effort was not due to the fact that he used stored procedures.

That doesn't lessen the fact it that effort will be expended.

>We can't all control our applications, middle tiers, etc. the way >we can control the database.

You can control them pretty well. Who is controlling the dba? Do they have too much control?

> I think you'll have bigger problems in the meantime, as the cost >of porting is about re-writing code that uses proprietary features >(or uses similar features like indexes in different ways), not >about the stored procedure syntax.  

I have written many applications in many languages on many platforms with many databases. Seldom are these features needed. The few that are different the are used - handled by my ORM.

May 13, 2009 6:53 PM
 

Mark said:

@Harry - I agree with you on maintenance of stored procs. How do i do OO and IoC and AOP? Just looking at a basic stored proc takes time to figure out what it does because there are no separation of concerns. So, if complex logic doesn't belong in the db, then what is the point of SPs? Very little effort, if any, is saved by doing "sp_some_proc..." versus "select x from y ...."

May 13, 2009 6:59 PM
 

Mark said:

@Paul - it seems you assume that we are "application developers" because we don't like stored procs and because we do app dev. Well, we do both. Honestly, there is no such thing as a database developer - unless you are writing a dbms. People who are "database developers" are app devs who deploy their code to a dbms.

It is from experience that we know it is difficult to maintain SPs.

A good developer IS a good developer. The problem is that most developers are barely "good". Try taking a COBOL [app} programmer and make them a Java [app] programmer.  Seldom are they good. You at best end up with JOBOL. So taking an "app" dev and making them an "db" dev - same issue.

>here’s nothing easier to code against than when a good database .developer writes a good database abstraction

Then you have never used a good api in a good IDE.

>For my application, all the logic is pushed down into the data and >handled by set-based queries.

Which, unless you are a top tier dba (count them on one finger), will be like BMWs - expensive to build and built to stay expensive.

>Does EVERY report, EVERY ETL process, and EVERY application now >AND IN THE FUTURE access the database through the abstraction >layer?

Yes. You don't bypass layers. To me, the application consists of ALL the parts. The database (not the db engine itself) is NOT a separate entity.

>If not then you’re in trouble own the road.

Hmmm. How far? 5 years? nope. 10? 15? 20? 30?

We have people doing it your way. Every change is difficult and time consuming. My way - changes are easy and i can add a new UI easily.

I am speaking from experience, I have built T-SQL and PL/SQL SPs and have had to maintain them.

If you put all your logic in the db .. what happens when you have to call a system function (i have a funny story about that). What happens when you need to do 2 phase commits to queues, content repositories and files systems?  What happens when due to SLAs you have to do write behind logic?

May 13, 2009 7:18 PM
 

Mark said:

@Alexander - "Yes. I know that the most efficient way to write protable applications is to utilize stored procedures."  It might be best way that you know. But it is NOT the best way.

"I do not think so. I think that all the best practices that we have in application programming (aka "What makes a good application developer") should apply in database programming too, "cohesive and loosely coupled" modules, the patterns, unit testing, code reusing and such. What do you think?"

I think it is VERY difficult if not impossible unless you use C# or Java as the SP language. But even then ... .

May 13, 2009 7:22 PM
 

Paul Nielsen said:

@Mark, with four posts in a row you deserve a reply. I appreciate your passion, but my experience has taught me a completely different conclusion.

Application developers think in terms of process, iterations, ui controls, programmatic flow of control, algorithms. When they do write stored procedures, they tend to be clumsy, bucket to bucket style processes that cascade, or tumble, data through multiple complex steps with numerous temp tables and cursors. I'm not assigning blame, but application developers are never trained to think in terms of solving data problems in sets. The style of good programming that you’re taught in college turns out bad stored procedures. – Especially for SQL Server since it’s more tuned for set-based queries than PL-SQL is.

Database Developers spend nearly all their time -  for years - working with complex set-based queries and writing lean efficient SQL stored procedures. Joe Celko is right when he says that hardest part of moving from being an application developer to a database developer is unlearning thinking in iterations and learning how to think in sets.

Mark, the fact that you believe that any developer is equally an app dev or a db dev, that you don't believe db devs exist, and that your experience is that stored procedures are hard to maintain only demonstrates that you've never experienced the joy of working with a professional database developer who writes a well-designed, high-performance database abstraction layer.

I’ve been working with databases since 1982. I used to also do front-ends, but the last line of app code I wrote was in VB4. Since then I’ve only developed inside SQL Server. For me the best part about computing is visualizing how to make data twist and shout so I can refactor a clumsy app-dev written proc that runs in hours into an elegant query that runs in seconds. I am the database developer you say doesn’t exist. And there are many more just like me here on SQL Blog.

May 13, 2009 8:16 PM
 

Dm Unseen AKA M. Evers said:

A very heavy and intersting discussion.

I've been a professional SQL Server developer for years, and i've seen this discussion rage on and on. What I see is on the one hand "apllication" databases developed from and for an application with little regard for the data model and the data. These can be higly flexible, with ORM layers and such. Performance on complex processing, Data quality and consistency? Just forget it. You're at the mercy of API's, programmers and architects who (sometimes) do not (want to) grasp the subtle parts of transactional consistency. OTOH I see well constructed databases that provide quality data that will last for ages because the models can be properly refactored and extended. Their frontends are usually not very sexy or advanced because that would be a CWOT.

But all is not bad. Well constructed databases can be accesed by model driven database applications (not ORM!!!). This gives you all the flexibility you need without sacrificing your data model (the opposite in fact is true). In this OO fad driver world however this is considerend a niche approach.

Also, a lot of customers do not understand or care enough, and a simple cost effective "run of the mill" application is all they want. All that "theoretical" stuff about data quality & consistency is lost on them.

May 14, 2009 7:15 AM
 

Karen Lopez said:

The natural conflict between developers and "data" people isn't just about mindsets or skills.  I believe it is primarily due to the fact that developers are measured and compensated based on "just get this one application, that does this one thing, done as quickly as possible. Optimize the hell out of it, make it scream, just get 'er done, now!"

Data folks, on the other hand, are measured and compensated based on their ability to preserve data, make it right, keep it healthy and happy, for decades or centuries not matter what someone might want to put in place to collect it, maintain it, and manage it. Anticipate any future need, store it in a way that it can answer any possible or impossible question, and make it scream "I know".

One couldn't design more different objectives to make us swashbuckle in the aisles.  It's as if management is really just hoping to see the Ultimate Smackdown LIVE! in the cubes, every day.

May 14, 2009 2:01 PM
 

unclebiguns said:

I'm an unabashed proponent of stored procedures for many of the reasons mentioned here.  Security - no direct access to the tables, maintainability/abstraction - if the data model changes I can change the SP's and the application or applications are not directly impacted are just 2 I would mention.  I also commented on this post on my blog:

http://wiseman-wiseguy.blogspot.com/2009/05/explanation-of-why-to-use-stored.html

May 14, 2009 2:07 PM
 

Paul Montgomery said:

@Paul_Nielsen "What makes a good application developer is different than what makes a good database developer."

I completely agree on this one.  I usually end up wearing both hats on projects and am always amazed how 'application' developers can't get out of the foreach mindset that works well in C#/Java/etc but brings DBMS performance to it's knees.

A rich, sproc-based, data API is worth a hundred ORM's but everyone seems to bring the data guy in too late and effectively neutered or put a mid-level DB admin into the role of a data architect (there is a difference as much as a mid level application dev != application architect).

May 14, 2009 4:29 PM
 

Anon said:

I'm jumping into the conversation a little late here, but in my experience as an intermediate web developer, the main problem I see is that there tends to be a profusion of stored procedures -- generally at least four per table.  For example, for a 'company' table, you might have companySelectById, companySearchByRadius, companySearchByState, companyInsert, companyUpdate, companyDeleteById.  When the company table changes, so do most stored procedures.  Corresponding changes have to be made in the application logic.

And in the world of the web, schemas seem to change fairly often.  

Any general tips or articles on how to manage this?

-- anon

May 16, 2009 12:17 PM
 

Bruce W Cassidy said:

Throwing stored procedures at the issue of database extensibility is no guarantee that the issue will be solved.  The focus should be around a well designed API.  So I found the title of your post somewhat misleading -- it addressed the need for a good database-level API well.  Stored procedures are really just the implementation mechanism.

I wonder if you could also generate a good database-level API using something like Queues?  Particularly if you are connecting the database to a middleware product.  And queues, like stored procedures, have been around for some time.

May 17, 2009 5:19 PM
 

JP said:

Quite an interesting read and discussion. Just to exapnd upon a few of the comments made by Amon...

"For example, for a 'company' table, you might have companySelectById, companySearchByRadius, companySearchByState, companyInsert, companyUpdate, companyDeleteById"

This comment from Amon highlights some of the challenges in stored procedure design. Should you create a different stored procedure to search by each column of the table? Probably not. Perhaps a single unified sp that allows you to search these columns would be better and reduce your workload as future needs change.

"When the company table changes, so do most stored procedures.  Corresponding changes have to be made in the application logic"

Yes this is true. One of the key benefits of using stored procedures will be the ability to refactor your database layer without impacting your application. Perhaps a single database is being used by a number of applications. By having separated your data access layer you can modify the underlying tables and stored procedures and protect yourself from breaking all the applications. Once the change is made in the database you can progressively modify your applications as time allows.

Why do I like sp's? Security and data quality. My big sell for sp's in creating a single point for inserts, updates and deletes and granting zero access to tables. Reducing the footprint of code that can modify your data is essential in maintaining it's quality.

May 17, 2009 8:54 PM
 

JP said:

Apologies Anon, I just realised I referred to twice as Amon.

May 17, 2009 8:56 PM
 

Michael said:

I see one Problem with this approach - the amount of skilled db developers.

On the other side even without the help of ORM, the amount of time needed to create or change the data access layer e.g in Java is really small.

So sprocs are just another tool in the pocket.

June 17, 2009 1:32 PM
 

Steve Kaschimer said:

I like stored procedures... up to a point. You have to be careful when using stored procedures that you don;t put TOO much of you application logic into them.

I have seen far too many projects where business logic is rolled into stored procs, and the SPs are NOT well commented, resulting in an absolute nightmare when it comes to maintainability.

June 18, 2009 12:00 PM
 

Syed Tayyab Ali said:

July 21, 2009 4:13 PM
 

frank cheng said:

Stored procedures should treated as bussiness layer.

I've seen an Oracle 8.5 with thousands of SP.

It became a dinosaur. No one can maintain it.

August 3, 2009 10:40 PM
 

Paul Nielsen said:

Frank,

well, Oracle folks aren't exactly known for their ability to avoid over-complexity.

August 3, 2009 11:06 PM
 

Robinson said:

"I see one Problem with this approach - the amount of skilled db developers"

I'm not sure about this.  I would expect (indeed hope) that any programmer who is writing against a database should at least have a good background in understanding more or less what's happening under the hood.  If he doesn't, then I wouldn't trust his application code any more than I'd trust his stored procedure writing.

I have used SP's myself, effectively creating an interface to the DB as suggested in this article.  I recently had to make a major change to one of the tables and did so easily without having to recompile and re-deploy an application to every single workstation.  So, despite what people like Jeremy Miller think (cognitive dissonance), I will continue to develop systems in this way, knowing that my interface will more than likely last longer than the applications built upon it.

September 29, 2009 9:53 AM
 

dino said:

Dynamic SQL is not as absurd as you say and stored procs are not as bad as I say :).

One thing I agree is that if you decide to work with SPs extensively, then you would better not mix the jars. Also it depends on what you are willing to do. If you want to develop a resale-able product, then stored procs are out of the question, but if you are internal IT for a big non-IT company then you should work with stored procs. In the last case, maintenance costs are not an issue as the employer already pays you so you might as well do something for the money. Also, as in internal it working for some time and planning to work for longer you already know the business rules and stuff (probably you know the database by heart)  so abstraction is not really necessary.

What is important to know though is that using of SPs is not actually software application programming, is just about data, so you'd better take into consideration that the stuff returned by your SPs to the actual software application must be well structured coherent and standardized but most of all abstract.

If you are using stored procs then the core of the maintenance will be in the db, so the software code done by the software developer must be clean, tidy stable and simple, if you deliver bizarre and unstandardized data to the programmer, he will make it work but not in an elegant manner, so if problems come from the application layer, they will be difficult to solve.

Am I right Mr. Nielsen?

November 12, 2009 2:48 AM
 

Jiho Han said:

I'm not sure if this thing is still on... but,

Can we (or more specifically Paul Nielsen) address the comment by "Anon" above?

In my experience, my biggest turnoff for sprocs is also the limitation/complexity surrounding ad-hoc queries.  For example, in my app, I need to provide a UI where the user can specify conditions (where) and columns (select).  Without dynamic sql, this is a bit unwieldy, isn't it?  The only way I can think of is to pass in list of comma separated columns for the select and another set of string parameters perhaps for the where, perhaps xml?

And I agree with the notion that a set-based thinking required for SQL development are different from what's typically required for an application developer.

I also believe that unlike Paul states, that good database developers are rare.  I haven't run into one for the past ten years I've been working.  There are good DBAs who can keep a SQL server running but that is a different set of skills from designing databases and writing stored procedures.

One last thing I'd like to mention is that I'm not convinced that business logic belongs in the stored procedures.  Stored procedures should be about data and data only.  I'm seeing many stored procedures that are one extreme to another.  It's either dead simple, like inserting(or CUD of CRUD) into a table, taking parameters and applying it as-is to the underlying table, at which point, I'm thinking why bother?  Now I'm having to maintain this mindless number of stored procedures that don't really provide value to me.  The other extreme is stored procedures that are way too complex and heavy (think your monolithic app with entire code in Main() method), containing way too much intimate knowledge of the business processes and logic, a nightmare to understand and maintain.

I am thinking, isn't there some kind of middle ground?

February 27, 2010 12:17 PM
 

Paul Nielsen said:

Anon,

If I understand correctly, you're questioning if procs makes it more difficult to manage change. From my experience, the most difficult change to manage is when the schema should be refactored and the schema is tightly coupled to ad-hoc SQL that’s spread around a few hundred thousands of lines of client side code. Procs make schema change manageable.

Also, for most systems, many of the standard CRUD procs can be code-gened.

-Paul

February 28, 2010 6:36 PM
 

KhadarKhan said:

In my experience, I have seen few people who (or at least with whom I have worked) are reluctant to make use of stored procedures for various reasons. Below are the few things I have observed

1. Maintenance overhead

2. Database developer/DBA also should have exposure to business requirements

3. They are from CRUD school and never used SPs before for no reason

4. They have not used any data access libraries except NHibernte or other old ORM tools

5. They don’t know much about the database except the little DML and Select command exposure

6. The applications on which they worked were with very small ones without much complex data access methods

I don’t say that all who are against SPs don’t know much about database. There are extraordinary people who have deep knowledge on databases. But database has its own place in domain driven applications and enterprise level applications and can’t stop using them just because to have skilled database developer or to avoid little maintenance overhead. There is nothing wrong in involving DBAs/database developers in implementation phase and allowing them to communicate with application programmers. After all they are the guys who initiate the actual implementation phase and they will have complete knowledge on the overall business flow. Rather running behind them for tweaking the performance after application deployment, it is better to make them part of application life cycle to avoid unnecessary side effects. With little/no efforts they can understand the business flow for a specific requirement and start coding to give what we want. There could be some maintenance overhead or other minor drawbacks but those are nothing compare to encapsulation, security, performance, ease of use etc. All the modern ORM tools (for instance DLINQ or LINQ to SQL, Entity Framework) which are in market are providing full support with rich debugging, intellisense, easy maintenance, TDD for executing SPs and UDFs without any additional overhead.

I have been working as .Net developer since long time and I am quite happy with the way the database server technology is being evolved day by day to yield more sophisticated and scalable results. In my career I have developed few enterprise applications and without the SPs our life would have had very tough to build the complex business that was scattered throughout the application. Of course the same could have done in front end itself but with lot of coding effort and time. Instead of adhering to “No to Stored procedures” policy and thinking hours and hours to implement an alternate approach, it is better to go with the SPs and let database handle it for us. If there is any situation where traditional SQL doesn’t fit business needs to be written in front end, SqlCLR will solve the problem :)

March 3, 2010 3:53 AM
 

Andrew Smith said:

I have to agree with pretty much everything in this article...However, there are times when the database schema is just too dynamic for SQL and stored procedures. IN such cases the only option is a DAL - but these really are limited and more often than not, still utilise the good old SP to some extent..

March 8, 2010 3:53 PM
 

Tim said:

I think using stored procedures is good if you don't get too carried away. Stored procedures tie you to a database technology, just like using vendor specific database features.

Also, using stored procedures just causes you to use more processing from a very expensive box.

Offloading that processing to a second box would prevent you from needing to upgrade your database server as quickly.

Companies like Oracle and Sybase love stored procedures. I wonder why?

March 30, 2010 6:59 PM
 

Terminator said:

I think any respectable developer will agree that a data abstraction layer is a must. The problem I see here is in this opinion:

"The data architect’s answer to why use stored procedures is that T-SQL is only language that you KNOW will last as long as the data."

T-SQL can last forever or it can die tomorrow when your company decides to support multiple databases; or sometime when Microsoft decides to replace T-SQL with, I don't know, maybe T-SQL.NET? So to be fair we can't be sure that T-SQL will last as long as the data, and for some (or many), T-SQL has died about 8 years ago with introduction of a proper object-oriented language (C#, VB.NET, etc).

The reason why you think languages have a lifespan of a bug is because at one point in time object-oriented programming was discovered, and thus, new and superior languages were created. Why was T-SQL left behind as a cumbersome procedural language, when everything else moved to objects?

It is quite ironic to talk about extensibility when the language used is not extensible by nature. Abstraction shouldn't end at database layer, but it should be in every single layer of your application, inside and out. Sure, you could make a data abstraction layer with SP's on the outside, but on the inside your best option is to resort to some awkward hacks, which is more in style of novice developers. It is no secret that a language like C# is much better suited for extensibility than T-SQL.

So why still use T-SQL and stored procedures? Is it because switching to another language will all of a sudden turn T-SQL into a mere bug? In that case, what keeps you from living happily ever after with VB6 or another dinosaur language? I don't see how T-SQL is immune to being switched for another language. If anything, it is by far more probable having to switch from T-SQL to PLSQL than to switch C#/Java.

Besides KNOWING that T-SQL is also just another bug, we should also note the disadvantages of T-SQL and stored procedures:

- It's a dinosaur language, there are far better, modern tools today. The world does not rotate around T-SQL.

- No one wants to read and try to figure out what on earth does that 500 line function does. So why would anyone want to read and understand an equally large stored procedure? With the help of object-oriented design, we learned to write short and highly cohesive functions. Not so with database developers, as they are still using an old and unsuitable language for the task.

- Mixing application logic and database access goes against sound architecture and fundamental principles like separation of concerns.

- You cannot support multiple database without rewriting all your procedures. And if you need to support multiple databases at once, you have to maintain and develop a version for T-SQL, a version for PLSQL, and a version for each additional native language of each supported database.

- You need to use an excuse that there is no need to support multiple databases or that there can never ever be a need to switch databases. That need could possibly be existent today, but since your code is so tightly coupled to your database, no one in their right mind would even consider switching databases as an option.

I think a much better way of implementing a data abstraction layer is through your application code, using a mainstream language and an ORM tool like NHibernate for .NET (or Hibernate for Java). This way you don't have to write SQL, the tool takes care of it, and you can refactor your database as needed. The disadvantages listed above do not apply. Furthermore, you can write unit tests and design a rock solid software architecture without compromising it like you would if you strictly write stored procedures. You would still use stored procedures, but only as a secondary measure, where there is a demonstrated valid reason to use one.

May 4, 2010 8:45 PM
 

sp_for_me said:

There are two kinds of developers.

One that uses stored procedures, and the other that will eventually wish he had. ;-)

September 17, 2010 8:15 AM
 

Jask said:

I have read every part of this discussion and as a fairly novice VB.NET developer (by comparison to what most of you will be here) I remail absolutly neutral to the whole thing. I feel all the discussions thus far have lead to a point of none conclusion. I cannot make my mind up about where I should head as an application developer in relation to SP.

I suppose when it comes down to it the company I work for now uses SP and the previous one I worked for didnt. So now I will use SP whereas previously I wouldnt. Its not really a choice a developer gets to make unless in a more senior position or at least a position where their voice will carry above the screeches of management in the design meetings.

Yes there are two kinds of developers. Those that listen to their bosses and do what they are told; and those that are fired.

December 13, 2010 10:40 AM
 

John said:

Debat goes on and on.

I found interesting Stored Procedure Generator or Creator.

http://www.tools4sql.net

March 18, 2011 7:04 AM
 

Evik James said:

The author's is that stored procedures puts the real work on the database where it belongs and not on your in the hands of your "coder of the week".

As a web developer, I wish a lot of the sites that I inherit had used stored procedures instead of queries written within a page. It's much easier to work on queries and data manipulation within the database and work on displaying data in the program that displays data. Combining the two jobs makes for confusion.

August 3, 2011 7:07 AM
 

SP Against My WIll! said:

The main problem I see with Stored Procedures is an extension of what Anon said.  He said every table needs a couple of selects, an insert, and update and a delete.  And I would agree with that.  But even then, every Select and Update are different and can have their individual clauses constructed a variety of ways.  So even though, you only need 4 columns from your select, you use the general purpose select and get back all the columns or end up writing another new one that does almost the same thing.

Stored procedures force you to either proliferate or generalize your access.  So I think they are only good for well defined and frequently used queries against stable databases.  Something you would add to a mature system perhaps.  We have to use SP for all our data access and we have 500 lines proces with 25 case switches and selects of 16 columns and believe me it's madness.  And none of them do exactly what you want.

That's why I like Linq.  You query only what you need and get back only what you need.  It's a precise query language as is SQL.  The whole idea behind SQL is to write exactly the right query and get back only the data you need.

February 27, 2012 7:38 AM
 

Zarate said:

Its so difficult trying to find a reputable search engine optimisation business nowadays, just going to do it in-house

I think

Had to add you to my reading bookmarks, keep up the interesting posts!

October 19, 2012 5:18 PM
 

Steward said:

Precisely what are people's experiences regarding job progression in the security field? I might gradually like to end up operating in the police force and I'm contemplating whether I

could just proceed directly into that or test the waters with something lower.

Has anyone started out in basic security and ended up working with the authorities?

October 21, 2012 10:57 AM
 

Andrew said:

What are people's thoughts about using stored procedure calls when you have to manage hundreds, or thousands, of databases?  We provide a SaaS offering where each customer has a discrete database.  I spent hundreds of thousands of dollars retooling the web code to get it multi-tenant (so we don't have a different code set for each customer).  I have a data access layer (written with the latest "fad" dev environment from MS).  After all of this, I now have an engineer who is creating hundreds of stored procs for all of the data access (that is called by the data access layer).  Seems like if I had problems managing which version of web code each customer had (prior to multi-tenancy), I'm going to have the same problem managing all of the stored procs and function calls across individual databases (based on the version of software they are using).  No problem in a thick-client world where my clients are responsible for updating their database when they get a new version, but when I'm responsible for all of the databases & upgrades, I'm thinking common DLLs tied with the code seem like the more extensible solution...

Thoughts?

November 2, 2012 6:11 PM
 

Sergei Sheinin said:

I am developing a new programming language platform for databases that allows adding programming logic to database without altering its schema. Actually programs, functions and tables are added using DML.  For more: http://sprout.internetsite.eu

November 25, 2012 8:35 AM
 

outdated said:

Why changing old db models is huge effort? You can probably create thousands of different type of reports with some automation but changing business logic and returning/handling new information plus migrating old data is big effort. Which one of them becomes simpler by keeping business logic in Stored-Procs compared to keeping it in middleware?  

You need to return new information with changed business logic in both cases. However you may be able to handle variety of logic in middleware compared to stored-procs. Even with best of db-architects and db-programmers available writing logic in stored proc is still much more difficult than writing in any higher level language.

Now imagine you had only database and data (no stored procs) and the business logic was in say c/C++ or whatever language after C (not cobol), how difficult it is to port it to new language or enhance the business logic. How many programmers can program complex logic in C, C++, Java, C#?

Even with expert DB architects, maintaining stored procedures full of loads of business logic is a nightmare. Because stored procedures try to do something that a database server doesn't need to do, which is keeping business logic. Business logic itself is a loose term, it is simply logic, keeping logic is not the main responsibility of data-base servers this is adhoc-responsibility for which they have tried hard to put up, believing that someday somebody will relieve them eventually.

Secondly, the data can be old, but if it was easy to change the interface as and when needed why won't you like to have that flexibility? Higher level languages make it much more easyto change both logix as well as interface to accommodate changes.  

What is the main use of database: to keep data and provide CRUD efficiently. ORMs are already fairly good, they can compete with stored-procs in most general scenarios, but eventually you will not need stored procedures at all, because theoretically you should be handling business logic and higher level abstraction of business/application at levels much higher than database server.

Think about these two facts:

1. If you implement your business layer in 3 decade old C++ or four decades old C, you can still use that interface both in Java and C# and perhaps in a language that will come 2 decades from now.

2. In present times you talk about exposing things using services, so you can have internal service dealing with your core business logic, who then cares whether the service is over c/C++/Java or XYZ language, as long as it is accessible through standard interface?

December 19, 2012 9:27 PM
 

marke54805 said:

The printed page has been around for a very long time... and we still reach for it.

January 17, 2013 9:49 AM
 

NhatNguyen said:

I have Store SQL :

alter proc [dbo].[BKGetInvoiceNew]

(

@Control int,

@checkno nvarchar(20),

@RVC nvarchar(10),

@FormDay nvarchar(15),

@ToDay nvarchar(15)

)

AS

BEGIN

declare @sql nvarchar(2000)

set @sql =’SELECT CheckNo,TableID,TotalBase,SubTotal,TaxTotal,Discount,STaxTotal,CloseBy,NGuest,CloseTime,PONumber,PaymentMode,PaymentInfo,NChild,TableNo

from SMILE_POS.dbo.Invoice where 1=1′

if @checkno ”

set @sql = @sql + ‘and Checkno =’+@checkno+”

if @RVC ”

set @sql =@sql + ‘ and RVC =’+@RVC+”

if @FormDay ” and @ToDay”

set @sql = @sql+’ Convert(DateTime,Cast(IMonth as varchar) + ”/” + Cast(IDay as varchar) + ”/” + Cast(IYear as varchar),101) between ”’ + cast(@FormDay as varchar) +”’ And ”’ + cast(@ToDay as varchar)+ ”

–PRINT @sql

EXECUTE (@sql)

end

I can not call this store SQL in winform, can you help me call this store? Thanks

July 3, 2013 12:51 PM
 

Nick said:

What a great discussion! Thanks Paul and everyone. The reason for my comment is to try and up this page in google's result set because it's packed with great info.

December 31, 2013 11:22 AM
 

Mark said:

I am an Oracle developer that has recently moved into DBA land. The purpose of this move is to enhance my development skills and not treat the database as a black box.

I have been working with databases since v7 of Oracle and a bit of SQL server 2000. I am amazed that I am one of the few people that seem to advocate the use of Stored Procedures in the last few years - everyone else prefers ORM and abstraction in one of the middle tier layers.

I am in danger of reiterating other pro-sp comments so forgive me but this is a hot topic for me. I constantly hear people say sps are bad without seeing benchmarks and tangible evidence to back it up.

Business logic is split into two sections IMO - data and presentation. Data logic can be seen as the physical data model including PK, FKs and other types of constraints. A data model on its own cannot enforce application logic so it follows that this also belongs next to the data. I hear people saying that CRUD can be handled very easily in ORM and I agree that SPs that are written to just do this are a waste of time - basically just a wrapper for dml. In my experience I have seen that middle tier logic seems to favours row by row (slow by slow) processing instead of processing sets.

Stored procedures come into their own when they perform an atomic business transaction perhaps reading and writing to many tables whilst handling concurrency and consistency for a specific database implementation. All this is encapsulated and provided by data developers who know the set based language of the RDBMS and data model. They can also be used to provide security so direct access to the data tables it's prohibited.

Most of the middle tier applications that I have seen use caching to avoid the increased network round trips between the db server and the web application if the BL is coded in the middle tier. What happens if the data changes in the database without going through the application? Is that possible? We'll of course it is provided a correct username and password. What about legacy systems that may need to interact with the data in the future who can't use a web service or interact with the middle tier?

Finally I hear that database independence is a key motivator for not using SPs. In fact having generic code means that specific vendor database features cannot be used - generic alternative code I.e. ANSI is either slower or not actually doable. I have come to the conclusion that the only way to have true db independence is to have a well defined interface between the db layer and middle tier. The middle tier calls the SP using say JDBC and reacts appropriately. It does mean that there is a code base for each db implementation BUT each db  is implemented differently anyways - think "read consistency" and record locking in Oracle.

Before anyone flames me, this is just my experience and am a db person so perhaps a little biased. I am interested In re-education :-)

March 12, 2014 6:56 PM
 

IntegralSun said:

I have nothing against using stored procedures for ETL and other back end jobs.  Using stored procedures even in applications can be a viable strategy of building more secure applications (granting permissions at the stored procedure level can greatly simplify the task of controlling access to database objects).

Stored procedures are a tool that boasts many valid user stories.  But what I find too often the case is that when you rely heavily on one tool, then all too often, all your problems start looking the same (hammer/nail blinders).

Regardless of whether you make heavy use of SPs or not you must refactor your data model to work well with either your business domain model or with your SPs (or both).  A poorly designed E/R model will cripple your development efforts.

I prefer to use all the tools at my disposal and find it much easier to leverage reuse when my business rules are encapsulated in the business tier in my n-tier architecture.  I seldom want to mixed DML statements with conditional business logic in my stored procedures.  Why would I do that when I can get achieve much better separation of concerns doing otherwise.

April 2, 2014 4:45 PM
 

Ricardo said:

Great Article. Thanks!

July 3, 2014 3:43 PM

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement