Beginning Microsoft® SQL Server® 2008 Programming

Chapter 7. Adding More to Our Queries

When I first started writing about SQL Server a number of years ago, I was faced with the question of when exactly to introduce more complex queries into the knowledge mix — this book faces that question all over again. At issue is something of a "chicken or egg" thing — talk about scripting, variables, and the like first, or get to some things that a beginning user might make use of long before they do server-side scripting. This time around, the notion of more queries early won out.

Some of the concepts in this chapter are going to challenge you with a new way of thinking. You already had a taste of this just dealing with joins, but you haven't had to deal with the kind of depth that I want to challenge you with in this chapter. Even if you don't have that much procedural programming experience, the fact is that your brain has a natural tendency to break complex problems down into their smaller subparts (sub-procedures, logical steps) as opposed to solving them whole (the "set," or SQL way).

While SQL Server 2008 supports procedural language concepts now more than ever, my challenge to you is to try and see the question as a whole first. Be certain that you can't get it in a single query. Even if you can't think of a way, quite often you can break it up into several small queries and then combine them one at a time back into a larger query that does it all in one task. Try to see it as a whole and, if you can't, then go ahead and break it down, ...