This book and its sequel—Inside Microsoft SQL Server 2008: T-SQL Programming—cover
advanced T-SQL querying, query tuning, and programming in Microsoft SQL Server 2008.
They are designed for experience
d programmers and DBAs who need to write and optimize
code in SQL Server 2008. For brevity, I’ll refer to the books as T-SQL Querying and T-SQL
Programming, or just as these books.
Those who read the SQL Server 2005 edition of the books will fi nd plenty of new materials
covering new subjects, new features, and enhancements in SQL Server 2008, plus revisions
and new insights about the existing subjects.
These 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 effi cient solutions in a
These 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, they teach you how to
identify the few scenarios where cursor-based solutions are superior to set-based ones.
This book—T-SQL Querying—focuses on set-based querying and query tuning, and
I recommend that you read it fi rst. The second book—T-SQL Programming—focuses on
procedural programming and assumes that you read the fi rst book or have suffi cient
T-SQL Querying starts with fi ve chapters that lay the foundation of logical and physical query
processing required to gain the most from the rest of the chapters in both books.
The fi rst 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 mind-set
you need to adopt to program in a relational, set-oriented environment.
The second chapter covers set theory and predicate logic—the strong mathematical
foundations upon which the relational model is built. Understanding these foundations
will give you better insights into the model and the language. This chapter was written
by Steve Kass, who was also the main technical editor of these books. Steve has a unique
combination of strengths in mathematics, computer science, SQL, and English that make him
the ideal author for this subject.
The third chapter covers the relational model. Understanding the relational model is
essential for good database design and helps in writing good code. The chapter defi nes
relations and tuples and operators of relational algebra. Then it shows the relational model
from a different perspective called relational calculus. This is more of a business-oriented
perspective, as the logical model is described in terms of predicates and propositions.
Data integrity is crucial for transactional systems; therefore, the chapter spends time
discussing all kinds of constraints. Finally, the chapter introduces normalization—the
formal process of improving database design. This chapter was written by Dejan Sarka.
Dejan is one of the people with the deepest understanding of the relational model
that I know.
The fourth chapter covers query tuning. It introduces a query tuning methodology we
developed in our company (Solid Quality Mentors) 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 rest of the chapters
in both books, which as a practice discuss working with indexes and analyzing execution
plans. These are important aspects of querying and query tuning.
The fi fth chapter covers complexity and algorithms and was also written by Steve Kass. This
chapter particularly focuses on some of the algorithms used often by the SQL Server engine.
It gives attention to considering worst-case behavior as well as average case complexity.
By understanding the complexity of algorithms used by the engine, you can anticipate, for
example, how the performance of certain queries will degrade when more data is added
to the tables involved. Gaining a better understanding of how the engine processes your
queries equips you with better tools to tune them.
The chapters that follow delve into advanced querying and query tuning, addressing both
logical and physical aspects of your code. These chapters cover the following subjects:
subqueries, table expressions, and ranking functions; joins and set operations; aggregating
and pivoting data; TOP and APPLY; data modifi cation; querying partitioned tables; and
graphs, trees, hierarchies, and recursive queries.
The chapter covering querying partitioned tables was written by Lubor Kollar. Lubor led
the development of partitioned tables and indexes when fi rst introduced in the product,
and many of the features that we have today are thanks to his efforts. These days
Lubor works with customers who have, among other things, large implementations
of partitioned tables and indexes as part of his role in the SQL Server Customer Advisory
Team (SQL CAT).
Appendix A covers logic puzzles. Here you have a chance to practice logical puzzles to
improve your logic skills. SQL querying essentially deals with logic. I fi nd it important to
practice pure logic to improve your query problem-solving capabilities. I also fi nd 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—T-SQL Programming—focuses on programmatic T-SQL constructs
and expands its coverage to treatment of XML and XQuery and the CLR integration.
The book’s chapters cover the following subjects: views; user-defi ned functions; stored
procedures; triggers; transactions and concurrency; exception handling; temporary tables
and table variables; cursors; dynamic SQL; working with date and time; CLR user-defi ned
types; temporal support in the relational model; XML and XQuery (including coverage
of open schema); spatial data; change data capture, change tracking, and auditing;
and Service Broker.
The chapters covering CLR user-defi ned types, temporal support in the relational model,
and XML and XQuery were written by Dejan Sarka. As I mentioned, Dejan is extremely
knowledgeable in the relational model and has very interesting insights into the model
itself and the way the constructs that he covers in his chapters fi t in the model when
The chapter about spatial data was written by Ed Katibah and Isaac Kunen. Ed and Isaac
are with the SQL Server development team and led the efforts to implement spatial data
support in SQL Server 2008. It is a great privilege to have this chapter written by the
designers of the feature. Spatial data support is new to SQL Server 2008 and brings new
data types, methods, and indices. This chapter is not intended as an exhaustive treatise
on spatial data or as an encyclopedia of every spatial method that SQL Server now
supports. Instead, this chapter will introduce core spatial concepts and provide the reader
with key programming constructs necessary to successfully navigate this new feature
to SQL Server.
The chapter about change data capture, change tracking, and auditing was written by Greg
Low. Greg is a SQL Server MVP and the managing director of SolidQ Australia. Greg has
many years of experience working with SQL Server—teaching, speaking, and writing about
it—and is highly regarded in the SQL Server community. The technologies that are the focus
of this chapter track access and changes to data and are new in SQL Server 2008. At fi rst
glance, these technologies can appear to be either overlapping or contradictory, and the
best-use cases for each might be far from obvious. This chapter explores each technology,
discusses the capabilities and limitations of each, and explains how each is intended
to be used.
The last chapter, which covers Service Broker (SSB), was written by Roger Wolter. Roger is
the program manager with the SQL Server development team and led the initial efforts to
introduce SSB in SQL Server. Again, there’s nothing like having the designer of a component
explain it in his own words. The “sleeper” feature of SQL Server 2005 is now in production in
a wide variety of applications. This chapter covers the architecture of SSB and how to use SSB
to build a variety of reliable asynchronous database applications. The SQL 2008 edition adds
coverage of the new features added to SSB for the SQL Server 2008 release and includes
lessons learned and best practices from SSB applications deployed since the SQL Server 2005
release. The major new features are Queue Priorities, External Activation, and a new SSB
troubleshooting application that incorporates lessons the SSB team learned from customers
who have already deployed applications.