T-SQL Querying 2005

The books cover advanced T-SQL Querying, Query Tuning and Programming in SQL Server 2005. They are designed for programmers and DBAs that need to write and optimize code in both SQL Server 2000 and 2005.

The books focus on practical common problems, discussing several different approaches to tackle each. You will be introduced to many polished techniques that will enhance your toolbox and coding vocabulary, allowing you to provide efficient solutions in a natural manner.
The books unravel the power of set-based querying, and explain why it’s usually superior to procedural programming with cursors and the likes. At the same time, it teaches you how to identify the few scenarios where cursor-based solutions are superior to set-based ones.
The books also cover other debatable constructs like temporary tables, dynamic execution, XML and .NET integration, which hold great power, but at the same time great risk. These constructs require programmatic maturity. These books will teach you how to use them wisely, in efficient and safe ways where they are relevant.

The first book–Inside Microsoft SQL Server 2005: T-SQL Querying–focuses on set-based querying, and should be read first. The second book–Inside Microsoft SQL Server 2005: T-SQL Programming–focuses on procedural programming, and assumes you read the first or have sufficient querying background.

Inside T-SQL Querying starts with three chapters that lay the foundations required for the rest of the chapters.

The first chapter covers logical query processing. It describes in detail the logical phases involved in processing queries, the unique aspects of SQL querying, and the special mindset you need to shift to in order to program in a relational, set oriented environment.
The second chapter covers physical query processing. It describes in detail the way the SQL Server engine processes queries, and compares and contrasts physical query processing with logical query processing. This chapter was written by Lubor Kollar, a Program Manager with the SQL Server development team in charge of the query optimizer and other parts of the engine. Few people in the world probably know the subject as well as Lubor does. I find it a privilege to have the designer of the optimizer explain it in his own words.
The third chapter covers a query tuning methodology we developed in our company (SolidQ), and have been successfully applying in production systems. The chapter also covers indexes and analyzing execution plans. This chapter lays the important background required for the following chapters in the book which as a practice talk about indexes and execution plans. These are important aspects of querying and query optimization.

The chapters that follow delve into advanced querying and query optimization, where both logical and physical aspects of your code are intertwined. These include: Subqueries, Table Expressions and Ranking Functions; Joins and Set Operations; Aggregating and Pivoting Data (including a section about CLR User Defined Aggregates written by Dejan Sarka); TOP and APPLY; Data Modification; Hierarchies and Recursive Queries.

Appendix A covers pure logic puzzles. SQL querying essentially deals with logic. I find it important to practice pure logic to improve your query problem solving capabilities. Here you have a chance to practice logical puzzles to improve your logic skills. I also find these puzzles fun and challenging, and you can practice them with all the family. These puzzles are a compilation of the logic puzzles which I covered in my T-SQL column in SQL Server Magazine. I’d like to thank SQL Server Magazine for allowing me to share these puzzles with the book’s readers.

Inside T-SQL Programming focuses on programmatic T-SQL constructs and expands to treatment of XML and .NET integration.

It covers: Datatype Related Problems, including XML and CLR User Defined Types; Temporary Tables and Table Variables; Cursors; Dynamic Execution; Views; User Defined Functions, including CLR UDFs; Stored Procedures, including CLR Procedures; Triggers, including DDL and CLR Triggers; Transactions, including coverage of the new snapshot-based isolation levels; Exception Handling; Service Broker.

The sections in the book that cover XML and .NET integration (UDTs, UDFs, Stored Procedures and Triggers) were written by Dejan Sarka. Dejan is a SQL Server expert and extremely knowledgeable in the relational model. He has fascinating views about the way these new constructs can fit with the relational model when used sensibly. I found it important to have someone with strong grasp in the relational model to cover these debatable areas of the product. All CLR code samples are provided in both C# and Visual Basic .NET.
The last chapter covering Service Broker was written by Roger Wolter. Roger is the Program Manger with the SQL Server development team in charge of Service Broker. Again, nothing like having the designer of a component explain it in his own words.

Finally, last but not least, Steve Kass, was the Technical Editor of the books. Steve is an extremely sharp guy. He is a SQL Server MVP, and teaches mathematics at Drew University. He’s very strong in SQL Server and logic, and his contribution to the book was invaluable.

I’d like to thank the guest authors who took part in the books: Lubor Kollar, Dejan Sarka, Roger Wolter, to Steve Kass who did the technical editing, and to Lubor Kollar and David Campbell who wrote the forewords. I deeply value your contribution!

And to you; the reader of these books: to me SQL is science, logic and art. I’ve been cooking these books for a long time and poured into them all my passion and many years of my experience. I hope that you will find the books practical, useful and interesting, and that you will find SQL a source of inspiration like I do. If you have any comments/corrections that you’d like to share, I’d love to hear about those: comments/corrections for Inside T-SQL Querying, comments/corrections for Inside T-SQL Programming.

Sincerely,
Itzik