Not that long ago, the prevailing wisdom in developer
communities was that whenever possible, you should exclusively use stored
procedures to interface with your SQL Server.
The reasons that everybody cited were that Stored Procedures were faster
and more secure. Were these good
reasons? Of course. Since stored procedures cached their
execution plans they didn’t require the server to recompile them upon every
execution. And, since ownership chaining
allowed you to custom tailor the access that your users have to the data, store
procedures definitely helped with security.
Now, just a few years later, I’m hard pressed to find anybody
still espousing the need for us to continue using stored procedures. It seems that Microsoft is now espousing
tools like DLINQ that encourage the query language to be created in code and
executed directly against the database. Now
people are claiming that with engine improvements, the performance advantage of
pre-compiled code isn’t THAT great, and the security benefits of stored
procedures aren’t THAT important.
Somehow the convenience of sloppily writing code directly
against SQL from code is supposed to eclipse the benefits of using stored
procedures. Does it? Short term?
Quite possibly. Long Term? Absolutely not. Why would I say this? Maintainabilty. Let’s look at why.
Stored procedures are self-documenting contracts
Databases change over time.
Requirements change, features get added, and things we “should have
done” emerge over time. Eventually, a
developer will be asked questions such as:
- How hard
would it be to add “X” feature?
- Can we
change the schema to improve performance in activity “Y”?
- What
features would need to be retested if we made the proposed schema change?
In many environments, developers have no clue how to answer
these questions. The reason for this is
simple: they have no documentation on exactly what is being required of the
database. Database calls are distributed
between one or more chunks of source code, plus things like reporting services,
Excel queries from the Accounting department, and data import scripts used by
the marketing department. There is no
central source of truth as to what the actual requirements of the database
are. Off-line documentation is often
incomplete, outdated and/or non-existent.
Stored procedures, used regularly can change all of this.
By having data access exclusively through stored procedures,
the list of stored procedures becomes an authoritative guide on the
requirements of the database. Want to
know what will be impacted by changing a table column? No problem.
Just use a dependency tool to look at what depends on that table. Trace the SQL proc names back to the source
code. Viola. No problem.
The full requirements of the database are right in front of you. The analysis is easy.
Does that sound useful?
It has been for me. In the last
year, I’ve had three major projects where I needed to make significant schema
changes to a database in a short amount of time with very little tolerance for
errors. Being able to view the impacted
store procedures was absolutely critical to getting these projects done on time
and relatively free of bugs. What few
bugs were found were almost exclusively related to sections of code that
violated this rule and ran SQL queries directly from code. Not only did having procedures defined allow
me to get things done on time, they gave me the confidence to tell business
users in advance that they would be able to have their new feature they needed
in the time frame they desired. And as a
consultant, that is what gives my clients confidence that I am the best man
they could’ve picked for the job.