Microsoft SQL Server 2008深入解析：T-SQL查询权威指南

需积分: 15 27 浏览量更新于2024-07-25 收藏 3.75MB PDF 举报

《Microsoft SQL Server 2008技术内幕：T-SQL查询（英文版）》由Lubor Kollar、Dejan Sarka和Steve Kass共同编著，Kalen Delaney担任系列编辑，Itzik Ben-Gan执笔。这本书是Microsoft Press出版，作为Microsoft Corporation的一个部门，专注于深入探讨SQL Server 2008的核心技术之一——Transact-SQL (T-SQL) 查询语言。该书的封面故事聚焦于T-SQL查询，这是Microsoft SQL Server数据库管理系统中的主要交互方式，用于执行数据操作、查询、存储过程和管理任务。T-SQL是一种结构化查询语言，它允许用户与关系型数据库进行高效、灵活的交互，是数据库管理员、开发人员和数据分析师不可或缺的工具。作者César Galindo-Legaria博士为本书撰写了序言，他作为Microsoft SQL Server查询优化团队的经理，分享了他在实际工作中对T-SQL查询优化的理解和经验，帮助读者了解如何设计出性能优良的查询策略。书中涵盖了T-SQL的基础概念，包括查询语法、数据类型、逻辑运算符、集合操作、联接、子查询、视图和存储过程等核心内容，同时还深入探讨了高级主题，如窗口函数、动态SQL、分页和排序、事务处理以及性能调优。读者不仅可以学习到理论知识，还能了解到SQL Server 2008版本中特定的特性和改进。版权方面，所有内容未经Microsoft Press书面许可不得复制或传播，且有Library of Congress的Control Number标明其版权状态。本书在美国印刷并装订，同时在加拿大通过H.B.Fenn and Company Ltd.发行。国际版本的信息可通过微软公司或直接联系Microsoft Press International获取。如果你是一名数据库专业人士，想要提升在SQL Server 2008环境下的T-SQL技能，或者需要了解如何优化查询性能，那么这本书将是你扩展知识库的重要参考资料。通过阅读这本书，你将能更深入地理解T-SQL的工作原理，以及如何利用它来满足复杂的数据处理需求。

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页未读，继续阅读

clhed

粉丝: 1
资源: 1

Microsoft SQL Server 2008深入解析：T-SQL查询权威指南

Microsoft SQL Server 2008技术内幕:T-SQL查询

Microsoft SQL Server 2008技术内幕：T-SQL查询_源代码及附录.zip

Microsoft SQL Server 2008技术内幕：T-SQL查询.pdf

Microsoft SQL Server 2008技术内幕:T-SQL查询（英文版）

Microsoft SQL Server 2008技术内幕：T-SQL查询

Microsoft SQL Server 2008技术内幕：T-SQL查询 样例数据库

Microsoft SQL Server 2008技术内幕：T-SQL查询 part2

Microsoft SQL Server 2008技术内幕：T-SQL查询 附录A

Microsoft SQL Server 2008技术内幕：T-SQL查询(第二卷)

SQLServer2008技术内幕：T-SQL查询

最新资源

Microsoft SQL Server 2008技术内幕：T-SQL查询样例数据库

Microsoft SQL Server 2008技术内幕：T-SQL查询附录A