Select * would build a weak abstraction layer or be an indication that there's no abstraciton layer. Abstraction is a key for extensibility. Beside, architects see everything in terms of extensility ;-)

Abstraction is the means of loosely coupling the database from the client. By defining and enforcing a contract API between the database and the client (be it a middle tier, ETL, report, or app) then both sides of the contract can freely refactor without breaking the other side of the contract. When new features are required, the change to the API contract can be agreed upon and both sides can build out to the new API, and the number of components affected is contained by the API. It's the same idea as SOA, but between the database component and the client component. And both sides of the contract need an abstraction layer, not just the client.

In contrast, a tightly coupled database – one with ad-hoc SQL directly touching the tables - is a brittle database; any change to the database structure causes a ripple effect of broken code (reports, tiers, forms, ETL, etc). Shops with tightly coupled databases spend more time figuring out how much code will break if they implement a database change than they do coding the change, or they can’t make the change at all which leads to outsourcing or switching to a competitor. I’ve spoken with a number of clients who have tightly coupled databases. I recommend changes to the schema and they say, “wish we could but we can’t; the SQL code is scattered all over in 2 million lines of VB code.”

That's why abstraction is the key to extensibility.

obviously, my attempt to migrate my career from DBA to blog comedian is failing and I should keep my day job (as if I had one) ;-)

So these two atoms are walking into a bar, and one atom says to the other,

"Hey, I think I lost an electron."

"Are you sure?" say the other atom.

"Yes, in fact I'm positive!"

I know, day job.

Hi Adam,

Andy “got it” in the first comment. The post was never intended to launch a debate on abstraction layers and extensibility. Nevertheless, you’re a friend, and I want you to understand my view even if you don’t agree.

There’s plenty of theory around the ideas of loose vs. tight coupling and SOA. But, as you know, I’m not very impressed with theory unless I can observe the benefit. So I’ll be very pragmatic in my reasoning.

If I understand your question correctly, we agree that stored procedures are the way to go, but you’re asking me to clarify why I believe extensibility is a reason for a stored procedure abstraction layer even though it means more code. Right?

Let me build a common enough example.

Let’s assume there’s a widget system and five tables store widget configuration data. Based on four criteria some widgets can be selected for some purpose. A few websites, several forms, a couple ETL processes, and several reports all use this feature adding up to 50 separate components.

A tightly coupled database would have code in each of the 50 components that accesses the five tables, does the same logic, and gets the result.  Number of routines: 50

A loosely coupled database would place the logic that selects the widgets in a proc and the 50 components would call the proc. Number of routines: 51

Now the business requires a change. The same four criteria will be used to select widgets, but now the method will be a bit more complex and will require adding two tables, modifying three tables, and completely rewriting the logic.

The tightly coupled database requires the project team to modify the schema and then find, modify, and unit test 50 routines in multiple technologies and languages.

The loosely coupled database requires the project team to modify the schema and then modify and unit test one proc. The 50 external components need never be touched. The loosely coupled design is more extensible because it reduces the redundency of code, reduces the cost of change, and reduces the risk of errors introduced by the change.

Admittedly, if the new feature requires a new parameter or a new column then the API and all components have to modified, but from my experience, a large percent of of changes involve feature changes or improvements inside the database that can be easily coded with an abstraction layer but become nearly impossible, or at least very expensive, with a tightly coupled database. And if the change request does modify the API, having the API makes the entire process much more manageable.