Coders think in terms of code. It's the old saw about the hammer. When that's the only tool you've got, everything starts to look like a nail.

Coders, of course, have more than one tool at their disposal. We have worlds of tools. But coders do like to code (see Andy's recent post [0] if you don't believe me). It's what we like to think we do best.

Before I get too far along here, let me clarify what I mean by code. I argued for a broad definition of source code in a recent comment [0], and I'm not backing away from that really, but maybe clarifying a few terms. Let's say, for the sake of argument, that code is that which is written in a programming language such as BASIC, C, Java, and the rest. I won't attempt to define it further. And let's say that what I called "source code" in that other post might better be called simply "source." Only three paragraphs into my post and I've already gone off on a tangent. Typical.

The point I want to make here is that coders like to work in familiar territory. Present a coder with a problem and his or her mind will immediately go to work on solving that problem in the software language of his or her preference. For example, if you present me with a problem, I'll probably start right away with a possible solution using Java and some of my recent favorite technologies, such as Hibernate, SOAP, and maybe JMS. Java is my favorite hammer and I like to use it.

But many many problems are not best solved by building a mighty computer program, as I recently learned.

When our focus is on the coded solution we often forget the other tools in our toolbox. There's HTML, for example. Straight HTML without code is sometimes the right solution. There's also the application of an existing tool. If you want an easy-to-update website for internal company documentation, you'd be better off installing a wiki than trying to invent something.

And there's also good old fashioned, straight up SQL.

No, SQL is not code and if you're thinking of it as code then you're thinking of it the wrong way around. Code is a linear set of commands with logical and looping structures. SQL is all about sets.

Modern stored procedure languages such as PL/SQL and T-SQL allow developers to lose sight of this sometimes. It's fairly easy in these languages to create a cursor and loop through your data, apply logic, perform updates, and so on. If you approach SQL as a coder, this may be your first impulse as you approach any complex SQL problem. But when you do this you are really missing out on the true power of thinking in sets, something that can be very difficult to do, but can be incredibly powerful when done right.

I'm currently working on what might be considered a rules engine for a client of ours. We even call it a rules engine. It's not really a general-purpose rules engine, though. The overall function is to analyze data in a complex set of tables based on certain rules, and output the results to another table to give you an easy way of analyzing and reporting on the information you need.

My first impulse was to either create a solution using Java classes and Hibernate, or to look at an existing rules engine, such as JESS. However, for this application, neither solution was appropriate. We did not need a rules engine that could do anything. There were clear parameters of what our rules needed to do--look for information in a particular database about particular objects. And a solution in Java, especially one using Hibernate, would have simply been far too slow for the amount of data processing our engine would have to do.

It took me a while to come to grips with this last fact, but finally I did accept it. The objects that the rules will be applied to number in the hundreds of thousands, and growing. There will eventually be 300 or more rules. A Java approach would, of necessity, need to loop through every one of those hundreds of thousands of objects and apply every one of those 300+ rules. Processing would likely take days or even longer.

But a straight SQL solution lets us apply our rules without looping. This is possible because SQL is all about sets. By carefully constructing each rule as an INSERT SELECT FROM statement, we can harness the power of SQL to make our application run much much faster than would be possible with a coded solution.

Figuring out how to really make this work in SQL was difficult but rewarding. Coders are not generally trained to think in sets. We think in loops. Figuring out how to write a really complex SQL statement is profoundly difficult when you're used to thinking in loops. When and how to join, outer join, and so on is very tricky and, for me at least, takes a good bit of experimentation to get right.

In the end (well, it's still a work in progress, but the structure is there and the thing works) we ended up with a very neat application that is elegant and very quick, considering the vast amount of data we're processing. So I've learned that it is possible to create a very nice application using nothing but table structures and stored procedures. And I've learned that straight SQL can be neat stuff too. If Java is my favorite hammer, then I'll call SQL my new favorite wrench.