SQL Server 2008 T-SQL编程深度解析

5星 · 超过95%的资源需积分: 10 167 浏览量更新于2024-07-25 收藏 12.18MB PDF 举报

"Microsoft.Press.Inside.Microsoft.SQL.Server.2008.TSQL.Programming.Apr.2009" 是一本由微软出版社发行的书籍，专注于T-SQL编程在SQL Server 2008中的应用。这本书由Itzik Ben-Gan、Dejan Sarka、Ed Katibah、Greg Low、Roger Wolter和Isaac Kunen等多位专家合著，旨在深入解析SQL Server 2008的Transact-SQL（T-SQL）编程。T-SQL是SQL Server数据库管理系统中的主要查询语言，用于数据查询、数据更新、过程创建以及复杂的数据库编程任务。书中涵盖了以下核心T-SQL知识点： 1. **T-SQL基础**：包括基本的SELECT语句，用于从数据库中检索数据。学习如何使用WHERE子句过滤结果，GROUP BY和HAVING子句进行分组和聚合操作，以及JOINs来联接多个表。 2. **变量与控制流程**：介绍了声明和使用变量，以及如何通过IF-ELSE、CASE语句实现条件控制，使用WHILE和BEGIN-END块进行循环控制。 3. **存储过程和函数**：探讨了用户定义的存储过程（UDFs）和函数，如何编写存储过程以封装复杂逻辑，并返回单个或多个结果集。 4. **高级查询技术**：深入理解窗口函数，如RANK(), ROW_NUMBER()和LAG()，以及集合操作，如UNION, INTERSECT和EXCEPT。 5. **触发器**：学习何时何地使用触发器，以及如何创建和管理触发器，以响应数据更改事件。 6. **游标**：虽然通常避免使用，但书中也会讲解何时需要使用游标以及如何正确使用它们。 7. **事务和并发控制**：介绍事务的基本概念，如ACID属性，以及锁定和死锁处理策略。 8. **错误处理和异常处理**：如何在T-SQL代码中捕获和处理错误，使用TRY-CATCH结构进行异常处理。 9. **性能优化**：讨论查询优化器的工作原理，如何使用索引提升查询性能，以及性能监视和调优工具的使用。 10. **安全性**：涉及权限管理，如GRANT, REVOKE和DENY，以及如何创建和管理登录名、用户和角色。此外，该书还可能包含实践案例、示例代码和练习题，帮助读者巩固理论知识并提高实际操作技能。对于希望在SQL Server 2008环境中深化T-SQL编程能力的IT专业人员来说，这是一本宝贵的资源。

xvi Foreword

Even if you are a seasoned SQL Server developer I’m sure this book will show you new and

more efﬁcient ways to perform your tasks. For example, I agree with Dejan that there are

few CLR UDTs in production systems. And this is not only true for UDTs—few UDFs, triggers,

and stored procedures are written in CLR languages. The book provides numerous examples

of C# and Microsoft Visual Basic solutions. Most of the examples are presented in both C#

and Visual Basic, which are the most popular CLR languages. The authors are careful about

CLR recommendations because of performance implications. Itzik not only provides general

performance guidelines, but he also tells you how long the alternatives took to execute on

his computer. Of course, you will try it on your computer!

Performance considerations are not restricted to CLR. You will ﬁnd performance improvement

tips in every single chapter of this book. For example, in Chapter 7, “Temporary Tables and

Table Variables,” you will learn when it is better to use temporary tables and when it is better

to use table variables. Itzik uses simple examples, interpreting query plans and showing how

to use IO counters when comparing different solutions for the same task.

I mentioned that Chapter 12— Dejan’s “Temporal Support in the Relational Model”

chapter—is fascinating. Why? Let me share a little secret. Some time ago we considered

implementing special support for temporal data inside SQL Server. The work was intense

and the SQL Server development team got help from leading academic sources as well.

One development lead even personalized the license plate on his car to “TIME DB.” What

happened with the project? The implementation was complex and costly. Some of the

alternatives were repeatedly re-evaluated without providing a clear winner. And there was

always a counter-argument—“you can use a workaround.” Whenever this argument was

challenged someone wrote a piece of code showing how a particular temporal task could

be achieved using existing features in SQL Server. But I don’t know anybody who did as

complete a job as Dejan in Chapter 12 of this book!

I worked with Roger Wolter on the same team when he was responsible for developing the

brand new Service Broker in SQL Server 2005. His chapter (Chapter 16) is great reﬂection of

his personality—deep with very accurate details in perfect structure. If you are new to Service

Broker you may want to start reading this chapter from the end, where you will learn which

scenarios you can use Service Broker with, along with a brief comparison of Service Broker with

messaging solutions delivered by Microsoft Message Queue (MSMQ), BizTalk, and Windows

Communication Foundation (WCF). Bank Itau in Brazil and MySpace are two examples of SQL

Server customers who use Service Broker for very different purposes. Bank Itau uses Service

Broker for batch processing. In MySpace, Service Broker creates a communication fabric

among hundreds of SQL Servers behind the MySpace.com social networking site.

I’m conﬁdent you will ﬁnd this book useful and worth reading whether you are a new or

seasoned SQL Server user. It is an invaluable reference for developers, data architects, and

administrators.

Lubor Kollar

Group Program Manager

SQL Server Customer Advisory Team

Microsoft, Redmond, Washington U.S.A.

xvii

Acknowledgments

Several people contributed to the T-SQL Querying and T-SQL Programming books and I’d

like to acknowledge their contributions. Some were involved directly in writing or editing the

books, whereas others were involved indirectly by providing advice, support, and inspiration.

To the coauthors of Inside Microsoft SQL Server 2008: T-SQL Querying—Lubor Kollar, Dejan

Sarka, and Steve Kass; and to the coauthors of Inside Microsoft SQL Server 2008: T-SQL

Programming—Dejan Sarka, Roger Wolter, Greg Low, Ed Katibah, and Isaac Kunen, it is

a great honor to work with you. It is simply amazing to see the level of mastery that you have

over your areas of expertise, and it is pure joy to read your texts. Thanks for agreeing to be

part of this project.

To Lubor, besides directly contributing to the books by writing a chapter for T-SQL Querying

and the foreword to T-SQL Programming, you provide support, advice, friendship, and are

a great source of inspiration. I always look forward to spending time with you—hiking,

drinking, and talking about SQL and other things.

To Dejko, your knowledge of the relational model is admirable. Whenever we spend time

together I learn new things and discover new depths. I like the fact that you don’t take

things for granted and don’t blindly follow the words of those who are considered experts

in the ﬁeld. You have a healthy mind of your own, and see things that very few are capable

of seeing. I’d like to thank you for agreeing to contribute texts to the books. I’d also like to

thank you for your friendship; I always enjoy spending time with you. We need to do the beer

list thing again some time. It’s been almost 10 years!

To the technical editor of the books, Steve Kass, your unique mix of strengths in mathematics,

SQL, and English are truly extraordinary. I know that editing both books and also writing your

own chapters took their toll. Therefore I’d like you to know how much I appreciate your work.

I know you won’t like my saying this, but it is quite interesting to see a genius at work. It kept

reminding me of Domingo Montoya’s work on the sword he prepared for the six-ﬁngered

man from William Goldman’s The Princess Bride.

To Umachandar Jayachandran (UC), many thanks for helping out by editing some of the

chapters. Your mastery of T-SQL is remarkable, and I’m so glad you could join the project in

any capacity. I’d also like to thank Bob Beauchemin for reviewing the chapter on spatial data.

I enjoy reading your texts; your insights on SQL Server programmability are always interesting

and timely.

To Cesar Galindo-Legaria, I feel honored that you agreed to write the foreword for the

T-SQL Querying book. The way you and your team designed SQL Server’s optimizer is simply

a marvel. I’m constantly trying to ﬁgure out and interpret what the optimizer does, and

whenever I manage to understand a piece of the puzzle, I ﬁnd it astonishing what a piece of

xviii Acknowledgments

software is capable of. Your depth of knowledge, your pleasant ways, and your humility are

an inspiration.

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

the 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 and Ashley Schneider, the project managers for the

vendor editorial team, S4Carlisle, 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, 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 that

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 and Glen McCoin 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 that 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, Yavor Angelov, Susan Price, 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.

Acknowledgments xix

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.

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

Crockett, Mike Otey, Lavon Peters, Anne Grubb; being part of this magazine is a great

privilege. Congratulations on the tenth 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, Paul Nielsen, 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, rather 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.

剩余831页未读，继续阅读

jiongjiongai

粉丝: 205
资源: 19

SQL Server 2008 T-SQL编程深度解析

SQL Server 2008 T-SQL编程指南

微软内部揭秘：SQL Server 2008 T-SQL编程指南

Microsoft SQL Server 2008 T-SQL查询深入指南：内部专家教程

Inside Microsoft SQL Server 2008 - TSQL Querying

Apress.SQL.Server.2008.Transact.SQL.Recipes.Jul.2008.pdf

SQLSERVER TSQL 源码

Sqlserver Tsql 分页

TSQL2012.sql

精通TSQL：Microsoft SQL Server 2008编程指南

tsql.tech-Code-snippets:我使用和_或已创建的适用于DBA或SQL开发人员的有用SQL Server脚本SP

最新资源