Inside Microsoft SQL Server 2005:
T-SQL Querying (MSPress)
 By:
Itzik Ben-Gan, Guest Authors:
Lubor Kollar, Dejan Sarka,
Foreword by: David Campbell
Technical Editor:
Steve Kass
Publication Date: March 29, 2006
ISBN: 0735623139
640 printed pages
Order the book at:
-
amazon
-
Barnes & Noble
- Oreilly (Ebook also
available) (use discount code MSINT,
details here)
Inside Microsoft SQL Server 2005: T-SQL Programming (MSPress)
 By:
Itzik Ben-Gan, Guest Authors:
Dejan Sarka,
Roger Wolter,
Foreword by: Lubor Kollar
Technical Editor: Steve Kass
Publication Date: May 17, 2006
ISBN: 0735621977
544 printed pages
Order the book at:
-
amazon
-
Barnes & Noble
- Oreilly (Ebook also
available) (use discount code MSINT,
details here)
A Letter to the Reader
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
About the Contributors
Itzik
Ben-Gan
Itzik Ben-Gan is a Mentor and Founder of SolidQ. A Microsoft
SQL Server MVP (Most Valuable Professional) since 1999, Itzik has delivered
numerous training events around the world focused on T-SQL Querying, Query
Tuning and Programming. Itzik is the author of several books on Microsoft
SQL Server. He has written many articles for SQL Server Magazine as well as
articles and whitepapers for MSDN. Itzik's speaking activities include Tech
Ed, DevWeek, various SQL User Groups around the world, PASS, SQL Server
Magazine Connections, and SolidQ's events to name a few.
As of 1992 Itzik has been involved in many projects covering various
database and computer systems related technologies. Besides helping
customers with their pressing needs, teaching, mentoring, fixing their
problems and optimizing their databases, Itzik helped developers and DBAs
shift to relational/set-based mindset, improving both the performance of
their code and its maintainability. Itzik's main expertise is T-SQL
Querying, Query Tuning, Programming and Internals, but he's proficient in
other database areas as well. In 1999 Itzik founded the Israeli SQL Server
and OLAP User Group and has been managing it since.
e-mail: itzik@solidq.com
Websites:
www.solidq.com
www.insidetsql.com
www.sql.co.il
Lubor Kollar
Lubor Kollar is member of the SQL Server development
organization since the 6.5 release in 1996. He 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 Query 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. Lubor enjoys interacting with the Microsoft Research team
transforming the newest research results into product features. Before
joining Microsoft Lubor was developing DB2 engines for various OS platforms
in IBM laboratories in Toronto and Santa Teresa.
From the professional achievements Lubor specially values solving one of the
open problems in Donald Knuth’s “The Art of Computer Programming” vol. 3
during his student years. And yes, you will find the problem and solution by
following Lubor’s name in the newer editions of the book.
Lubor loves outdoors – skiing, hiking, gardening, mountain biking, fishing
and mushrooming to name his most beloved outside-the-work and home
activities. He holds Professional Ski Instructor license and during the
winter weekends he is teaching skiing in the local ski resort near Redmond.
Since Lubor Kollar is not very frequent name you can use your favorite
search engine to find out much more about Lubor – his white papers, blog
contributions, articles, conference papers, patents, trips, and more.
e-mail:
Lubor.Kollar@microsoft.com
Dejan Sarka
Dejan Sarka, MCP, MCDBA, MCT, SQL Server MVP, SolidQ Mentor,
is a trainer and consultant working for many CTECs and development companies
in Slovenia and some other countries. Besides training he continuously works
on OLTP, OLAP and Data Mining projects, especially at the design stage. He
is a regular speaker on some of the most important international conferences
like TechEd, PASS and MCT conference. He is also indispensable on regional
Microsoft TechNet meetings, on the NT Conference, the hugest Microsoft
conference in Central and Eastern Europe, and some other events. He is the
founder of the Slovenian SQL Server Users Group. Dejan Sarka also developed
two courses for SolidQ – Data Modeling Essentials and Data
Mining with SQL Server 2005.
e-mail: dejan@solidq.com
Website: www.solidq.com
Roger Wolter
Roger
Wolter has close to 30 years experience in the computer industry the last 7
with Microsoft. He is currently a Program manager on the SQL Server team
specializing in SQL Server Service Broker and SQL Server Express. His
projects at Microsoft have included COM+, SQLXML, the Soap Toolkit, SQL
Server Service Broker and SQL Express.
e-mail:
Roger.Wolter@microsoft.com
Steve Kass
Steve Kass is Associate Professor of Mathematics and
Computer Science at Drew University in Madison, New Jersey. Steve graduated
from Pomona College and holds a Ph.D. in Mathematics from the University of
Wisconsin–Madison. He is also a Microsoft SQL Server MVP.
e-mail: skass@drew.edu
Website: users.drew.edu/skass
David
Campbell
 David
Campbell is the General Manager of Strategy, Infrastructure and Architecture
of Microsoft SQL Server.
David graduated with a Master’s Degree in Mechanical Engineering (Robotics)
from Clarkson University in 1984 and began working on robotic workcells for
Sanders Associates – later a division of Lockheed Corporation. In 1990 he
joined Digital Equipment Corporation where he worked on their Codasyl
database product DEC DBMS as well as their relational database product; Rdb.
Upon joining Microsoft in 1994, David was a developer and architect on the
SQL Server Storage Engine team that was principally responsible for
rewriting the core engine of SQL Server for SQL Server Version 7.0.
David holds several patents in the data management, schema and software
quality realms. He is a frequent speaker at industry and research
conferences on a wide variety of data management and software development
topics.
Back to top of page
|
