The Guru's Guide to SQL Server Stored Procedures, XML, and HTML

This is an advanced coder's book. Geared toward the intermediate-to-advanced developer, it is designed to help you reach the next level as a SQL Server stored procedure developer. This book assumes you already know how to write Transact-SQL queries and how to build stored procedures. This book offers little in the way of beginner-level instruction. It is aimed at developers with moderate-to-advanced skills who want to become better stored procedure programmers - developers who want to go to the next level of software craftsmanship as it relates to Transact-SQL, stored procedure programming, and XML.

The opening epigraph of my previous book, The Guru's Guide to Transact-SQL, is a quote by my friend, the renowned author and lecturer Joe Celko, regarding the importance of unlearning procedural programming in order to master non-procedural languages such as SQL. At the time, I agreed with Joe that writing Transact-SQL code in a procedural manner was the single biggest impediment to writing good Transact-SQL code. When I penned the first Guru's Guide book, I firmly believed that attempting to code in Transact-SQL in the same way that, say, C++ is written was the main reason that people who are competent developers in other languages often run into difficulties when they try to code in Transact-SQL. Their whole approach was wrong, I reasoned, and that's why they had problems. I believed that they weren't thinking like database programmers; that instead, they were thinking like traditional programmers, which just won't work in the world of database programming. So I thought.

Since then, I've had a change of heart. I once read an interview where Eddie Van Halen said that a band's music albums are snapshots of where the band is at a particular point in time (musically and otherwise). That's true of books, too. The Guru's Guide to Transact-SQL is where I was in 1998 and 1999 when I wrote it. Since then, my thinking on the relationship between procedural programming and Transact-SQL has evolved. Why? Well, let me tell you a little story...

Sometime during the two years I spent writing the first Guru's Guide, one of the book's technical reviewers wrote asking me about an article I'd written several years before for my column in Sybase Developer's Journal that demonstrated some tricks with bitmasks in Transact-SQL. He wanted to know if I could send him a copy of the article because he was doing some work with bitmasks and wanted to use one of the techniques I'd written about. I searched high and low for the article, but couldn't find it anywhere on the various computers I use. The machine on which I'd written the column had long since been retired, as had any backup tapes I might have had of it - if they had ever existed in the first place.

Finally, a search on the Internet turned up the ancient piece, and I forwarded it to the fellow who'd asked for it. With some amusement, I sat at my desk and read through the article for a few minutes (most writers really like to read what they write, no matter how old it is and no matter what they tell you). I wondered to myself: what ever possessed me to try these bit twiddling techniques in Transact-SQL of all things? Why do I think of things like this in the first place? I wondered what drove me to make discoveries like the techniques about which I'd written the article. I reasoned that if I could figure out how or why I went about making discoveries like this, perhaps I could unlock the secret of innovation itself, or, at least, how I occasionally stumble upon it. Perhaps I could move to the next level as a Transact-SQL coder myself.

I thought about it for a few days and finally realized why I came up with ideas like the bit-twiddling technique. The conclusion I came to was that, as much as I would have liked to have believed that I thought of them all on my own, many of my "discoveries" in Transact-SQL were due to what those in academia refer to as crosspollination. It was because of, not in spite of, my experiences with other languages that I'd come up with a number of the innovative coding techniques I'd discovered over the years. Most of the discoveries I'd made in Transact-SQL had grown from seeds planted in my brain by my work in traditional programming languages such as Pascal, C/C++, assembly language, and various others. It occurred to me that there were likely scant few true innovations originating in the relatively pubescent world of Transact-SQL programming. After all, languages like C and Pascal had pre-dated Transact-SQL by many years - languages like COBOL and BASIC by even longer. We don't see many new problems in the world of computing. What we see are new solutions to the same old problems. People were solving these problems long before Transact-SQL or SQL Server even existed. Surely, most of the discoveries to be made in the field of software engineering have already been made. Surely, those of us innovating in Transact-SQL are merely standing on the shoulders of the giants who came before us.

In their book, The Pragmatic Programmer (Addison-Wesley, 1999), Andrew Hunt and Dave Thomas make the bold recommendation that people who aspire to be better programmers should learn at least one new programming language per year. I'll make the same recommendation here. If you want to master Transact-SQL stored procedure programming, you should master programming itself first. Programming, coding, software engineering - whatever you want to call it - requires many years and many languages to master. Like the martial arts apprentice who must first master a number of separate martial arts before he can attain an advanced belt, a programmer who would master Transact-SQL should master the various aspects of programming in general before he can hope to master Transact-SQL itself.

The big-picture perspective and the cross-pollination that interdisciplinary work affords is the chief reason universities require schooling in fields outside of one's major focus of study. By studying the way that other fields do things, you see the many similarities and differences between your field and theirs, you gain deeper insight into those similarities and differences, and you learn to apply what you've discovered about topics completely outside your field to your own work in ways that have perhaps not been tried before. In other words, you learn to innovate. By embracing the broad view of the world the university espouses, you begin to understand your field more holistically - you begin to understand its philosophy more intimately, and you begin to grasp where it fits in the grand scheme of things.

I think the same kinds of insight can be gained through studying languages and techniques outside the realm of SQL Server. Were it not for my work in assembly language and my study of the works of masters like Steve Gibson, I might never have stumbled upon the bit twiddling techniques about which I wrote that ancient column. If not for my work in Pascal and Delphi and my study of code by gurus such as Anders Hejlsberg and Kim Kokkonen, I wouldn't have come up with a good number of the techniques I've developed in Transact-SQL over the years, including many of the data manipulation routines you'll see in this book. My research into common design patterns in Transact-SQL was inspired by the book Design Patterns by Erich Gamma and company, which I keep close at hand for my work in languages such as C++ and Object Pascal. The book The Practice of Programming, by Brian Kernighan and Rob Pike, has largely influenced my insistence on idiomatic programming. I'm a stickler for testing because of books like After the Gold Rush by Steve McConnell and Extreme Programming Explained by Kent Beck, and I'm a big proponent of the value of re-factoring because of works like Refactoring: Improving the Design of Existing Code by Martin Fowler. Many of the algorithms discussed in this book were inspired by those in Donald Knuth's three-volume work, The Art of Computer Programming and by Jon Bentley's book, Programming Pearls, as well as by many others.

None of these books are about Transact-SQL, per se, or even SQL Server. None of them demonstrate techniques that can be easily translated to a set-oriented language like SQL. They are, however, about programming, and my work in other languages is responsible for my knowledge of them. I have benefited - immensely - from interdisciplinary work - from the crosspollination between my work in Transact-SQL and my work in other languages and from the perspective such work affords a programmer. And I think you will, too.

So, rather than preaching that you must give up your sinful procedural programming ways in order to reach the nirvana that is Transact-SQL mastery, I will instead encourage you to explore other languages and other tools. Pick one per year - it can be any language or tool in which you're not already an expert - anything from Visual Basic to Delphi to Ruby to C#, C++, or Java. Come up with a few projects to undertake with your new language, ideally - but not necessarily - things that tie it back to SQL Server in some way, and then dive in. Buy the books you need, read the newsgroups, do the research, build your software. You will be surprised at how much you learn about programming, and how much you grow as a developer through the experience.

Then, sometime throughout these research projects, think about how you might apply what you're learning to your work as a Transact-SQL developer. How does SQL Server employ this or that language element that is featured by the tool you're studying? How does it implement functionality that you've found particularly helpful in your new language? How do they differ? How does, say, automation differ between Transact-SQL and Delphi? Given that Transact-SQL, like all of SQL Server, is written in C and C++, what language nuances can you trace to its origins?

After a few years of this, and after you've gained the perspective that forays into the world outside of SQL Server can afford, you will be well on your way to having the tools necessary to truly master Transact-SQL and stored procedure programming. You will appreciate software engineering as a discipline; you will love programming for its own sake.That is the key to mastering any programming language, including Transact-SQL.

So, my apologies to Joe Celko notwithstanding, I no longer believe that procedural programming is the single biggest impediment to good Transact-SQL coding. It is quite the opposite. Not truly grasping a language's strengths and weaknesses - the things that make it unique - is the single biggest impediment to building good software with it. And you can only gain the perspective necessary to accurately assess those strengths and weaknesses through interdisciplinary work and cross-pollination. In Transact-SQL's case, its strength is set-oriented development, its chief weakness is top-down programming. That doesn't mean that you can only write set-oriented programs with Transact-SQL or that writing procedural Transact-SQL code is only for the foolhardy. After all, they call them stored procedures for a reason. It just means that your style of coding will be different with Transact-SQL than it would be with, say, Visual Basic. That's not just true of Transact-SQL, it's true of many languages - many have nuances and idioms that make them unique. You wouldn't code in C++ the way you code in VB, either. Use the right tool for the job. Play to your tool's strengths and away from its weaknesses. Become intimate with those strengths and weaknesses by mastering not only the tool, but also software development in general. Purpose to become a master programmer, not just a stored procedure expert.

And about that conversation we had over dinner in San Francisco, Joe: I still think C# is the best thing to happen to programming in a good long while.

Ken Henderson

January 2001

0201700468P10152001