Inside Microsoft SQL Server 2008:
T-SQL Querying (MSPress)
By: Itzik Ben-Gan, Lubor Kollar, Dejan
Sarka, Steve Kass
Technical Editors: Steve Kass, Umachandar Jayachandran
Foreword by: César Galindo-Legaria
Publication Date: March 25, 2009
ISBN-10: 0735626030, ISBN-13:
978-0735626034
Format: Paperback, 832 pages
Order the book at:
-
amazon
-
Barnes & Noble
- Oreilly (Ebook also
available) (use discount code MSINT,
details here)
Inside Microsoft SQL Server 2008:
T-SQL Programming (MSPress)
By: Itzik Ben-Gan, Dejan Sarka, Roger
Wolter, Greg Low, Ed Katibah, Isaac Kunen
Technical Editors: Steve Kass, Bob Beauchemin
Foreword by: Lubor Kollar
Publication Date: September 16, 2009
ISBN-10: 0735626022, ISBN-13:
978-0735626027
Format: Paperback, 832 pages
Order the book at:
-
amazon
-
Barnes & Noble
- Oreilly (Ebook also
available) (use
discount code MSINT, details
here)
Introduction
These books cover advanced T-SQL querying, query tuning, and programming in
Microsoft SQL Server 2008. They are designed for experienced 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 find 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 efficient
solutions in a natural manner.
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 first. The second book—T-SQL Programming—focuses
on procedural programming and assumes that you read the first book or have
sufficient querying background.
T-SQL
Querying starts with five 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 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 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 defines 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 fifth
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
modification; 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 first 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 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—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-defined types; temporal support in
the relational model; XML and XQuery (including coverage of open schema);
spatial data; tracking access and changes to data (extended events,
auditing, change tracking, and change data capture); and
Service Broker.
The chapters
covering CLR user-defined 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 fit in the model when used sensibly.
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 tracking access and changes to data 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 first 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.
Sincerely,
Itzik
Back to top of page
|
