深入理解SQL Server 2008 T-SQL查询原理

需积分: 9 155 浏览量更新于2024-07-28 收藏 4.15MB PDF 举报

《深入理解SQL Server 2008：T-SQL查询》是一本由Itzik Ben-Gan撰写的权威指南，作为Inside Microsoft SQL Server系列的一部分，由Lubor Kollar、Dejan Sarka和Steve Kass等专家共同参与。该书的系列编辑是Kalen Delaney，而封面故事由César Galindo-Legaria博士撰写序言，他担任微软SQL Server查询优化团队的经理。本书详细探讨了T-SQL（Transact-SQL）查询在SQL Server 2008中的原理和实践，这是数据库管理系统的核心组件，用于执行数据检索、更新、管理等操作。读者可以从中了解到SQL Server查询优化策略、语法结构、性能调优技巧以及高级查询技术。作者Itzik Ben-Gan以其深厚的专业背景，结合Microsoft Press的出版质量，为读者提供了一手的理论与实战知识。版权方面，所有内容未经Microsoft Corporation书面许可不得复制或传播。此外，该书在加拿大由H.B.Fenn and Company Ltd.分发，并在英国图书馆有馆藏记录。Microsoft Press的书籍在全球范围内通过书店和分销商发行，对于国际版本的更多信息，可以通过联系当地的微软公司办公室或直接向Microsoft Press International咨询，联系方式为传真号码(425)936-7329，或者访问官方网站www.microsoft.com/mspress。《深入理解SQL Server 2008：T-SQL查询》不仅适合数据库管理员、开发人员和SQL Server专业用户，也对希望提升SQL查询性能分析和优化技能的读者极具价值。通过阅读这本书，读者将能够深入了解如何构建高效、可维护的SQL查询，以及如何应对SQL Server环境中的复杂查询挑战。无论是初学者还是经验丰富的从业者，都能从中收获宝贵的知识和实践经验。

xvi Acknowledgments

To the team at Microsoft Press: Ken Jones, the product planner: I appreciate the personal

manner in which you handle things and always look forward to Guinness sessions with you.

I think that you have an impossible job trying to make everyone happy and keep projects

moving, but somehow you still manage to do it.

To Sally Stickney, the development editor, thanks for kicking the project off the ground. I know

that the T-SQL querying book was your last project at Microsoft Press before you started your

new chosen path in life and am hopeful that it left a good impression on you. I wish you luck

and happiness in your new calling.

To Denise Bankaitis, the project editor, you of all people at Microsoft Press probably spent

most time working on the books. Thanks for your elegant project management and for

making sure things kept ﬂ owing. It was a pleasure to work with you.

I’d also like to thank DeAnn Montoya, the project manager for the vendor editorial team,

S4Carlisle Publishing Services, and Becka McKay, the copy editor. I know you spent countless

hours going over our texts, and I appreciate it a lot.

To Solid Quality Mentors, being part of this amazing company and group of people is by far the

best thing that happened to me in my career. It’s as if all I did in my professional life led me to this

place where I can fulﬁ ll my calling, which is teaching people about SQL. To Fernando Guerrero,

Brian Moran, and Douglas McDowell: the company grew and matured because of your efforts, and

you have a lot to be proud of. Being part of this company, I feel a part of something meaningful

and that I’m among family and friends—among people whom I both respect and trust.

I’d like to thank my friends and colleagues from the company: Ron Talmage, Andrew J. Kelly,

Eladio Rincón, Dejan Sarka, Herbert Albert, Fritz Lechnitz, Gianluca Hotz, Erik Veerman,

Jay Hackney, Daniel A. Seara, Davide Mauri, Andrea Benedetti, Miguel Egea, Adolfo Wiernik,

Javier Loria, Rushabh Mehta, Greg Low, Peter Myers, Randy Dyess, and many others. I’d like

to thank Jeanne Reeves for making many of my classes possible and all the back-ofﬁ ce team

for their support. I’d also like to thank Kathy Blomstrom for managing our writing projects

and for your excellent edits.

I’d like to thank the members of the SQL Server development team who are working on T-SQL

and its optimization: Michael Wang, Michael Rys, Eric Hanson, Umachandar Jayachandran

(UC), Tobias Thernström, Jim Hogg, Isaac Kunen, Krzysztof Kozielczyk, Cesar Galindo-Legaria,

Craig Freedman, Conor Cunningham, and many others. For better or worse, what you develop

is what we have to work with, and so far the results are outstanding! Still, until we get a full

implementation of the OVER clause, you know I won’t stop bothering you. ;-)

I’d like to thank Dubi Lebel and Assaf Fraenkel from Microsoft Israel and also Ami Levin, who

helps me run the Israeli SQL Server users group.

A06A626034.indd xvi 2/18/2009 10:43:50 PM

Acknowledgments xvii

To the team at SQL Server Magazine: Megan Bearly, Sheila Molnar, Mary Waterloo,

Michele Crockett, Mike Otey, Lavon Peters, and Anne Grubb: Being part of this magazine is a

great privilege. Congratulations on the 10th anniversary of the magazine! I can’t believe that

10 years passed so quickly, but that’s what happens when you have fun.

To my fellow SQL Server MVPs: Erland Sommarskog, Alejandro Mesa, Aaron Bertrand,

Tibor Karaszi, Steve Kass, Dejan Sarka, Roy Harvey, Tony Rogerson, Marcello Poletti (Marc),

Paul Randall, Bob Beauchemin, Adam Machanic, Simon Sabin, Tom Moreau, Hugo Kornelis,

David Portas, David Guzman, and many others: Your contribution to the SQL Server community

is remarkable. Much of what I know today is thanks to our discussions and exchange of ideas.

To my fellow SQL Server MCTs: Tibor Karaszi, Chris Randall, Ted Malone, and others: We go a

long way back, and I’m glad to see that you’re all still around in the SQL teaching community.

We all share the same passion for teaching. Of anyone, you best understand the kind of

fulﬁ llment that teaching can bestow.

To my students: Without you, my work would be meaningless. Teaching is what I like to do

best, and the purpose of pretty much everything else that I do with SQL—including writing

these books—is to support my teaching. Your questions make me do a lot of research, and

therefore I owe much of my knowledge to you.

To my parents, Emilia and Gabriel Ben-Gan, and to my siblings, Ina Aviram and Michael Ben-Gan,

thanks for your continuous support. The fact that most of us ended up being teachers is probably

not by chance, but for me to fulﬁ ll my calling, I end up traveling a lot. I miss you all when I’m

away, and I always look forward to our family reunions when I’m back.

To Lilach, you’re the one who needs to put up with me all the time and listen to my SQL ideas

that you probably couldn’t care less about. It’s brainwashing, you see—at some point you

will start asking for more, and before you know it, you will even start reading my books. Not

because I will force you but because you will want to, of course. That’s the plan at least. Thanks

for giving meaning to what I do and for supporting me through some rough times of writing.

A06A626034.indd xvii 2/18/2009 10:43:50 PM

xix

Introduction

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 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 ﬁ 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 efﬁ cient 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 ﬁ rst. The second book—T-SQL Programming—focuses on

procedural programming and assumes that you read the ﬁ rst book or have sufﬁ cient

querying background.

T-SQL Querying starts with ﬁ 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 ﬁ 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.

A07I626034.indd xix 2/25/2009 10:44:11 AM

xx Introduction

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 deﬁ 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 ﬁ 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 modiﬁ 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 ﬁ 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 ﬁ nd it important to

practice pure logic to improve your query problem-solving capabilities. I also ﬁ nd these

puzzles fun and challenging, and you can practice them with the entire family. These puzzles

A07I626034.indd xx 2/25/2009 10:44:11 AM

剩余823页未读，继续阅读

wxhlkw1

粉丝: 0
资源: 1

深入理解SQL Server 2008 T-SQL查询原理

Inside SQL Server 2005: Query Tuning and Optimization

inside sql server 2000

Inside SQL Server 2000

Inside SQL Server 2005 Tools

Inside SQL SERVER 2005 T-SQL Programming

MS Press - Inside SQL Server 2000

MS Press.Inside SQL Server 2000

Inside Microsoft SQL Server 2005

Inside Microsoft SQL Server 2008

Inside Microsoft SQL Server 2000

最新资源