Transact-SQL Cookbook

Preface

SQL in general, and Transact-SQL in particular, is so deceptively simple that few people take the time to master it. Many of the SQL books currently on the market contribute to this state of affairs by not taking their readers very far beyond the simple SELECT column_name FROM table_name WHERE conditions pattern of writing SQL SELECT statements. In this book, we hope to take you far beyond the level of writing simple SQL statements and open your eyes to the full power of SQL and Transact-SQL.

For many years, SQL has been the domain of large corporations using high-end databases such as Oracle, Sybase, Informix, and DB2. Microsoft’s entry into the market with Microsoft SQL Server brought Transact-SQL to the workgroup server and desktop level, thereby broadening the market for Transact-SQL programmers. More systems are being programmed in SQL and Transact-SQL, and if you’re a Transact-SQL programmer, you’ll want to leverage Transact-SQL to its fullest.

Getting the most out of SQL and Transact-SQL requires a particular way of thinking. SQL is a set-oriented language in which you focus on end results, not on the procedures used to accomplish those results. If you’re used to thinking procedurally, this shift to set-oriented thinking can be jarring. The SQL solutions to problems that are easily described procedurally are often not obvious. So why bother with the set-oriented SQL solutions? It’s true that Transact-SQL provides you with procedural capabilities, but use of those capabilities is no excuse for not utilizing the core SQL language to its fullest. SQL solutions are often magnitudes more efficient than the corresponding procedural solutions.

Why We Wrote This Book

We have many years experience working in SQL environments, and over the years we’ve developed, or stumbled across, a number of useful SQL design patterns that we apply to problems that we encounter in our day-to-day work. Knowing these patterns gives us an edge and allows us to accomplish things with ease that might otherwise be difficult.

Looking back over our careers, we remember the “magic” of discovering a new and innovative SQL-based solution to a programming problem. There was the sudden flash of light and a rush of adrenalin as a whole new vista of possibilities opened up before us. It’s our hope that we can share some of that magic with you. For that matter, we each learned a few pieces of magic from the other while collaborating on this book.

Audience for This Book

We have two target audiences in mind for this book: those who have just recently learned SQL and those who have been using SQL for several years. This is not a learning-SQL type of book. We assume that you have a working knowledge of the basic SQL statements: INSERT, UPDATE, DELETE, and SELECT. We also assume that you are passingly familiar with Transact-SQL’s procedural capabilities.

If you have recently learned SQL, then you know the basic statements. Next you need to learn how to “think SQL” to creatively apply SQL to the programming problems you encounter daily. This is difficult to teach; the creative application of SQL is best learned by example. The purpose of this book is to provide nonobvious examples of SQL being used creatively to solve everyday problems. You’ll be able to apply our patterns to your own work, and hopefully you’ll be inspired to discover creative solutions of your own.

This book isn’t just for newcomers to SQL. We think an experienced SQL programmer will discover at least one new technique in this book. We have even learned from each other as coauthors by reading over each other’s recipes.

Which Platform and Version?

All the recipes in this book were tested for compatibility with SQL Server 2000. Most of them have also been tested on SQL Server 7. Some recipes use the new ANSI join syntax supported by SQL Server 2000. Those recipes will require some minor modifications for them to work on older SQL Server releases.

While not as commonly encountered as it used to be, Sybase also uses Transact-SQL. Sybase’s Transact-SQL is not 100% compatible with Microsoft’s Transact-SQL, but we’ve coded the recipes so that, in the vast majority of cases, they will also work for Sybase.

Structure of This Book

This book is divided into eight chapters. You can read through all eight chapters sequentially, or you can read only those chapters that pertain to problems you are solving at any given time.

The only exception is Chapter 1, which you should read first no matter which other chapter you read next.

• Chapter 1, introduces the concept and use of a Pivot table. This recipe is in a chapter by itself, because we use the Pivot table pattern in many other recipes throughout the book. This is a must-read chapter.

• Chapter 2, focuses on the core of SQL’s functionality: set manipulation. In this chapter, you’ll find techniques for performing the set operations you learned in elementary school, such as difference, intersection, and compliment, using SQL. You’ll also find techniques for returning the top-n values from a set and for implementing various types of aggregation.

• Chapter 3, shows you how to implement lists, stacks, queues, arrays, and matrices using Transact-SQL. Such structures are often considered the domain of other programming languages, but it’s sometimes advantageous to implement them at the database level.

• Chapter 4, shows you several useful techniques for dealing with hierarchical data from SQL. Hierarchical data presents some special challenges because SQL is optimized for relational data. Readers who have been in the industry long enough will recall that relational databases replaced hierarchical databases. Nonetheless, hierarchical data exists and we must deal with it.

• Chapter 5, demonstrates the ins and outs of working with time-based, or temporal, data. Temporal data is frequently encountered in the business world, and failure to recognize the subtleties of querying such data is a common source of errors. In this chapter, you’ll learn about granularity and about SQL Server’s built-in support for temporal data. You’ll then find a number of recipes that show SQL being used creatively to solve various time- and date- related problems.

• Chapter 6, deals with the problem of logging changes and implementing audit trails. Learn how to implement an audit-trail mechanism using Transact-SQL, how to generate audit snapshots of a given point in time, and other related techniques.

• Chapter 7, speaks to the issues involved with getting data into SQL Server from external sources. You’ll find recipes for importing data into “live” systems, for validating imported data, and for dealing with master/detail data.

• Chapter 8, is a mini-course in statistics. You’ll see how to use SQL to calculate means, modes, medians, standard deviations, variances, and standard errors. You’ll also learn how to implement moving averages, calculate correlations, and use confidence intervals.

Wherever possible, recipes build on data from previous recipes in the same chapter, so reading through a chapter should be fairly straightforward. If you want to experiment with just one particular recipe, check the beginning of its chapter for a description of the example data used in that chapter, or download the recipe-specific script files that create example data from this book’s web page (http://oreilly.com/catalog/transqlcook ).

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Is used for filenames, directory names, and URLs. It is also used for emphasis and for the first use of a technical term.

Constant width

Is used for examples and to show the contents of files and the output of commands.

Constant width italic

Is used for replaceable elements in examples.

Constant width bold

Indicates code in an example that we want you to pay special attention to.

UPPERCASE

In syntax descriptions, usually indicates keywords.

lowercase

In syntax descriptions, usually indicates user-defined items, such as variables.

[ ]

In syntax descriptions, square brackets enclose optional items.

{ }

In syntax descriptions, curly brackets enclose a set of items from which you must choose only one.

|

In syntax descriptions, a vertical bar separates the items enclosed in curly or square brackets, as in {ON | OFF | YES | NO | TRUE | FALSE}.

...

In code listings, ellipses indicate missing output that is not critical to understanding the example and that has been removed in the interest of not taking up inordinate amounts of space in the book.

Tip

Indicates a tip, suggestion, or general note.

Warning

Indicates a warning or caution.

Sample output in code listings has been adapted to fit the size of the book. Strings that contain a lot of spaces have been shortened, and numeric values that extend over two decimal places have been either rounded or truncated. These changes create more readable output and maximize the amount of information we can pack into a sample listing.

About the Code

The main purpose of this book is to explain concepts and ways of solving problems in Transact-SQL. Some recipes might seem superficial and unnecessary; however, they are important for demonstration purposes.

The code is often fragmented. We strongly encourage you to try to understand its purpose before you use it in production code. There is almost no error-handling included. We wanted to convey each idea as clearly as possible.

We wrote the recipes from a programmer’s perspective and do not include a DBA’s viewpoint. We don’t want anyone to use this book as the sole source of information for their SQL systems. Delivering a proper database system is much more than just programmer’s work, which should be clear to anyone in the business. Programmers, designers, and DBAs should work together to design databases properly, program code, and adjust indices and other mechanisms to deliver optimum performance.

This book is an attempt to assist SQL programmers and to bring another experienced view into perspective. Most of the recipes have been tried in practice in real-world systems, and the recipes work. Other recipes have been added to complete the chapters and the topic; this fact does not diminish their importance.

Comments and Questions

Please address comments and questions concerning this book to the publisher:

O’Reilly & Associates, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

[phone] (in the United States or Canada)

[phone] (international or local)

[phone] (fax)

We have a web page for this book, where we list errata, examples, or any additional information. You can access this page at:

http://www.oreilly.com/catalog/transqlcook

To comment or ask technical questions about this book, send email to:

[e-mail]

For more information about our books, conferences, Resource Centers, and the O’Reilly Network, see our web site at:

http://www.oreilly.com

Acknowledgments

We would like to thank O’Reilly, our favorite publisher, for their trust in us and their investment in our project. Particularly, many thanks to Gigi Estabrook, our first editor, who had the courage and vision to start this project and bring us together. Robert Denn picked it up, when Gigi decided to pursue other challenges in her life. He gave us space and time to develop our ideas and brought this project to a successful end.

Invaluable comments from Wayne Snyder, Kevin Kline, and Baya Pavliashvili, our tech reviewers, improved this text. They found mistakes that we had missed even after we had read each chapter several times. They also provided several good suggestions for clarifying the text.

From Ales

I hope that this book will complement the existing opuses of outstanding authors like Joe Celko, David Rozenshtein, Anatoly Abramovich, Eugine Birger, Iztik Ben-Gan, Richard Snodgrass, and others. I spent many nights studying their work, and I learned almost everything I know from their books. As I’m writing these lines, I’m aware that for every night I spent discovering their secrets, they must have spent 10 nights putting their knowledge into a consistent and readable form. It is an honor to be able to give something back to the SQL community.

This book could never have happened without Jonathan. In the beginning, I was looking for somebody who could correct my English and reshape the text into a readable form. However, after reading the first few pages from him, I knew I had hit the jackpot. Jonathan is an outstanding SQL expert. What seemed, in the beginning, a fairly straightforward read-and-reshape-into-proper-English task became the full-sized burden of creating a new book. We constantly exchanged ideas and new recipes. I am proud to have him as a coauthor and even prouder to gain a new friend. In his first email he sent me, he had a line in the signature, which I’ll probably remember for the rest of my life: “Brighten the Corner Where You Are.” Well, he did. And it tells everything about him.

I would also like to mention a few people who touched my professional life and influenced me significantly one way or another: Tomaz Gaspersic, Primoz Krajnik, Marko Hrcek, Anton Imre, Robert Reinhardt, Professor Sasa Divjak, and Professor Kurt Leube. They were, and still are, my dearest colleagues, friends, and mentors.

I could never have completed this book without my family and, of course, without Helena. She was always there for me. When we went on vacations and I dragged along a suitcase full of books, cables, and a notebook computer, she calmly read her novel. She didn’t say a word. Many times when the sun was shining outside and I was staring at the computer, she often came to me: “You have to have a walk!!!!” When she was vacuuming and made me lift my feet to clean under the table, to prevent suffocation from dust pollution, she just gave me a kiss. She didn’t say a word. Then in the evenings when sitting in our living room, she always said she believed that everything would be fine. I didn’t. She did. She was right. And she was always there.

As I reflect on this project, I’m taken aback by the amount of work involved. Consider this: over 500,000 characters, each retyped at least twice, 2 years of writing, uncountable hours and nights spent over it. This works out to about 1,300 characters typed each day. The smallest revision number (the number of times a chapter has been revisited, reread, and changed) is 8, and the largest, 14. Conservatively speaking, we had to reread an average chapter 10 times before we were happy with it. This sums to 5 million characters read. A huge burden to go through — 2 years of dedicated work by two experts — and yet, in computer terms, only 500 KB read 10 times over. Having finished such a project, and after doing the previous calculations, I find it fascinating how easily we, in the database industry, talk about megabytes, gigabytes, and terabytes. And yet, in the end, it feels good to do such an exercise once in a while. It makes one humble; it made us better.

From Jonathan

I’d like to start off by thanking Ales for bringing me into his project to write this Transact-SQL Cookbook. Ales is an excellent programmer, and I learned a lot from him while collaborating on this book. Most of the recipes in this book are in fact his, though I did manage to contribute a few of my own.

I also wish to thank my neighbor and good friend Bill Worth for loaning me his copy of How to Lie With Statistics so that I could refresh my memory as to the difference between modes, medians, and means.

Finally, as always is the case with my books, I owe a great debt to my family for putting up with my long hours researching, writing, and experimenting. My wife Donna especially deserves my thanks not only for being more supportive of my writing career than I can possibly deserve, but for her consistent support during the many career vicissitudes that I’ve experienced during my lifetime.

There’s an interesting anecdote I’d like to share from back when this book first got started. Ales is from Slovenia, and I’m from Michigan. When I was first contacted about this book, it so happened that I was vacationing in the small town of Baraga in Michigan’s Upper Peninsula. The town is named after Bishop Frederic Baraga, a well-known Slovenian priest who traveled and ministered widely along the Lake Superior shoreline in the mid-1800s. Because he often made winter journeys of up to 60 miles on foot, Father Baraga became widely known as the “Snowshoe Priest.”

In addition to his work as a missionary, Father Baraga became fluent in the local language and wrote the first English-Ojibwa Dictionary and Grammar; a work that is still in use today. With that, he invented written language for the Ojibwa and preserved their language forever. He also wrote a book about the history and life of native tribes in which he describes the story of a native girl who saved the life of Englishman John Smith. 150 years later she became a movie star. Her name was Pocahontas.

Tip

Learn more about Father Baraga and his influence on Michigan’s north country by visiting http://www.exploringthenorth.com/bishopb/shrine.html.

As if the coincidence of my being in Baraga, Michigan at the beginning of this project wasn’t enough, I actually moved to Michigan’s Upper Peninsula during the course of this book. Settling in Munising, I found myself a mere 24 miles from the town of Traunik (http://www.traunik.com), home to a thriving Slovenian community for many years during the early and mid-1900s. Having Ales as a coauthor, Traunik as a neighbor, and living under the shadow of Father Baraga makes me feel strongly connected to this tiny country of Slovenia, which perhaps someday I’ll even be lucky enough to visit.