SQL Server – When to Use Stored Procedures

Stored procedures are bad, they're often slow and approximately as efficient as ordinary client side code.

[The speedup is usually due to the way the client and stored procedure interface is designed and the way transactions are written as short, focused bursts of SQL.]

Stored procedures are one of the worst places to put code. It breaks your application into two languages and platforms according to rules that are often random.

[This question will be downvoted to have a score of about -30 because many, many people feel that stored procedures have magical powers and must be used in spite of the problems they cause.]

Moving all the stored procedure code to the client will make things much easier for everyone.

You'll still have to update the schema and ORM model from time to time. However, schema changes are isolated from ORM changes, allowing some independence between applications and database schema.

You will be able to test, fix, maintain, understand and adapt all those stored procedures as you rewrite them. Your app will run about the same and become much less fragile because you're no longer breaking into two different technologies.

ORM's are not magic, and good database design skills are absolutely essential to making it work.

Also, programs with a lot of client SQL can become slow because of poor thinking about transaction boundaries. One of the reasons stored procedures appear to be fast is that stored procedures force very, very careful design of transactions.

ORM's don't magically force careful transaction design. Transaction design still has to be done just as carefully as it was when writing stored procedures.

Business logic doesn't go into the database

If we're talking about multi-tier applications, it seems pretty clear that business logic, the kind of intelligence that runs a particular enterprise, belongs in the Business Logic Layer, not in the Data Access Layer.

Databases do a few things really well:

1. They store and retrieve data
2. They establish and enforce relationships between different data entities
3. They provide the means to query the data for answers
4. They provide performance optimizations.
5. They provide access control

Now, of course, you can codify all sorts of things in a database that pertain to your business concerns, things like tax rates, discounts, operation codes, categories and so forth. But the business action that is taken on that data is not generally coded into the database, for all sorts of reasons already mentioned by others, although an action can be chosen in the database and executed elsewhere.

And of course, there may be things that are performed in a database for performance and other reasons:

1. Closing out an accounting period
2. Number crunching
3. Nightly batch processes
4. Fail-over

Naturally, nothing is engraved in stone. Stored Procedures are suitable for a wide array of tasks simply because they live on the database server and have certain strengths and advantages.

Stored Procedures Everywhere?

There's a certain allure to coding all of your data storage, management and retrieval tasks in stored procedures, and simply consuming the resulting data services. You certainly would benefit from the maximum possible performance and security optimizations that the database server could provide, and that's no small thing.

But what do you risk?

1. Vendor lock-in
2. The need for developers with special skill sets
3. Spartan programming tools, overall
4. Extremely tight software coupling
5. No separation of concerns

And of course, if you need a web service (which is probably where this is all heading, anyway), you're still going to have to build that.

So what is typical practice?

I would say that a typical, modern approach is to use an Object-Relational Mapper (such as Entity Framework) to create classes that model your tables. You can then speak to your database through a repository that returns collections of objects, a situation that is very familiar to any competent software developer. The ORM dynamically generates SQL corresponding to your data model and the information requested, which the database server then processes to return query results.

How well does this work? Very well, and much more rapidly than writing stored procedures and views. This generally covers about 80% of your data access requirements, mostly CRUD. What covers the other 20%? You guessed it: stored procedures, which all of the major ORMs support directly.

Can you write a code generator that does the same thing as an ORM, but with stored procedures? Sure you can. But ORMs are generally vendor-independent, well-understood by everyone, and better supported.