In Re: A default architecture – without stored Procedures
I wrote this today in response to Patrik Löwendahl’s post: A default architecture – without stored Procedures . I figured that I might as well post it up here, too, so that people can comment on my rantings.
You’d be hard pressed to get me to concede that a data access scheme based on stored procedures is a bad idea. In the absence of that, buy a good ORM tool.
I’m going to take a stab and responding to each of your points because I think it’s good to get a dialog going on these kinds of things.
Pain Point the First: Version Management
I’m not sure what you mean by this. It’s fairly trivial to version control SQL files, however I’m guessing that you aren’t having any difficulty with adding plain text files to version control. So, I’m going to make a guess and say that this has something to do with quickly determining which version of development code is in the database. This can be somewhat tricky. It becomes necessary to use some kind of build/migration system similar to the one used in Ruby on Rails where you have a schema_info table with a version column that is incremented or decremented by an automated tool. At this point, a high level of DBA discipline is required to prevent changes from being made in production that are not present in source control and vice versa.
Pain Point the Second: Two Code Bases
Again, I’m not sure what is meant by this. You’re absolutely right, maintaining stored procedures does require two skill sets. In an ideal world there would be a distinct separation of skills between database and application developers. While basic to intermediate SQL can be handled by most developers, venturing into high performance/advanced SQL requires a distinct skill set that requires years of experience and conscious practice.
Pain Point the Third: Business Logic
I’ve said this a large number of times over my career as both an application developer and database developer: business logic does not belong in the database. By default T-SQL will compile and store the first execution path it encounters in a stored proc. Let’s say you have a procedure with two branches, one performs a simple insert and the other performs a complex operation that can benefit from a compiled execution plan. If the simple insert statement branch is the first branch to be executed, it will be compiled and the other, complex, path with be performed as an ad hoc query unless the stored proc is ALTERed and the second execution path is executed. If you need complex logic and you have SQL 2005, you can use CLR stored procedures.
Pain Point the Fourth: Testing Stored Procedures
While I’m definitely not an expert on the subject of testing stored procedures, I have read numerous resources on the subject of testing stored procedures. Adam Machanic devotes some time to it in Expert SQL Server Programming. I’ll leave this topic to the experts.
Pain Point the Fifth: Writing Trivial SQL
When you’re operating, or attempting to operate, a high performance, high access database, there are no trivial data access calls. Granted, for the majority of data access scenarios (SELECT a, b, c FROM xyz WHERE param = @param), a stored procedure could be described as development overhead. This is a point that we agree on. However, I see the point that many DBAs have - if they want to change the underlying schema for performance/storage considerations, they should be able to do so as long as they provide developers with the same set of outputs as before, given the same inputs of course.
Pain Point the Sixth: Stored Procedures aren’t Dynamic
Nor should they ever be. The point of a stored procedure is to access data as efficiently as possible. In the event that you have multiple potential join combinations, you will need multiple stored procedures. Not to sound insulting, but the demand for multiple potential join combinations sounds like, to me, a poorly planned data access scenario. There are ways to dynamically generate WHERE and ORDER BY clauses using T-SQL and parameterized procedures/queries.
Ultimately, of course, the purpose of a stored procedure is to increase performance, granularity of data access, and provide an additional layer of security on top of the data. A data access solution based around stored procedures isn’t for every one, but it’s always good to know the reasons why you should be using it.
