Inside Microsoft SQL Server 2005 - T-SQL Programming (2006)

Preface

This book and its predecessor—Inside Microsoft SQL Server 2005: T-SQL Querying—cover advanced T-SQL querying, query tuning, and programming in SQL Server 2005. They are designed for programmers and DBAs who need to write and optimize code in both SQL Server 2000 and 2005. For brevity, I'll refer to the books as Inside T-SQL Programming and Inside T-SQL Querying.

The books focus on practical common problems, discussing several 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 unveil the power of set-based querying, and explain why it's usually superior to procedural programming with cursors and the like. 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 much debated constructs—such as 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 T-SQL Querying—focuses on set-based querying, and I recommend that you read it first. The second book—Inside T-SQL Programming—focuses on procedural programming and assumes you read the first book or have sufficient querying background.

Inside T-SQL Querying starts with three chapters that lay the foundation of logical and physical query processing required to gain the most from 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. Lubor was a group program manager during the SQL Server 2005 development and his team was responsible for the "bottom" part of the Relational Engine—from query compilation and optimization to query execution, transactional consistency, backup/restore, and high availability. Table and Index Partitioning, Database Mirroring, Database Snapshot, Snapshot Isolation, Recursive Queries and other T-SQL query improvements, Database Tuning Advisor, and Online Index creation and maintenance were the major SQL Server 2005 features his team has been working on. Few people in the world probably know the subject of query optimization as well as Lubor does. I find it a privilege to have one of the designers of the optimizer explain it in his own words.

The third chapter covers a query tuning methodology we developed in our company (Solid Quality Learning) and have been applying in production systems. The chapter also covers working with indexes and analyzing execution plans. This chapter provides the important background knowledge required for the chapters that follow, which as a practice talk about working with indexes and analyzing execution plans. These are important aspects of querying and query tuning.

The chapters that follow delve into advanced querying and query tuning, where both logical and physical aspects of your code are intertwined. These chapters include: "Subqueries, Table Expressions, and Ranking Functions"; "Joins and Set Operations"; "Aggregating and Pivoting Data" (including a section about CLR user-defined aggregates, which was written by Dejan Sarka); "TOP and APPLY"; "Data Modification"; and "Graphs, Trees, Hierarchies, and Recursive Queries."

Appendix A covers pure logic puzzles. Here you have a chance to practice logical puzzles to improve your logic skills. SQL querying essentially deals with logic. I find it important to practice pure logic to improve your query problem-solving capabilities. I also find these puzzles fun and challenging, and you can practice them with the entire family. These puzzles are a compilation of the logic puzzles that 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.

The second book—Inside T-SQL Programming—focuses on programmatic T-SQL constructs and expands its coverage to treatment of XML and .NET integration. The topics it covers include: datatype-related problems, including XML and CLR user-defined types (UDTs); temporary tables; 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; and service broker.

The sections in the book that cover XML and .NET integration ("User-Defined Types," "User-Defined Functions," "Stored Procedures," and "Triggers") were written by Dejan Sarka. Dejan is a SQL Server expert, and he is 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 a strong grasp of the relational model to cover these much debated 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 manager 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.

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 he teaches mathematics at Drew University. He has extensive knowledge of SQL Server and logic, and his contribution to the books was invaluable.
Read Comments To Download This book