Microsoft SQL Server 2005技术内幕:T-SQL程序设计

　　本书是inside microsoft sql server 2005系列四本著作中的一本。它详细介绍了t-sql的内部构造，包含了非常全面的编程参考。它提供了使用transact-sql（t-sql）的专家级指导，t-sql是用于sql server的最常见的也是功能最强大的编程语言。该书由itzik ben-gan权威执笔，重点关注语言特性以及它们如何被sql server引擎解释和处理。
　　通过本书，你将深入了解t-sql的高级用法，包括触发器、用户自定义函数、异常处理等。该书解释并比较了sql server 2000和sql server 2005在数据库开发相关问题上的解决方案，深入讨论了sql server 2005中新增的t-sql编程特性，包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解复杂的逻辑并掌握t-sql。
　　本书适合于专业数据库开发者、bi开发者、dba和以sql server作为后台数据库的一般应用程序开发者，读者可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言，以切合实际的方案来解决复杂的实际问题。

序
前言
致谢
引言
第1章 数据类型相关的问题，xml和clr udt
1.1 datetime数据类型
datetime的存储格式
时间处理
　　datetime相关的查询问题
　1.2 与字符相关的问题
　　模式匹配
　　区分大小写(case-sensitive)的筛选器
　1.3 大型对象(large object，lob)
　　max 说明符
　　bulk行集提供程序
　1.4 隐式转换(implicit conversion)
　　标量表达式
　　筛选表达式
　1.5 基于clr的用户定义类型
　　udt理论简介

　　想必很多人都听说过该书的上一版——《Microsoft SQL Server 2000技术内幕》，这是一本在SQL Server领域被公认的经典之作。随着SQL Server 2005的发布，该书的最新版也问世了，然而就像《SQL Server 2000技术内幕》的作者Kalen Delaney所说的，SQL Server 2005太庞大了，以至于一本书无法容纳这么多的内容。因此，《SQL Server 2005技术内幕》被分成4册单独出版，同时她邀请了Itzik负责其中的T-SQL部分：T-SQL查询和T-SQL程序设计。.
　　作为《Microsoft SQL Server 2005技术内幕：T-SQL程序设计》和《Microsoft SQL Server 2005技术内幕：T-SQL查询》这两本书的译者，我有幸先睹为快，作者深厚的技术功底令我折服，以至于在完成了《Microsoft SQL Server 2005技术内幕：T-SQL程序设计》的翻译工作后又接手《Microsoft SQL Server 2005技术内幕：T-SQL查询》一书。我相信，你在阅读本书时能感觉出作者在书中所倾注的心血。本书除了囊括大量鲜为人知的技术内幕外，你还可以阅读到许多作者的经验之谈和一些非常实用的技巧。
　　在《Microsoft SQL Server 2005技术内幕：T-SQL程序设计》的第3章中，作者把T-SQL程序员的职业生涯分成三个阶段，其中的第三个阶段是最高境界，同时也是知识和经验最丰富最成熟的阶段。用作者的话说是能悟出“空”的状态，按我的理解是应该达到了“手中无剑，心中也无剑”的状态。毫无疑问，他把带领读者进入最高境界作为本书的目标，书中的各个方面都凸显出了这一点。不过，建议读者在阅读这两本书之前最好具备T-SQL查询的一些基础知识。如果大家允许，我想引用易中天老师的一句话来形容此书，该书系醇酒一坛，“度数”略高，不胜酒力者慎之！
　　本书的叙述风格正是我喜欢的那种，在介绍某项特性时，大都按这样的思路进行：先简要介绍该特性，紧接着深入分析其原理和适用的场景，然后与上一版本的该特性相比有什么优势，最后辅以大量示例，其中还有许多未包含在正式文档中的内幕消息。这充分显示了作者在技术和写作上的自信。..
　　书中大量精致的示例令人拍手叫绝，它们不仅很好地演示了相关的理论知识，而且其中的大部分示例都非常实用，所解决的问题都是很常见很棘手的问题，我个人就比较喜欢其中的利用辅助表解决问题的方法。同时，仔细研究这些示例中的算法还可以锻炼自己的逻辑思维，这有助于在实际工作中解决一些难题。从某种意义上讲，也许这种锻练比你从书中学到的技术更重要。
　　一向追求完美的Itzik Ben-Gan为保证本书的质量，对于SQL Server 2005中新增的一部分特性，他还专门邀请了相关领域的权威专家，这些专家在各自领域对SQL Server的理解无人能及，他们对这些特性描述的深入程度无须多说。他们的加盟使本书更为精彩，更为完美。
　　对于我来说，翻译这样一本经典之作是一种荣誉，也是一种压力。从一开始我便诚惶诚恐，不敢怠慢。我要特别感谢本书的作者Itzik Ben-Gan和两位特约作者Dejan Sarka和Roger Wolter，在翻译的过程中，他们给了我很大的帮助，对我所提出的诸多问题都做了详细的解释。他们的技术让我敬佩，他们的热心以及对读者负责的态度让我感动。在这里，我还要感谢胡百敬老师，通过和他的探讨与交流，使我对一些技术细节有了更深的认识。此外，因我在交稿后反复修改，给本书的编辑杨福川先生和蔡勇先生增加了很多工作。最后，感谢我的同事何明耀和胡永亮，他们为我提出了一些非常好的建议。
　　《Microsoft SQL Server 2005 技术内幕：T-SQL程序设计》一书由我一人翻译，《Microsoft SQL Server 2005 技术内幕：T-SQL查询》一书由我、唐灿、刘波共同翻译，唐灿和刘波负责翻译其中的4、5章，我负责翻译其余所有章节，并审校全书。尽管我们已尽最大努力保证翻译质量，但因水平有限，疏漏和错误在所难免，希望广大读者能够提出宝贵意见。另外，我还为这两本书的中文版创建了一个专门的论坛，网址为：http://groups.google.com/group/insidetsql。届时，我会在上面及时公布勘误及相关信息，希望能与大家一起讨论与该书有关的技术性问题。当然，你也可以通过我的电子信箱lidong6@gmail.com与我取得联系。...
　　赵立东
　　2007年5月于北京
　　

　　本书及前篇——Inside Microsoft SQL Server 2005: T-SQL Querying——介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。它们是为那些需要在SQL Server 2000和SQL Server 2005中编写和优化代码的开发人员和DBA而设计的。为简单起见，我把这两本书简称为Inside T-SQL Programming和Inside T-SQL Querying。.
　　这两本书侧重于实践中的常见问题，并讨论了解决这些问题的方法。你会从中学到大量精湛的技巧，这些技巧会充实你的工具箱和编码技能，可以让你自然而然地开发出高效的解决方案。
　　这两本书将向你揭示基于集合（set-based）查询的强大威力，并解释为什么它比使用游标的过程化编程（procedural programming）更具优势。同时，它还会教你如何识别出一些使用基于游标的解决方案优于基于集合的解决方案的场景（scenario）。
　　书中还讲述了其他几种争议较多的构造（construct），如临时表、动态执行、XML和 .NET集成——它们在具有强大功能的同时，也具有极大的风险。使用这些功能需要有非常丰富的编程经验。这两本书将教你如何明智、高效并安全地使用它们。
　　本系列的第一卷——Inside T-SQL Querying——介绍基于集合的查询，我建议你先阅读这本书。第二卷——Inside T-SQL Programming——侧重于过程化编程，它假设你阅读过第一本书或具有足够的查询知识。
　　Inside T-SQL Querying的前3章介绍逻辑查询处理（logical query processing）和物理查询处理（physical query processing）的基础知识，这是学习其他章节所必需的。
　　第1章介绍逻辑查询处理。这一章详细描述了查询处理中的各个逻辑阶段（logical phase），SQL查询所独有的特征，为适应面向集合的关系编程环境而需要转变的特殊观念。
　　第2章讲述物理查询处理。它详细描述了SQL Server引擎处理查询的方法，比较了物理查询处理和逻辑查询处理。这一章由Lubor Kollar执笔。Lubor Kollar是SQL Server 2005的group program manager，他的团队负责关系引擎（Relational Engine）的底层部分——从查询编译优化到查询执行、事务的一致性、备份/恢复及高可靠性。在表和索引分区、数据库镜像、数据库快照、快照隔离、递归查询T-SQL查询实现、数据库引擎优化顾问、创建和维护联机索引等这些SQL Server 2005中的主要特性的研发中也都有他的团队的参与。世界上很少有人能像Lubor Kollar那样了解查询优化。我很荣幸能够让优化器的设计者之一亲自解释它。..
　　第3章阐述查询优化的方法论（methodology），这是我们在Solid Quality Learning总结出来的，而且已经应用到产品系统中。这一章还介绍了如何使用索引及分析执行计划（execution plan）。本章所提供的背景知识对于理解后面章节中关于使用索引和分析执行的实践内容非常重要。它们是查询和查询优化的重要方面。
　　后面的章节深入探讨高级查询和查询优化，代码的逻辑部分和物理部分相互交叉。这些章节分别是：“子查询（Subquery）、表表达式（Table Expression）和排名函数（Ranking Functions）”，“联接（Join）和集合（Set）操作”，“聚集（Aggregating）和旋转（Pivoting）数据”（包含一节与CLR用户定义聚集函数有关的内容，作者为Dejan Sarka），“TOP和APPLY”，“数据修改（Data Modification）”，和“图（Graph）、树（Tree）、层次结构（Hierarchy）及递归查询（Recursive Queries）”。
　　附录A提供了一些纯粹的逻辑难题。你可以通过练习逻辑难题以提高你的逻辑技能。SQL查询的本质就是处理逻辑。我认为练习纯逻辑对于提高用查询解决问题的能力是非常重要的。这些题目非常有意思，也非常具有挑战性，你可以和全家一起练习。它们是我在SQL Server Magazine的T-SQL专栏中发表的题目汇总。感谢SQL Server杂志允许我向本书的读者分享这些题目。
　　本系列的第二卷——Inside T-SQL Programming介绍编程相关的T-SQL构造，以及XML处理和 .NET集成。所包含的主题有：数据类型相关的问题（包括XML和CLR用户定义类型UDT），临时表，游标，动态执行，视图，用户定义函数（包括CLR、UDF）；存储过程（包括CLR存储过程）；触发器（包括DDL和CLR触发器）；事务（包括新的快照隔离级别，错误处理及Service Broker）。
　　书中讲述XML和 .NET集成的章节（“用户定义类型”，“用户定义函数”，“存储过程”和“触发器”）由Dejan Sarka执笔。Dejan是一位SQL Server专家，他非常了解关系模型。他认为如果使用得当，这些新的构造可以适应关系模型，他在这方面的观点非常引人注目。让熟练掌握关系模型的专家来介绍该产品中这些倍受争议的领域，我认为这非常重要。所有的CLR代码示例都提供了C＃和Visual Basic .NET两个版本。
　　最后一章介绍Service Broker，这一章是Roger Wolter执笔。Roger Wolter是SQL Server团队中负责Service Broker的program manager。同样，没有比让组件的设计者亲自解释该组件更合适不过的了。
　　最后，Steve Kass是本书的技术编辑。Steve是个非常精明的人。他是SQL Server MVP，他在杜尔大学（Drew University）教授数学。他精通SQL Server和逻辑学，为本书做出了无法估量的贡献。
　　对你，本书的读者，我想说的是，SQL对我来说是科学、逻辑和艺术。我已经为这两本书准备了很久，在这两本书中倾注了我所有的激情和多年的经验。但愿它们能让你觉得既实用又非常有趣，你会和我一样发现SQL是灵感之源。如果你有任何的意见或建议，我非常乐意聆听。你可以通过这个网站http://www.insidetsql.com与我取得联系。...
　　

　　在关系数据库中，以集合（set）的方式来处理大量记录才有效率，使得 SQL（Structure Query Language）语言无法被C#/Java/Visual Basic 等面向对象语言取代。而善用数据是信息系统成功的关键因素之一，现今大量交易、数据整合、商业智能的结构性需求大增，将使SQL显得更加重要。.
　　随着 Microsoft SQL Server 版本的演进，衍生自 ANSI SQL 的 T-SQL（Transact Structure Query Language）语言变得独立且功能强大，拥有众多用户，是解决各种数据问题的主流语言。在 SQL Server 的功能大量扩增并广泛进入企业各系统后，日益庞大的数据量让 T-SQL 发挥了重要的作用。
　　虽然讨论 T-SQL 的书很多，但因为 SQL Server 2005 巨幅扩增了功能，且标准化了许多语法，将原来需要通过系统存储过程、DBCC 才能创建、设置或维护的功能，都回归到标准的 T-SQL 语法，并放宽了语法的自由度。例如：引入 .NET 对象作为 SQL Server 服务器端对象、XML 数据类型与 XQuery 语法、Service Broker 消息队列服务、以 DDL 建立系统登录与数据库用户、通过 T-SQL 维护索引、TOP 运算符可以搭配变量或子查询，以及搭配 CTE 使用在 UPDATE、DELETE 语法中、聚合运算搭配 OVER 运算符等，诸多功能在 SQL Server 2000中是无法用T-SQL办到的。因此，以往的 T-SQL 书籍将不足以讲解2005版SQL Server 的功能。
　　若论微软 SQL Server方面的最重要著作，大概非 Inside Microsoft SQL Server 系列丛书莫属了。从SQL Server 6.5 以来，一直常驻在专业 SQL Server DBA 案头的，就是对应各版本的Inside Microsoft SQL Server系列丛书了。这不是笔者的妄加赞誉，而是素有信息界诺贝尔奖称呼的图灵奖（Turing Award）得主 Jim Gray 在该系列书籍的序言中所说的。笔者本身也以读过该系列的书，代表进入了该版本的世界（例如要熟悉 SQL Server 2000，先读Inside Microsoft SQL Server 2000），以此为深入了解 SQL Server 的标准。
　　由于大幅改版与新增功能，导致以往出书速度还蛮快的 Inside Microsoft SQL Server 系列，这次让大家从 SQL Server 2005 上市后等了一年。也由于功能繁多，原本已极为厚重的 Inside Microsoft SQL Server 再也无法以单行本面世，而改为四本合集的系列丛书。先前 Inside SQL Server 各版本的作者 Kalen Delaney，在此次 Inside Microsoft SQL Server 2005 的四本书中，改任丛书编辑（Series Editor），并亲自撰写《Inside Microsoft SQL Server 2005 : The Storage Engine》一书。
　　此刻，我们需要全面地重新认识 T-SQL，够分量的书籍大概非《Inside Microsoft SQL Server 2005 : T-SQL Querying》和本书莫属。两书的作者群里，以资深的讲师与顾问 Itzik Ben-Gan 为首，包含SQL Server 2005 团队的两位产品经理，分别负责SQL Server 查询引擎和 Service Broker 的深入介绍，以及其他拥有多年经验的顾问与教师们合作撰写。因此，两本书中皆有着技术底层的详细解说，搭配各项功能的最佳应用实践。
　　这两本书主要以集合数据处理（set-based query）和程序逻辑（procedural programming）两个方面来区分。前者重视 SQL DML 语法的逻辑和效率，本书则强化搭配过程控制（如 IF、WHILE、游标等）所建构的服务器端对象，如函数、存储过程、CLR 对象、Service Broker 等。
　　笔者一直认为技术书籍与联机帮助不同的地方是技术书籍提供应用场景与阅读趣味，将技术与实际应用串起来。因为联机帮助写得像字典，若将信息语言的应用比喻为作文，则一般人很难光靠一本字典学会作文。我们需要看文章来模拟笔触、技法与结构。在学习信息技术时，自然需要实际场景和程序代码范例。而这两本书所提供的 T-SQL 范例大都简短有力，两三行就切中要旨，由范例就可看出作者的功力。对于 T-SQL 的方方面面，作者们都进行了提纲挈领、深入浅出的讲解，不管是凸显语法结构、运算符、适用性，还是建立服务器对象，都让有T-SQL基础的人能迅速抓住重点。..
　　使用T-SQL如同激荡脑力的智力测验，同一个问题让人惊叹的解法层出不穷，需要经验与知识的累积，是技术与艺术的展现。而这两本书读来让人愉悦，有如在读 T-SQL 的秘技，作者整合了不同的技术，并将它们混在一起使用。例如，以一般的 SELECT 语法查询，要将多笔记录的域值组成符号分隔的单一字符串时，会采用类似如下的做法。
　　DECLARE @c nvarchar(4000)
　　SET @c=''
　　SELECT @c=@c + CustomerID +',' FROM Customers
　　SELECT @c
　　在上述范例中，会返回 Customers 数据表中以逗号分隔所有客户编号的单一字符串。但作者在本书中，强调此种方法的不确定性，因为微软并没有明文保证此种连接字符串的方法一定可行。但由于 FOR XML 子句在 2005 版本时多了 PATH 选项，因此该组字符串可以换成如下标准的查询方式。
　　SELECT CustomerID + ',' AS [text()]
　　FROM Customers FOR XML PATH('')
　　书中充满了此种结合不同技术的意想不到之解法，让懂得关键之人顿感妙趣横生，不懂奥妙之人，仍可借鉴引用。
　　本书的目的是为了帮助你熟悉 T-SQL 语言，并通过该语言撰写 SQL Server 服务器端的对象，例如视图、用户自定义函数、存储过程、触发器等，由于未解释 T-SQL 基础语法定义，属于进阶的书籍。其内各章节大都彼此独立，且作者所拟的章节针对性很强，你可以浏览后，选择主题切入。日后在工作上需要应用到该项功能时，再回头精研作者所阐述的要旨。
　　前4章详细解释了T-SQL一般使用中让人困扰的用法，例如以字符类型描述日期时间时，最好采用 [yy]yymmdd[ hh:mm[:ss][.mmm]] 格式，例如以‘20060423 20:14:42.132’来表示时间最佳。另外还探讨了动态组织与执行 SQL 语法、临时表（Temporary Table）和表变量（Table Variable）的迷思、游标（Cursor）的正确使用方式等，针对以 T-SQL 撰写商业逻辑时常用的技巧，作者详加剖析了较佳的用法。
　　在其后的章节中，分别解释了为何要在服务器端创建视图、函数、存储过程、触发器、端点、Service Broker 等对象，以及事务管理和错误处理。书中解释了许多关于性能的议题，如第6章特别强调了用户自定义函数的正面价值在于：提供了安全、弹性以及程序的可维护性外，但它可能损伤性能。而第7章说明了存储过程虽然会因为缓存执行计划而提升性能，但若数据分布不平均，造成索引误用，依然损伤性能。因此需要小心搭配整个存储过程的 WITH RECOMPILE 选项，或是 SQL Server 2005 新提供的：单句查询语法搭配 OPTION（RECOMPILE）选项。

　　序
　　上世纪90年代后期Microsoft SQL Server 7.0发布后不久，我向微软IT部门的一位资深开发人员询问她和她的团队使用SQL Server时所用的编程语言。“过去我们用C和Visual Basic写代码，但是现在我们只使用T-SQL进行开发。”这让我非常吃惊。那时我已经从事开发新版SQL Server的工作大概已经3年了，后来我才意识到一些非常大的项目也在使用T-SQL开发。此后，微软加大了对新版SQL Server 的投入，包括对T-SQL语言的重要改进。世界上的SQL Server 实例数量成倍增长，今天很多的开发人员、数据库管理员、数据分析师、测试人员以及架构师都选择T-SQL作为开发语言，在很多时候，这也是他们唯一使用的编程工具。
　　仔细观查T-SQL的结构你会发现有两种类型的语句，其中一类是选择、插入和更新数据库中的数据的语句，另一类是提供流控制、输出、变量声明和处理以及我们在大部分非数据库编程语言可以找到的其他功能。SELECT、INSERT、SEND和RECEIVE属于第一类，DECLARE、 BEGIN、 IF、ELSE、 WAITFOR和PRINT属于第二类。Itzik Ben-Gan根据两类语句之间的大概界线把T-SQL分到两本书——Inside Microsoft SQL Server 2005: T-SQL Quexying和Inside Microsoft SQL Server 2005: T-SQL Programming。
　　这两本书都不是一个完整的语言参考，也没有必要写这样一本书，因为你可以从微软网站http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx下载语言参考。相反，Itzik Ben-Gan利用他丰富的T-SQL教学经验把非常复杂的主题包括在他的书中，还包含SQL Server 2005中新增的功能。让教师写书的另一个优势是他已经向他的学生讲解过无数个与这些主题相关的问题。Itzik Ben-Gan非常巧妙地利用这些经验呈现这些素材，就好像回答问题一样，同时书中还包含大量的示例和技巧。我最喜欢的技巧在第一章。它解释了如何使用一个小测试表确定对一个大表执行的ALTER TABLE语句会花费几秒还是几小时。这个重要的技巧会是一个真正的救生员（或是事业提高者）。但要注意，不要忘记使用一个非空的测试表!
　　本书中我最喜欢的示例是第4章动态SQL中的动态Pivot。SQL Server 2005引入了PIVOT 和UNPIVOT语句，但它们无法处理未知元素数量的旋转。这个限制倍受用户批评。Itzik Ben-Gan向你展示了如何在SQL Server 2005 中利用一个由3个语句组成的批处理（如果不算变量声明）实现动态pivoting。如果你使用的不是SQL Server的最新版本，你会在本书中找到SQL Server 2005之前版本的动态pivot的批处理方法。它使用了10条语句，通过比较这两个版本的动态pivot，你会发现SQL Server 2005的强大的编程能力。本书包括SQL Server 2005之前版本的代码段和命令方法，这使得本书对那些还没使用SQL Server 2005的开发人员来说也非常具有吸引力，他们可以直接使用2005之前版本的解决方案。
　　《Microsoft SQL Server 2005技术内幕：T-SQL程序设计》这本书包含了大量实用的代码示例，你可以很轻易地把它们应用到自己的工作中。本书的作者除了关注示例的正确性之外，还考虑到安全、性能及潜在的阻塞等因素。因此，本书所包含的大量示例的查询计划都经过检查并与其他计划作过比较。例如，在第9章中，你会发现关于如何使用动态管理视图（DMV）以检测阻塞的场景。DMV是SQL Server 2005中新引入的，它提供了关于服务器状态的信息，这些信息可以用于监视服务器实例的状态、诊断问题和调整性能。
　　SQL Server是一个复杂的产品，几乎任何问题都不止一种解决方法。Itzik Ben-Gan及合著者彻底地检查每个问题的多种选择并解释应该如何从中做出选择。有时一种方法通常更好些，但经常有利也有弊，这时作者会给出做出正确选择的标准。例如，在第1章你将学习在T-SQL中表示2006年2月12日时，为什么应该使用'20060212'而不是'02/12/06' 或 '12/02/06'。在第2章比较表变量和临时表。在第3章你将学习到如何使用游标，以及更为重要是，什么时候不应该使用游标。第4章描述了EXEC和sp_executesql之间的区别。在接下来的第5章中，你将学习什么时候应用使用公用表表达式（CTE），它是SQL Server 2005中新增的，可以代替创建视图。第7章介绍存储过程，解释了为什么对象名称应该限定架构。第8章阐明了出于性能考虑，什么时候应该把触发器中的inserted或deleted表中的内容转移到临时表中。在第11章你将学习什么时候使用Service Broker，以及什么时候使用MSMQ或BizTalk更好。这份清单远未详尽，只是让你品味一下本书的内容。