For the past thirty years, I've worked in many clearing banks, City institutions and international corporates as an Application Developer. Since the development of modern relational databases, I've never come across a production corporate system that allowed direct table access to the application programmer. In most cases the Computer Manual of the enterprise specifically forbade the practice.

This is hardly surprising. as the practice of accessing tables directly makes any further development of the data architecture almost impossible, breaches security, and provides a considerable performance hit. Monitoring the subsequent, and inevitable, performance problems, deadlocks and lost transactions becomes a DBAs nightmare. And when the inevitable problems arise, the application programmers who previously argued so vociferously for direct table access will typically bleat feebly about inadequacies in the database or its Admin Team, whilst busying themselves elsewhere; for now it is the DBAs problem. Yessir!

Having said that, I am alarmed by the idea of pointing out too publicly such an obvious, proven, and satisfactory approach as having a defined interface between the database and the other application layers. This is because I derive a good income from trouble-shooting projects that take the misguided 'Live-free and die' approach of letting programmers wander about in the guts of the database.

Curiously, for the first time in my career, I recently had to design and implement a corporate-standard system all by muself. Ah, you will think, so I bet Phil allowed himself direct table access!, so Phil will break the rules for his best friend! Not a bit of it, I was as tough on myself as I would be on any novice VB programmer. This was just as well as I was able, subsequently, to make radical changes to the data model as the business made its occasional pirouette in its Requirements definition for the application, with only minimal changes to the other components of the system.

Please do not think I am biased in any way. It should go without saying that I am as entirely open-minded as the Editor of Simple Talk (Sir) and eager to be persuaded that my cherished opinion, garnered from decades of practice as a database professional are the mere foolishness of a grizzled and bad-tempered old man. I'm certainly eager to hear the arguments for direct table access.

Given the recent debates over at CodeBetter.com on stored procedures vs. ad hoc SQL, how could I not...

One great advantage of using only stored procedures when accessing a database is that one can insert access-logging into the system to tease out bugs and performance problems. Of course, the rule has to be strict: no direct table access. Then, one can insert an entry into an access-log table at the start of every public-facing stored procedure, writing the name of the procedure, the time, the spid, and the value of all the parameters. One then writes a second log entry at the exit points. When problems come up, as they always do in real life, one can then check the access-log table for performance problems, and check to see what parameters were called, and the values used. This way, it is simple to tease out problems caused by incorrect values, attempted fraud, programmers making mistakes and so on. Even better, if a problem crops up in a stored procedure causing data loss, one can re-run the procedure with the original values to mop-up. Of course, one disables logging for the production environment. Well, no you don't, because the performance hit of doing so isn't worth bothering about, and those log entries are there as a godsend when things go wrong.

It is strange that the vociferous email replies which seem to have upset both Adam and Tony have not yet been followed by any reasoned public response here that spells out the advantages of direct table access. I think a lot of us who work in the industry would like to see an educated and thoughtful argument for discarding the current industry 'best practice' of requiring application-access to be via stored procedure. I suspect that many of us are scratching our heads, wondering what it could be, and I, for one would be fascinated to know how one can maintain production standards for security whilst allowing application programmers direct access to tables. How would one pass Audit?

Posted on behalf of Joel Wilson
===============================

I read Adam Machanic's article, To SP or not to SP in SQL Server: an argument for stored procedures, and felt compelled to respond. After 20 years+ programming (Basic, Fortran, 8086 asm, xBase, VB, C/C++, J++, C#) I'm completely convinced of the exact opposite of his conclusion: Never use stored procedures.

Actually, never, is not quite accurate but I build enterprise applications using SPs very sparingly. Generally I would only use an SP when there was a specific security, synchronization/locking, or performance issue. The main reasons I avoid them are maintenance, ownership, coupling, and deployment.

Maintenance is an issue because it means that either: 1) programmers have access to the database to create/modify SPs (and the skill set to work with the underlying DBMS) or 2) DBAs must coordinate with programmers to create/modify SPs as necessary – a bottleneck in the development process. In either case the roles of the actors (developers and DBAs) are blurred. Either developers must understand the DBMS or the DBAs must be involved in application development. Conceptually, data access is a development task (since the application is the consumer), data management is a DBA task (modeling, profiling/performance, maintenance, archiving, etc.).

Ownership is an issue because it’s difficult (nearly impossible in large scale systems) to keep track of what application owns/uses what SP (more on this in deployment).

Coupling is the most interesting aspect of Adam’s article since he undermines his own premise that strongly coupled components are undesirable – a premise with which I (and most developers) agree with. By embedding you data access inside you DBMS you create a coupling of tiers (data access and data storage) that is almost impossible to separate. By creating a well-defined, loosely coupled data-access tier that primarily uses standard, textual SQL statements (I don’t like the term “ad-hoc” since it implies a last minute strategy), you can change the underlying DBMS with little effort. For security purposes, this data tier should use correct parameterization (as Adam states) to avoid SQL injection hacks. My preferred method is to keep the SQL statements in an XML file where each command has a name.

Finally, deployment. As noted above, I prefer to keep the SQL statements in an XML file that is deployed with the application. In this way the coordination of making sure that the correct SPs get deployed with the correct application is nullified. This links back to the ownership issue – impact analysis is simplified since you can easily scan you XML store for specific table/column names.

Performance was the paramount reason that SPs became a staple of DBMSs and the one area that the SP camp used to win hands down. But, as Adam notes, in today’s modern DBMSs (and operating systems -- think connection pooling) the performance difference is so small as to be unimportant in most cases.

Ron Soukup penned this paragraph...
"Use Stored Procedures Almost Always

While I urge you to think carefully before using cursors, I urge you to use stored procedures rather than dynamic SQL whenever possible. Recall ... the efficiencies that stored procedures bring in terms of not requiring the compilation of an execution plan for each execution. Plans can be reused and stay cached, available for subsequent use. Beyond the significant performance advantages, stored procedures can provide a valuable level of indirection between your applications and the database design. If your application issues a procedure like get_customer_balance and expects a result set to be returned, the underlying database can change, and as long as the procedure also changes to return the result set as expected, the application can be totally unaffected and unaware of the change. For example, perhaps you decide to denormalize your database design to provide faster query performance. The stored procedure can be changed to respecify the query. Perhaps many applications call the procedure. You can simply change the stored procedure once and never touch the application. In fact, a running application doesn't even need to be restarted—it would execute the new version of the stored procedure the next time it is called.

p 680. Inside SQL Server Microsoft Press 1997"

And who is this 'Ron Soukup' with these hot-headed ideas so contrary to those of Joel Wilson? none other than the man who led the Microsoft SQL Server Team for more than a decade. Hmmm