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

4星 · 超过85%的资源需积分: 9 101 浏览量更新于2024-07-27 收藏 4.15MB PDF 举报

"MS Inside SQL Server 2008 T-SQL Querying" 本书"MS Inside SQL Server 2008 T-SQL Querying"是针对微软的SQL Server 2008数据库管理系统的一本深入指南，专注于Transact-SQL（T-SQL）查询语言的使用。由Lubor Kollar、Dejan Sarka和Steve Kass三位专家共同撰写，并由Kalen Delaney担任系列编辑。此书还得到了César Galindo-Legaria博士，微软SQL Server查询优化团队的经理所作的序言。在SQL Server 2008中，T-SQL是进行数据查询、数据操作和数据库管理的核心工具。本书旨在帮助读者深入理解T-SQL的各个方面，包括但不限于： 1. **基础语法与概念**：介绍T-SQL的基本结构，如SELECT、FROM、WHERE、GROUP BY和HAVING子句，以及JOIN操作，帮助读者构建有效的查询。 2. **高级查询技术**：探讨窗口函数、集合运算、子查询和联接的复杂用法，以及如何利用T-SQL进行数据聚合和分组。 3. **数据修改**：讲解INSERT、UPDATE和DELETE语句的使用，以及如何处理事务和并发控制，确保数据的完整性和一致性。 4. **存储过程和函数**：解释如何创建和使用存储过程和用户自定义函数，提高代码复用性和性能。 5. **查询优化**：深入探讨查询执行计划，学习如何分析和优化查询性能，利用索引和统计信息来提升查询效率。 6. **动态SQL和编程**：介绍如何编写和执行动态SQL，以及如何利用T-SQL进行流程控制和错误处理。 7. **安全性与权限管理**：讲解如何设置和管理SQL Server中的用户权限，保护数据库的安全。 8. **XML支持**：介绍SQL Server 2008对XML数据类型的支持，包括查询和操作XML数据的方法。 9. **高级特性**：涵盖如Common Table Expressions (CTEs)、Recursive Queries、Table-Valued Parameters等SQL Server 2008特有的T-SQL特性。通过本书，读者可以提升在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页未读，继续阅读

lcr5413

粉丝: 1

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

Inside Microsoft SQL Server 2008 T-SQL Programming.pdf

Inside Microsoft SQL Server 2008 T-SQL Programming

Inside Microsoft SQL Server 2008 T-SQL Querying Code

Inside SQL SERVER 2005 T-SQL Programming

Inside Microsoft SQL Server 2008 T-SQL Querying

Inside Microsoft Sql Server 2008 T-Sql Querying

Inside Microsoft SQL Server 2008- T-SQL Programming

Inside Microsoft SQL Server 2008 - T-SQL Querying

Inside Microsoft SQL Server 2005 T-SQL Programming.chm

SQL Server 2008 T-SQL编程指南

最新资源