This editorial was originally published on Aug 13, 2012. It is bein re-published as Steve is on vacation.

I ran across this piece from a developer on why he avoids stored procedures and thought it made some good arguments. The primary thrust of the piece is that ORM tools (Object-Relational Mapping) have evolved to handle most of the requirements of many applications. They also save a ton of development time since so many stored procedures written are simple CRUD type operations. 

In many cases I agree. Having developers write stored procedures is silly and a waste of time. Procedures that select a few fields, or that update a table based on a primary key are mind-numbingly simple to write, but they take time to get in place. That's time a developer isn't spending thinking about the application and logic. Plus with any ORMs and tools like LINQ, you can write one line of code and let the ORM handle all of the work of getting or storing the data. Good points, and in many cases that's correct. If you do mostly CRUD type work, this is a good reason to perhaps avoid stored procedures and let a tool do the work for you.

Unless you use a different tool. There are plenty of tools, most of them free, that will generate that CRUD code for you. A few templates or snippets will handle the front end side of the call as well, building code to call stored procedures. If you're actually typing this stuff over and over, you are wasting time.

My brain started to wander when I saw "A database should be limited to the role of a persistence layer" which is silly sounding when you move beyond CRUD operations. It completely shut off when I saw "your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database" since I think this rarely happens. If it does for you, fine, but the vast majority of apps never leave their initial database.

There are benefits in ORM tools, but you need to understand how the ORM works, what it's strengths and weaknesses are. Blindly following the basic pattern for your state-lookup-data-editing dialog for all reporting screens is a sure way to cause yourself some problems. Allowing the ORM to define your relational database, without spending some time thinking about the benefits of good database design and proper modeling is asking for performance problems, or even integrity issues. 

ORM tools are just that tools. Used well, they can perform admirably, but just as I don't use a hammer to drive a screw into wood, don't depend on your ORM handling everything database related for you in an efficient manner.