The Art of SQL

...he might write a book like "The Art of SQL". There are a lot of SQL books on the market - some of them very good. So why would there need to be a new book written about the subject? You know, when I first picked up this book I thought the very same thing. But then, after reading through it, I'm convinced that we need Stephane Faroult's "The Art of SQL." This book deals with SQL performance through the lens of Sun Tzu's venerable treatise "The Art of War." For those who do not know it, "The Art of War" is an ancient Chinese military treatise written during the 6th century BC by Sun Tzu. It consists of 13 chapters; each one is devoted to a single aspect of warfare. Many view it as the definitive work on military strategies and tactics. This ancient book is still being read and followed to this day. For such a work to still be viable thousands of years later is a credit to its usefulness and importance. It is also noteworthy that there have been several attempts by various authors to translate Sun Tzu's tenets into modern day applications such as business philosophy and sales training, but this new book is the first that I know of to translate these concepts into database programming and SQL. The author actually uses the exact same title chapters for The Art of SQL that Sun Tzu used in The Art of War. Amazingly enough, the tactic works. Consider, for example, the chapter titled "Laying Plans," in which Faroult examines how to design databases for performance. As anyone who ever built database applications knows an improperly designed database can be the biggest impediment to flawless application performance. The chapter titled "Tactical Dispositions" covers the topic of indexing and in "The Nine Situations" the author examines several calssic SQL patterns and how best to approach them. This book is not for a novice who wants to learn SQL from scratch. The author assumes the reader is conversant with SQL as he describes how to apply SQL in a practical manner. If you can't code an outer join or don't know what a nested table expression or in-line view is, then this is not the book for you. Neither is the book a list of SQL scripts that you can pluck out and use. Instead, The Art of SQL skillfully manages to explain how to properly attack the job of coding SQL to effectively and efficiently access your data. The book offers best practices that teach experienced SQL users to focus on strategy rather than specifics. As the author points out, "You need knowledge, you need skills, and you need talent. Talent cannot be taught, but it can be nurtured. This is what most strategists, from Sun Tzu to modern-day generals, have believed." And now Faroult's book, The Art of SQL, is there to help nurture your talent.

Imagine a cookbook with no recipes but after you read it it helps to become a better cook. This book does not specifically tells what to do. It does not have codes or technical jargon. It gives you an understanding. The book reads well, it is not full of technical references but it is not to say this book is for someone who does not know the workings of a DBMS. The author draws a relevant parallel between designing a database and waging a war. I enjoyed the quotes thoughtfully peppered in the book. Do me a favor and do not get it...in case we are going after the same job

Probably most database solution developers and DBAs have picked up their knowledge of the subject as they have needed it, rather than in a formal and structured (so to speak) method. And even those that have had a structured education probably learned a lot more while in the field. And so there are always gaps. Gaps in technique, but also gaps in the why of many things that I, for example, took for granted about large DBMS's and SQL. This book handily fills in those gaps. It assumes a moderate to advanced foundation in SQL and DBMS, and then takes off from there. It is mostly prose with some code and SQL sprinkled throughout, but if you have a foundation, you can flesh out the technique. It is like listening to a graduate level lecture. It is distilled wisdom more than How To, and the more you bring to the material, the more you will get from it. And every page is rich with information. I don't feel like I have wasted my time on any one page, as I often do in the how to manuals. Definitely an advanced piece.

The Art of SQL is a truly unique book. In sharp contrast to many other database books on the market, this one does not endeavor to provide an exhaustive SQL reference guide, a low-level vendor-specific DBMS implementation description, or a cookbook-style collection of FAQs. Instead it explains, in incredibly straightforward and clear language, how to think about SQL, schema design, and DBMS operation in general and apply that knowledge to real-world situations. It provides simple mental models for the inner workings of most modern database systems along with concrete examples of how these mental models can be used to speed up queries and design better-performing schemas. Throughout the book many commonly encountered design patterns query requirements are discussed, such as tree or hierarchy-based data structures, name/value pair tables and various common types of selection filtering and aggregation. For each of these, multiple implementation options are described and evaluated, with the pros and cons of each approach explained. This book assumes the reader is proficient at forming SQL statements, and thus spends its time exploring how restructuring tables and indexes or reforming queries can affect performance. There is a stronger focus on schema design considerations rather than query structure optimization, which I really appreciated because many SQL references focus almost exclusively on the latter. There are also a number of rather complicated real-world examples sprinkled throughout the book. These are carefully analyzed using the concepts presented in their respective chapters. The idea here isn't that the reader might encounter these exact problems in their projects, but rather to illustrate the process of applying the book's concepts to a concrete problem. This book is for the most part database vendor agnostic, but still manages to do a great job of explaining the inner workings of databases in a clear, concise manner. In my opinion, it goes to just the right level of detail in explaining things like physical table structure, indexing implementations, locking and the query optimizer. The explanations are simple enough and general enough for mere mortals to internalize, yet still provide a powerful framework for thinking about and improving database performance. Chapter Four, which discussed relational theory and how it relates to SQL, was one of my favorite chapters and serves as a perfect example of this book's ability to provide easily understandable theoretical concepts and tie it in to database implementation and its consequences on query performance against an example table structure. The Art of SQL is not a beginner's book, and is not an Oracle reference. What is does, however, is provides a conceptual underpinning between these two extremes that will enable database administrators to effectively formulate battle plans against whatever challenges lie in their path. I wish this book had been around when I was learning databases!

SQL being a declarative style language is intuitive in simplicity, and yet, the instincts are not enough when you enter the labyrinth of complex problems. From an application developer's prospect, I'm familiar with different mindsets of programmers. Those who despise writing SQL beside CRUD statements and consider it a lowly languages, to those who religiously believe the business logic should reside in business objects (hence the name) and the lingua franca of database should not contain any of the rules despite the performance gains. Then there is SQL zealots who prefer to write virtually everything in SQL and would like to use high level algorithmically sophisticated languages as sheer callers. Nevertheless, SQL is ubiquitously essential part of a developer's everyday life and "The Art of SQL" by Stephane's Farlout the best thing after SUN-TZU's "Art of War" in the SQL warfare. "The Art of SQL" is theory and practice blended; once you start reading it, it becomes something in between Knuth's Art of Computer Science with C++ annotated reference manual. This 350 page book is divided into twelve chapters and written as a pseudo war-manual. The topics of chapters are as follows. * Laying Plans (Designing Databases for Performance) * Waging War (Accessing Databases Efficiently) * Tactical Dispositions (Indexing) * Maneuvering (Thinking SQL Statements) * Terrain (Understanding Physical Implementation) * The Nine Situations (Recognizing Classic SQL Patterns) * Variations in Tactics (Dealing with Hierarchical Data) * Weaknesses and Strengths (Recognizing and Handling Difficult Cases) * Multiple Fronts (Tackling Concurrency) * Assembly of Forces (Coping with Large Volumes of Data) * Stratagems (Trying to Salvage Response Times) * Employment of Spies (Monitoring Performance) Like said in the book, "A skillful developer should aim for response times proportional to the number of rows returned." the author emphasizes on performance, discusses tackling bad SQL code, how to do analysis on problem at hand and then how to think it out in SQL way. The discussion varies from indexes, query optimization, concurrency and physical schema representation. SQL examples in the book are more of analogies than real world examples, they are focused encouraging the reader to think and map these to his or hers real life development experience. Like author says in one of its notes "Much of the difficulty of good SQL programming lies in seeing how a problem can translate, not into succession of queries to a database but into very few queries.", the core theme of the book is to talk about SQL, not about any particular database. Therefore please don't buy it as a art of SQL Server or Oracle but as a pure SQL (not T-SQL or PL/SQL either) guide. The book is clearly written and easy to understand however, mind you, it's not a freshman's cookbook guide to structured query language. You have to be in trenches to understand Stephane metaphors and they kind of grow on you. For