SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Pros and Cons of Stored Procedures

I saw this mentioned on Twitter, and watched it while doing some light editing and scheduling. It’s from TechEd 2009 in LA, and includes a number of MVPs and consultants and even a Microsoft SQL Server program manager.

It’s a video, running about 55 minutes, on The Pros and Cons of Stored Procedures. It’s a panel, and they take some questions from the audience, but they kind of run through the pros and cons of why they use, or don’t use in some cases, stored procedures. Having been on a couple of these in the past, and while we have an outline, it’s mostly off the cuff discussion.

I thought it was very thought provoking, listening to people talk about where and why they use stored procedures. The different points of view shouldn’t make you choose to use them or not use them, but to listen to the reasons why they choose stored procedures in many circumstances.

If you write SQL code, or argue with developers about whether to use them, you (and your team) should watch this video, and then have a rational discussion about your differences.

The one surprising thing I heard was from Adam Machanic, a stored procedure advocate, who said that in reporting systems he doesn’t often use them. He wants people to ad hoc be able to query tables. I disagree, but that’s my experience. I’ve had better luck responding to report requests back in the day when tools were less capable.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by rijiboy on 22 May 2009

Steve, I left this message on LinkedIn, when someone asked about this question. I am just going to copy that here as well.

Besides the Execution plan, and layer of abstraction (encapsulation) the best thing is portability. I have following reasons as well

Your database itself (I assume) has a routine backup plan, so your stored procs are retrievable in case of a system crash.

You can embed stored procs inside other procs and believe me its much cleaner then use GOTO or even sometime nested if's or case statements.

As a DBA if I get a call about Database running slow, I can not only find the culprit proc, but also either recompile it or maybe modify it ( I wish though ) to optimize performance, besides doing other things to find the cause.

From security point of view I can allow application user access to few stored procedures instead of entire DML on database, or even entire DML type operation on certain tables, lets consider if a stored procedure was suppose to update only three columns of a table, and I grant update to entire table to application user, the developer writing the query may be able to update four or more columns, cause I cant control column level security (maybe I can and I havent read in detail), a bad query can also destroy execution plans of other optimzed procs or queries

Also to control SQL attacks/injections, a DBA or secuirty auditor can quickly expose potential issues inside a proc, cause thats available to them easily, whereas if its embeded in application as a DBA I may not even have knowledge of what classes/functions to search for such code, without the proper help of development team, and what is the correct logical flow of those TSQL queries, logical flow can make or break security, think if your buffer over flow happened before a potentially non-paramaterized query or after.

Posted by Steve Jones on 22 May 2009

Good points, and I tend to agree. I think stored procs need to be used, they don't add a lot of overhead. and they provide a lot of advantages.

Posted by tfifield on 22 May 2009

I'm also a big fan of stored procedures.  If the front end is limited to using stored procedures only, it makes for a cleaner application with fewer chances to dirty up the database.  

If you don't need or want a separate business layer then all business rules can be encapsulated in the stored procedures.

Reports can more easily be optimized performance wise also.

Todd Fifield

Posted by dmattison on 22 May 2009

I totally disagree. I only use stored procs for extremely heavy database update operations. A robust datalayer encapsulated into a dll is just as transportable as any stored proc and MUCH, MUCH easier to debug and step through.  Source control takes care of disaster recovery.  I like the flexibility of being able to write transportable ANSI SQL that can be used in ANY SQL compliant database instead ob being locked into one vendor.  You can still use their optimization tools and plans top tweak the sql but the debugging ease and speed inherent in NOT using stored procs much outweighs any perceived advantages.  

Posted by JJ B on 22 May 2009

I appreciate the tip about the video.  I'm going to try to check it out.

Posted by Mik Clucas on 25 May 2009

I think there are situations where Stored Procedures are the right way to go, in Transactional DB's where stored procedures are the only way to ensure smooth running.

In a Reporting or Data Warehouse application they remove the flexibility of the design and are contra-productive.

After all one of the prime reasons for the existance of reporting & DWH applications is to give business users the ability to questions in a fast changing business world.

In any case the tools DBAs use must be applicable to the environment, Optimised stored procedures in Transaction applications; Indexes & materialised views in Reporting apps.

Posted by Steve Jones on 26 May 2009

I don't think a data layer is more portable. It requires rebuilding and redeployment, which is no easier than, and potentially more disruptive, than compiling stored procedures.

DR, source control, they can be handled in SQL as easy as with Visual Studio. You just need to configure things. It's easy to say one is easier than the other if you are more familiar with it.

Posted by Allen McGuire on 17 June 2009

I'm all for stored procedures.  rijiboy hit most of the key points so I won't reiterate them, but I like having the ability to, when I get time, examine the query plan for stored procedures and make sure the database (indexes, etc.) is optimized.  Stored procedures often lend to reusable code as well.  If you have four platforms that all call "getCustomer", why not have them all call the same stored procedure... for a DBA it's just more manageable to have all the database calls/queries in one place.

I can almost guarantee that if you allow developers to write the queries, they will not take performance into consideration, will likely query more data than is needed (select *...), will not fully qualify the object names (added overhead), etc.  Four developers would write the same logical query four different ways.

As a DBA, I take the procedure cache and buffer cache into consideration - ask a developer what those are... deer in the headlights?  ;-)

Leave a Comment

Please register or log in to leave a comment.