深入探索：微软SQL Server 2008 T-SQL查询

需积分: 9 105 浏览量更新于2024-07-24 收藏 4.18MB PDF 举报

"Inside Microsoft SQL Server 2008 T-SQL Querying 是一本由 Lubor Kollar, Dejan Sarka, Steve Kass 和 Itzik Ben-Gan 合著的专业书籍，系列编辑由 Kalen Delaney 担任。这本书深入探讨了 Microsoft SQL Server 2008 的 Transact-SQL (T-SQL) 查询技术，由微软 SQL Server 查询优化团队的经理 César Galindo-Legaria 博士作序。本书由 Microsoft Press 出版，版权归 Itzik Ben-Gan 所有，未经许可禁止复制或传播。" 该书主要知识点包括： 1. **T-SQL基础知识**：书中会介绍T-SQL的基本语法，如SELECT语句、JOIN操作、WHERE子句、GROUP BY与HAVING子句，以及聚合函数等，这些都是查询数据的基础。 2. **高级查询技术**：作者们可能详细讨论了子查询、联接（包括内连接、外连接和交叉连接）、窗口函数，以及如何利用临时表和表变量进行复杂的数据处理。 3. **性能优化**：由于作者之一是来自微软SQL Server查询优化团队的经理，因此书中的内容很可能涵盖查询性能优化技巧，如索引的使用、查询计划的分析、执行计划的优化，以及如何避免不高效的查询模式。 4. **存储过程和触发器**：T-SQL不仅仅用于查询，还用于编写存储过程和触发器，以实现更复杂的业务逻辑和数据库维护任务。书中可能会讨论这些高级特性的使用和最佳实践。 5. **事务和并发控制**：在多用户环境中，理解事务管理和并发控制至关重要。书里可能会讲解T-SQL中的事务语句、隔离级别以及并发问题的解决方法。 6. **错误处理和调试**：有效的错误处理和调试技巧是每个数据库开发者必备的技能。书中可能涉及如何捕获和处理错误，以及如何使用T-SQL进行调试。 7. **动态SQL和编程元素**：动态SQL允许在运行时构建查询，而T-SQL还支持流程控制语句（如IF-ELSE, WHILE）和变量，这些内容在编写复杂的数据库脚本时非常有用。 8. **实用案例和实践指导**：为了帮助读者更好地理解和应用理论知识，书中很可能会包含实际的案例研究和练习，帮助读者提升解决实际问题的能力。 9. **与SQL Server 2008的集成**：此版本可能涵盖了SQL Server 2008的新特性，例如改进的XML支持、新数据类型（如日期/时间类型）和新的查询优化工具。 10. **国际版和反馈机制**：书的末尾提到了国际版的获取方式以及微软出版社的联系方式，读者可以通过这些途径获取更多关于本书的信息或提供反馈。《Inside Microsoft SQL Server 2008 T-SQL Querying》是一本全面且深入的SQL Server查询指南，旨在帮助读者掌握和提升在2008环境下使用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页未读，继续阅读

williambirkin

粉丝: 9

深入探索：微软SQL Server 2008 T-SQL查询

深入探索Microsoft SQL Server 2008 T-SQL查询

精通SQL：微软SQL Server 2008 T-SQL查询深入解析

深入探索：微软SQL Server 2008 T-SQL查询

Inside Microsoft SQL Server 2008 - T-SQL Querying

Inside Microsoft SQL Server 2008: T-SQL Querying

Inside Microsoft SQL Server 2008:T-SQL Querying

Inside Microsoft SQL Server 2008 - TSQL Querying

Microsoft SQL Server 2008 - T-SQL Querying March 2009

探索Microsoft SQL Server 2008 T-SQL查询技术

探索Microsoft SQL Server 2008 T-SQL查询深度

最新资源