For a longtime Microsoft SQL Server users such as myself, one would ask: What is there to reconsider? It's long considered to be a best practice in the Microsoft world to at least have 3-4 stored procedures (sprocs) for every table usually an insert, update, delete and a simple select statement.
It may surprise some that people outside of the SQL Server world (Oracle, DB2) do not use sprocs nearly as often and they apply them differently. I will lay out my argument calling for the end of this 'best practice'.
First let's dissect the main arguments for this best practice, they include the following: performance, security, reusability & separation of duties.
Performance argument surrounds the assumption that stored procedures are precompiled. However this is not always a benefit SQL Server's own Books Online says:
When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.
If you read this you will understand that only the part of the query that does not change is precompiled and if the parameters change performance may suffer. SQL Server stores execution plans for any query sent to it, therefore there is no advantage in this department. If any performance difference is negligible. Also if you are dynamically generating SQL in your sproc and executing it, you will also have no performance advantage. Sprocs also give developers a container to write cursors which are a bad practice (in SQL Server) since they create temp tables. Additionally the most common stored procedures are usually the most trivial SQL statements (insert, update, delete).
Security argument is based on two sub arguments: granular security access & SQL Injection attacks. The granular security argument is that you don't give users access into the table, you only give them access to execute a stored procedure. My response to this is so what, the user's still have access to insert. Really what is the risk (odds) of a user finding out the database security credentials, understanding the database, and issuing a proper sql statement. Odds are if the user is capable of this then they are capable of executing your sproc. Furthermore many pieces of your security have to fail you for any of this to happen. I call this a long shot.
The SQL Injection is actually a misconception based on the fact people commonly issue parameterized queries as opposed to constructing a string. You can use sprocs and still have SQL Injection vulnerability if you are doing something like this:
cmd.Execute("exec InsertCust 1, 'hello'");
To really secure your database consider firewalling your database to only allow connections from the servers that may access it, such as web server, app tier or reporting system.
Reusability argument is only relevant if you don't have proper layers in an application to allow reuse from your app tier or business objects. Unless you are letting other applications access your database which is generally not recommended.
Separation of duties assumes that the job of your RDBMS is to be a repository for SQL code. This wasn't it's vision and there's a stronger argument that SQL statements fall more inline with business process and business logic therefore they belong there. If you implement a good ORM strategy then you don't even have to worry about 'hard coding' SQL into your application. Furthermore your app tier and business objects are more likely to have unit tests therefore regression testing at the object level is seamless.
The case against stored procedures
Stored procedures are procedural, not object oriented therefore their extensibility for reuse is minimal. This is why a sproc only architecture is a failed one, it's time consuming for little to no return on your investments. It's common for people to rewrite an entire sproc purely because the original one did not work exactly for their purpose or they do not understand it.
Followup: In June I was at Microsoft TechEd and I reiterated my argument against stored procedures to a member of the SQL Server development team at Microsoft. Afterwards I asked if this was correct and his words: "Yes, I tell people not to use stored procedures."
To which I responded "it would be nice for you to tell people this."
"People are going to do what they are going to do" was his response. My translation is simple: the opinion of your peers do not necessarily reflect the best practices of the vendor. Additionally times change, best practices change, performance metrics change and Microsoft doesn't typically speak with solidarity when it comes to best practices or design decision. i.e. as long as you are running on Microsoft platform with Microsoft language and tools they don't care what you do.
You have to accept that change is inevitable in life and prepare yourself for trends that you may be uncomfortable with or unaware of. I'll close with a comment from the creator of ORM framework Hibernate.
Stored procedures are essentially a nonrelational view of a relational database ... my view, currently, is that the goal of an object-relational mapping tool should be to map between tables and objects, not between objects and "some other stuff."
Scott White
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
or now on iTunes!
- Windows Media Podcast - 43.0MB WMV
- iPod Video Podcast - 34.2MB MP4
- MP3 Audio Podcast - 6.9MB
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.
