The importance of using stored procedures for all your data access used to be the first thing you would learn at database development school. Not so anymore as development tools and object mappers seem to encourage dynamic SQL to prosper under the hood. Are stored procedures a lost art or just an anachronism?

Performance

Processing power, network speeds and query optimisation have all improved to the point where firing dynamic SQL at a database server isn’t quite the kamikaze mission it used to be. In fact, performance isn’t really the selling point for stored procedures anymore.

A major benefit of stored procedures used to be that they were compiled and the resulting query plans cached for better performance. However, since SQL Server 2005 this behaviour has been extended to cover the majority of dynamic SQL statements, even providing re-usability for statements whose parameters have not been explicitly stated.

Despite this, you can still leverage a few small performance gains from stored procedures. They can reduce the number of round trips between application and server, particularly for multiple statements when using SET NOCOUNT. They also can be called with much smaller statements than more complex SQL queries so can reduce network traffic.

These performance gains are often small beer though. There are some compelling reasons for using stored procedures, but these are not related to performance.

Abstraction and maintenance

There’s a lot to be said for using stored procedures to create an abstraction between your application and database design. It helps to reduce the coupling between your data and business logic and reduces maintenance overheads by centralising data access logic. Defects can be fixed in one place and you can alter the underlying data structure without having to address any other component.

Believe it or not, it’s easier to write and maintain stored procedures than dynamic SQL. A stored procedure benefits from syntax checking at compile time where dynamic SQL errors don’t show up until run time. Any run-time problems in procedures are easier to fix too because they normally involve deploying a single script to the database environment.

Control and tuning

Allowing any SQL code to be fired at a database makes DBAs nervous. Rightly so, because it removes any control they have over how data is being accessed and makes it impossible to do effective query tuning.

Object mappers can occasionally produce pretty bizarre interpretations of queries. It’s as though writing optimised queries is becoming a lost art among developers. Stored procedures afford you greater control over what’s being executed against your database servers. Without them it becomes a lot more difficult address bottlenecks and scaling issues.

Security

You can provide a more finely tuned stored security model through stored procedures. You can assign execution rights on stored procedures without having to grant access rights to the underlying data tables. Not only is this easier to manage, but it allows you to segment security by feature rather than data table.

Stored procedures can also provide other security benefits. They can grant protection against some form of SQL injection attacks, provide input parameter checking and can also hide the implementation of business logic from people who have compromised an application. Stored procedures don’t provide complete protection or guard you against the impact of badly-written code, but they can help to reduce the surface area available to attackers.

Functionality

Stored procedure logic is richer. You have easier access to program control statements, variables, temporary tables, exception handling and cursors. There is some debate over how much application logic should reside on the database tier, but stored procedures do afford a functional richness that is not available to dynamic SQL statements.

Filed under Databases.