Good reasons:
- SQL lags woefully behind general purpose programming languages in terms of capabilities and IDE support.
- There are some things you just can't do in SQL and it's easier to have a definitive repository of business rules rather than splitting the arbitrarily between SQL and a scripting/programming language.
- Painful error reporting - Maybe it's just me, but I have a hard time taking a SQL error and automatically parsing it and throwing the appropriate error based on what went wrong (foreign key constraint, error with trigger, data type issue, reserved word/character, etc).

Bad reasons:
- Most web developers suck at SQL
- Most web developers don't "think in sets" which is what is required to get the best from SQL.

Neutral reasons:
- It makes it much harder to port between (say) MySQL and SQL Server, so you need to factor that into the math.

Reasons to put more code into SQL:
- Performance
- Easier to port between scripting/programming languages

Posted by Peter Bell on Aug 28, 2007 at 12:43 PM UTC - 6 hrs

I think we see *lots* of these apps that are all SQL and thin objects.

Or are you asking why ppl don't use stored procs more? My answer would be that SPs are too hard to version and deploy properly, compared to SQL in code. You can run multiple versions of code easily against the same database but with SPs you have give them unique names or manage multiple schemas etc etc.

Posted by Sean Corfield on Aug 28, 2007 at 12:45 PM UTC - 6 hrs

@Sean - Yes, I was thinking about putting the code inside the database.

@Peter and Sean - You both mentioned really good reasons why we don't see it, but I'd like to focus on the lack of good tooling. Do you think we don't see the tooling because no one wants it (ie, few people develop that way), or that we don't want to do it because we don't have tools to support us?

I was working with a lot of DB stuff recently, and the first thought I had was "what a crap way to organize code" and then, "is there a plug-in that I could use to organize this better?"

I wonder if there are products out there we just don't know about. Anyone know of any that help solve the tooling issues Sean and Peter mentioned? I'd love to hear about it.

Posted by Sam on Aug 28, 2007 at 04:18 PM UTC - 6 hrs

To me it all comes down to the fact that certain proogramming tasks cannot be accomplished effectively in SQL. So no matter how good your intenetions are, you will wind up with an app whose biz logic is partially contained in SQL and partially contained in code.

Couple that with crap (comparatively) IDEs and there you are.

The new SQL2k5 tools are neatly available via a VS.NET type interface. Also VS integrates working with generic and strongly typed datasets quite nicely if you are into that sort of thing (strongly typed that is).

The capability of C# or Java cannot come close to being approximated by SQL of any flavor. Until that gap is filled I can't imagine the biz logic within SQL concept working well.

Posted by Doug on Sep 11, 2007 at 03:47 PM UTC - 6 hrs