Is it a best practise for a database to allow apps

Question

I've heard reasoning behind this and I was curious if others think of this as a best practise/good idea.

One reasoning is, that putting restrictions on direct access to database tables, and forcing apps/users to use SPs (Stored Procedures) to do CRUD operations will allow DBAs

1. fine grained control to approve/review SPs before moving them to production
2. avoid random queries written by developers which might cause server load/other server issues

This means the developer can not write Linq queries on tables (although Linq queries involving SPs are possible) This also means the developer has to forget about compile-time checks as well as being in total control of the data, rather principally use another language(SQL) to work with data.

I am not objecting to this, neither think this is a great idea. I am just curious to know what others think. What are any other advantages and disadvantages of this approach.

Update: As mentioned by Thomas in his answer, I tend to think that SPs are logic and using 'database programming in SQL' to do logic is okay as long as it's not 'Business logic'. Business logic needs separation, compile-time check and integration checkpoints and what not.

Answer 1

Well another obvious benefit is performance, once the SP's are compiled they will execute quicker than SQL in code. You have already stated a major benefit of SP's for larger organisations with an eye on security and control. It makes it easier for your DBA to run an eye over the SQL running against the DB in times of performance issues also as its all in the DB.

One disadvantage is portability, if you are writing a commercial application and a customer insists you adapt to their existing DB you'll have a bit more work to do with SP's than for in-line SQL.

Answer 2

Using stored procs (and ones which do not use dynamic SQL) allows the database people to performance tune and more importantly it limits access to database tables and views so that no one can change them except the dba. This is VERY important if you have a financial application and want to guard against internal fraud.

Answer 3

To use your list:

It means the developer doesn't need to write Linq queries on tables. It also means the developer can forget about compile-time checks on database access code, and let someone who knows the database be in total control of the data. And the develoer doesn't need to use another language (SQL or Linq) to work with data.

In other words, it depends on your working context.

Answer 4

This blog article presents a very good counterpoint to the "Stored Procs are always better" crowd.

http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx

Personally, I am on the fence and lean towards using them.

Answer 5

As others have noted, this depends very much on your environment.

However, even in a corporate situation, where you want to protect your mission critical data, using stored procedures for CRUD is not necessarily mandated. As noted, using a stored procedure to implement database insertion assumes the stored procedure is going to be smarter than the applications programmer. Stored procedures are logic, so the skill of writing them is the skills of a programmer. So, if you happen to have a DBA who is also a better programmer than your programmers, then, by all means get them to write stored procedures to keep your programmers honest. This might be the case some places. It is worth keeping in mind Thomas Hansen's point that using a stored procedures can result in the entire organization being locked into the logic of the given stored procedure. Again, some organization might want or need this.

Another approach is to use some stored procedures to guarantee consistency but still allow ad-hoc insertions. Depending on the database, this can allow a cleaner interface and more consistency.

Stored procedures might be a bit faster but that is unlikely to be a main consideration for a large organization's main database (and a small organization probably wouldn't want that many tiers).

If you are going to add large amounts data, things like batch files and turning transactions off are useful. But to my mind, CRUD does not imply a large amounts of data being created but rather small instances of creating, reading, editing and deleting data as is done by most user-level applications.

Answer 6

Personally, I've never found procs flexible enough for the 'R' in CRUD. I usually use views. But accessing the database through views & procs also allows you to abstract away the database tables giving the data model greater flexibility in the future ... almost like working against an interface.