Piotr Rodak has posted an interesting article on the use of instead of triggers. The discussion extended to one about applications having direct access to SQL base tables.
This reminded me of the intense debates on the pros/cons of using stored procedures. These debates often centre on things such as security, performance – very rarely do they mention what I consider to be the most important reasons for using sprocs – the ability to decouple database schema from external applications allowing a more maintainable data contract to be provided.
After a number of years of use a typical corporate database will often have a number of external applications that use it eg:
- Web apps
- Reporting apps
- ETL/SSIS components to import/export data
- Linked server access
These applications often have direct access to base tables. This can make it is very difficult to react quickly to business changes. Even basic refactoring eg base table name changes – become very onerous. A change to a base table will often require changes to all external applications that access it – there is no flexibility in approach all will have to change at the same time.
Allowing direct access to base tables often means that the database schema effectively becomes ‘static’ – as change is seen as being too difficult. In my experience this can lead to data quality issues as, for example, base tables then get reused for purposes that they were not intended for.
How to get around this?
1. Enforce the principle of not giving direct access to base tables to external apps. Direct access should be limited to a small number of views. Changes to base tables can then be isolated to only those views that directly access them.
2. External access should be via sprocs – in some cases this can be extended to views. If a base table needs to be changed then existing data contracts/interfaces are more easily maintained via sprocs – for views ‘instead of triggers’ can be used. In this way external applications are decoupled from these changes.
What about ORMs?
By default ORMs often use direct access base tables. Some such as NHibernate can use sprocs – but they lose some of ‘their flexibility’ – see Ayende Rahien’s article Should you use NHibernate with Stored Procedure? In the scenario where dev teams are using ORMs and do not want to use sprocs – then the use of views can be a reasonable compromise.
What do you think?
Are sprocs the most appropriate way to allow the decoupling of external apps from databases?
Alternative view point
Last modified: 2011-05-15