Microsoft SQL Server 2008 T-SQL查询深度解析

需积分: 9 194 浏览量更新于2024-07-16 收藏 4.16MB PDF 举报

《微软T-SQL查询指南:深入理解SQL Server 2008》是一本由Lubor Kollar、Dejan Sarka和Steve Kass合著的专业书籍，由Kalen Delaney担任系列编辑，Itzik Ben-Gan撰写，由Microsoft Press出版。本书专为深入探讨SQL Server 2008中的查询语言——T-SQL（Transact-SQL）设计，适合数据库管理员、开发人员和其他IT专业人员，旨在帮助读者理解和优化查询性能，提高数据处理能力。书中涵盖了T-SQL的基础概念、语法结构、查询优化策略以及高级查询技巧，适合希望通过系统学习提升SQL Server查询技术的读者。作者们以其丰富的经验，从理论到实践，剖析了查询的执行过程，包括如何编写高效且易于维护的查询语句，如何利用索引、视图和存储过程来改善查询性能，以及如何理解和使用SQL Server的查询优化器。此外，本书还特别邀请了César Galindo-Legaria博士作序，他是Microsoft SQL Server查询优化团队的经理，他的见解无疑为读者提供了权威的行业视角。版权方面，所有内容受版权保护，未经Microsoft Press许可，不得任何形式复制或传播。对于国际版本的获取，可通过当地的Microsoft Corporation办公室或直接联系Microsoft Press International获取更多信息，他们提供全球范围内的销售和分销服务。读者可以通过书籍零售商或通过微软官网获取印刷版，也可以关注网站上的最新更新和电子版选项。《Inside Microsoft SQL Server 2008: T-SQL Querying》是一本实用且全面的参考书籍，无论是初学者还是经验丰富的开发者，都能从中收获关于T-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页未读，继续阅读

wp_wnd

粉丝: 33
资源: 1

Microsoft SQL Server 2008 T-SQL查询深度解析

Inside Microsoft SQL Server 2008 - T-SQL Querying

microsoft.press.inside.microsoft.sql.server.2005.t-sql.querying.apr.2006-1

microsoft.press.inside.microsoft.sql.server.2005.t-sql.querying.apr.2006-2

Is downloading org. Xerial: sqlite - JDBC: 3.40.1.0…\ 3.40.1 \ org \ xerial \ sqlite-jdbc \ 3.40.1.0 \ sqlite-jdbc-3.40.1.0.jar

Error querying database. Cause: java.sql.SQLException: interrupt

### Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

Error querying database. Cause: java.sql.SQLSyntaxErrorException: Duplicate column

Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax

最新资源