There are as many opinions for and against using stored procedures as there are database-related roles. I believe the position that is most qualified to speak to stored procedures is the data architect’s role. Data architecture is the role that studies the various methods, patterns, standards, and best-practices that result in databases that will, together, best serve the organization now and in the future.

Don’t confuse data architecture with data modeling. Although data architects often do data modeling, data architecture and data modeling are different disciplines.

Data architecture evaluates various database designs and implementations by several criteria: e.g. usability, scalability, integrity, extensibility, security, and availability.

Of those six basic database criteria, there’s one that stands out as the most expensive to refactor or correct later: extensibility – the ability to modify the database to meet changing requirements. I’ve seen organizations with horrible database designs, that desperately need to re-develop that database, but they can’t. The database is so tightly coupled that the idea of making a slight change to the database brings management to its knees in fear. Dozens of applications, hundreds of reports, and a score of nightly ETL and maintenance tasks are tied directly to tables in the database. At one location, it takes a six person, highly qualified team six months to just modify a column. The result of tight coupling is a brittle database.

The cost of a brittle database is enormous to an organization. On the corporate roadmap, the database is the primary roadblock. The organization can’t implement critical changes because the database can’t support those changes. The organization (both internal clients and IT) will try to work around the brittle database with additional databases in an attempt to find features. IT will try to wrap the brittle database with additional layers in an attempt to find loose coupling. But complexity breeds complexity and eventually the organization with multiple completing database solutions will try to apply a master database so there’s a single source for answers. All this takes years and millions of dollars and… never… really… works.

The only real solution is an abstraction layer that fully encapsulates the database. Every database fetch, insert, and update must go through this access layer. Just as SOA provides this encapsulation for processes, the database needs the same black-box API. If the database team wants to refactor the database to improve some feature, it’s free to do so. If a new feature is added, the database team can add that feature and modify the API. It’s very clean, easy to refactor, and the database is now an on-ramp to the corporate roadmap.

If you can agree that a logical abstraction layer is required for database extensibility, the next question is, “Physically, where should the abstraction layer be implemented?”

Many will answer that it’s implemented in the programmer’s favorite language, and unfortunately that’s probably true.

A data architect would ask this probing question, “ What’s the projected life of the data?” When I teach my data architecture seminars, or give conference talks on data architecture, I ask the question, “How many of you have data that’s five years old?” every hand goes up. “Ten years old?” a few hands go down. This progresses until eventually one guy has data that 40-50 years old.

The point is: data has a long lifespan. If data has the lifespan of an elephant, then application languages have the lifespan of bugs. Think back just five years ago, what was the hot application language used to develop DAL layers? Ten years ago? Anybody up for writing a new web page that calls the COM+ DAL layer? Didn’t think so.

The data architect’s answer to why use stored procedures is that T-SQL is only language that you KNOW will last as long as the data. A stored procedure that was written ten years ago is still just as easy to call as one that will be written today, or in the future. Anything less is building a long term solution on today’s fad, and it’s just plain foolishness.

As soon as my book is complete, I'm going to focus full-time on www.codeplex.com/nordic and an ISV app based on Nordic. In the meantime here's an email I recently received from an ISV that's testing Nordic as the database design for thier next version:

I have implemented a variation of Nordic which relies on (ID int, ServerID tinyint) keys for PK’s and FK’s, replacing the GUID’s.  I’ve added one table, which I mentioned to you a while back, a home for the AssociationID where the associationID (actually it is the composite ID, ServerID) is unique.

We ran into problems with the Nordic stored procedures since Entity Framework does not have a concept of table variables in SQL.  So I’ve taken to writing triggers on the tables and views over the associations.  The triggers use table variables inside so they are hidden from the Entity Framework.  Using triggers, as a side benefit, lets us enforce, very simply, a “never delete” rule globally, which has been a design goal for us for a long while.

Our database is a  bit of a stripped down Nordic now but I’ve populated the database over the last week and I have to say it kicks ass!!!  Using the “instead of” triggers on the views, I’ve pushed the Object table to 870,000 rows.  I made our association table be extension of Object as well, since we want the Object attributes to apply to associations just as much as they apply to entities.  46% of the Objects are 7 different classes of entity data and 54% of the Objects are class/role associations spread over 15 class/role association types.

The insert triggers are capable of creating 500,000 rows spread across 4 tables (includes object rows, class entities and class/role associations) in 2 minutes (on a machine that is 6 years old!!) starting out with no data in the model.  Our tables are tall and narrow and I did strip down the object table a little bit from what you have.  I’m not sure yet what the performance will be when we get up into the 10 million row range and add bunches of indexes.

Writing the triggers kind of sucks but the bang for the buck is good so far and I’m getting faster, better at it.  Someday, maybe Marc will incorporate some of the logic into his framework, Interacx.

And I’ve tested this model in a P2P (Peer to Peer) replicated configuration.  (The ServerID’s purpose in life is to make PK’s unique in a replicated environment.)  In all  my tests so far the data model maintains integrity when we make changes to two different database instances at the same time.

Looks like we will continue to extend this model, adding features to it, and maybe someday use it in a production environment instead of just as a research tool.

When your book reviews end we should talk on the phone some day.

I spend my life thinking strategically about data. I'm going out on a limb and making a prediction:

In two years time, a major question for every new deployment will be "Do we buy our own server, or host our data in the cloud?" and the momentum will be heavily for the cloud.

In five years time, hosting your own SQL data on your own servers will seem as obsolete as running your own dial-up BBS.

(so plan your career wisely)

- The SQL Whisperer

SQL Server 2008 Bible is draft complete, finally, and I'm cramming all day and half the night every day to answer every one of Hugo's most excellent tech edit comments. This edition will come in at about 1550 pages, I think. Several of the chapters are meatier than the last edition and this reflects my enthusiasm for SQL Server 2008. I'm much more pleased with 2008 than I was with 2005.  

I can see a light at the end, the "final drop" deadline is looming ever closer, With any luck, I'll be completely done with the book about the day before the MVP Summit.