SQL Server 2012 编程入门经典(第4版)


SQL Server 数据库经典译丛 SQL Server 2012 编程入门经典 (第 4 版) [美] Paul Atkinson Robert Vieira 著 王 军 牛志玲 译 北 京 Paul Atkinson, Robert Vieira Beginning Microsoft SQL Server 2012 Programming EISBN:978-1-118-10228-2 Copyright © 2012 by John Wiley & Sons, Inc., Indianapolis, Indiana All Rights Reserved. This translation published under license. 本书中文简体字版由 Wiley Publishing, Inc. 授权清华大学出版社出版。未经出版者书面许可,不得以任何 方式复制或抄袭本书内容。 北京市版权局著作权合同登记号 图字:01-2012-6872 Copies of this book sold without a Wiley sticker on the cover are unauthorized and illegal. 本书封面贴有 Wiley 公司防伪标签,无标签者不得销售。 版权所有,侵权必究。侵权举报电话:010-62782989 13701121933 图书在版编目(CIP)数据 SQL Server 2012 编程入门经典(第 4 版)/(美)阿特金森(Atkinson, P.),(美)维埃拉(Vieira, R.)著;王军, 牛志玲 译. —北京:清华大学出版社,2013.4 (SQL Server 数据库经典译丛) 书名原文:Beginning Microsoft SQL Server 2012 Programming ISBN 978-7-302-31651-0 Ⅰ. ①S… Ⅱ. ①阿… ②维… ③王… ④牛… Ⅲ. ①关系数据库系统—程序设计 Ⅳ. ①TP311.138 中国版本图书馆 CIP 数据核字(2013)第 040797 号 责任编辑:王 军 刘伟琴 装帧设计:牛静敏 责任校对:成凤进 责任印制: 出版发行:清华大学出版社 网 址:http://www.tup.com.cn,http://www.wqbook.com 地 址:北京清华大学学研大厦 A 座 邮 编:100084 社 总 机:010-62770175 邮 购:010-62786544 投稿与读者服务:010-62776969,c-service@tup.tsinghua.edu.cn 质 量 反 馈:010-62772015,zhiliang@tup.tsinghua.edu.cn 印 刷 者: 装 订 者: 经 销:全国新华书店 开 本:185mm×260mm 印 张:44.5 字 数:1111 千字 版 次:2013 年 4 月第 1 版 印 次:2013 年 4 月第 1 次印刷 印 数:1~4000 定 价:89.00 元 ————————————————————————————————————————————— 产品编号: 作 者 简 介 Paul Atkinson 自从 20 世纪 90 年代早期在 Microsoft 公司工作以来,就在研究数据库技术, 他在 Microsoft 公司先把 Microsoft Access 作为一种辅助项目来学习,后来将它看做一种有代表 性的支持技术。在此期间他还获得了波特兰州立大学的计算机科学学位,作为一名 DBA 在.com 上冲浪,在网络泡沫破灭后则成为医疗行业的一位承包商。目前,Paul 在休伦医疗集团任 BI 建筑师和团队带头人,负责开发传统和实时的 BI 解决方案。他开设的高性能 T-SQL 编程课程 是休伦湖最受欢迎的课程设置。Paul 与妻子 Kristin、女儿 Maureen 住在俄勒冈州的波特兰市。 Paul 不工作或写书时,就在几个足球队踢球,或者骑山地车、滑雪、当厨师,骑自行车上下班, 他还是学校委员会的主席、Timbers 军队的资深成员和半个巧克力制作大师。就像 Robert Heinlein 写的那样,他的特长都与昆虫相关。 Robert Vieira 是休伦咨询集团的软件架构师,是 Microsoft SQL Server 方面顶级权威的一位 专家。他在国内会议上发言,其教学和写作中的独特教学方法非常有名。 技术编辑简介 Eric Aldinger 是一位品质保证工程师,来自俄勒冈州的波特兰市。他花了许多时间研究绿 地开发项目的数据集成和数据存储问题。Eric 编辑本书,证明了一个不大可能证明的公理:只 要有足够的时间,加州大学的文学学位在学术界之外是有用的。Eric 有许多嗜好,但本书读者 不会对它们感兴趣。 致 谢 没有人能独立完成这样一本厚书。精雕细琢一本可读性强的技术性图书,因为其中有一些 相当枯燥的内容,所以要花大量的时间和精力,而这些时间和精力并不是来自于作者,而是来 自于支持、引导和鼓励他的人。许多人都提供了帮助,但首先要感谢如下几个人,没有他们, 我就不可能完成本书。 Rob Vieira 以前就帮助我编写了一些以开发人员为中心的 SQL Server 图书,他在这个主题 上的贡献超过了所有人——甚至包括我。除了建立了本书的框架之外,Rob 还是一位良师益友, 没有他,我就不可能开始本书的编写。他一定会很高兴我完成了本书,因为他可能已经厌烦我 在 6 点之前就回家写书去了。 Bob Elliott 仅根据推荐就给了我编写本书的机会(尽管是个好建议)。 Kristin Atkinson 是我的妻子,我们结婚已 15 年了,自从我开始编写本书,她就承担了所 有的家务。每天晚上我回到家,坐下来写本,她就会照管其他的事情。她不仅为我扫清了道路, 还在我试探地提出意见时做出很好的回应。Kristin 也为本书付出了不少心血,现在她对 SQL Server 的了解可能超过了我知道的其他技术人员。 Maureen Spears 对我这个新手犯的错误和提出的问题非常宽容,对我的拖沓也很理解。他 的帮助、意见和反馈使我的写作激情一直很高昂。 Eric Aldinger 满腔热情地接受了技术编辑的职务,确保这个主题的勘误部分非常干净。走 到了这一步,我们花在技术性图书上面的时间就比较少,而能用更多的时间去骑车了。 前 言 所谓“旧的不去,新的不来”。这个主题多年来都是 Rob Vieira 的,但现在他不再继续,所 以我用自己的一章新内容——本书的第 1 章——作为开始。同样,读者也要进行某种转换,您 可能了解一些编程知识,目前正打算开始学习关系数据库编程,尤其是 Microsoft SQL Server 2012。 数据库编程与大多数其他软件工程有很大的区别。如果您像我一样,也是从早期的编程工 作(基于 6502 计算机上的 BASIC 语言)转到数据库上的。我是 20 世纪 90 年代早期在 Microsoft 公司工作时,为了支持雷达的一个测试版产品,需要在 Access(当时是 2.0 版本)中跟踪我所做 工作的某些方面而转到数据库上的。我学习了数据库后,就被吸引住了,在 Access 支持团队中 工作的那几年(也学习了 SQL Server 4.2 及以上版本),我掌握了这门技术。 使用 SQL Server 是与普通的过程式编程完全不同的体验,需要用数学和集合术语来思考, 学习如何仔细地确定需要什么,而不必明确说明如何完成任务。从过程式编程转向这种思考方 式时,如果没有获得任何帮助,就好像不借助离合器就来个大转弯。然而 SQL 这种语言比较简 单,一旦学会,使用起来非常愉快。 我向内行、Microsoft 的内部培训师和同事们学习,当时希望能有一本书介绍概念和函数知 识,来帮助我理解我看到的东西,了解当时我还不知道的知识。而本书就是我希望阅读的书, 这意味着如果您处于 T-SQL 的早期学习阶段,您可能也需要本书。 这是一本循序渐进的教程,以有序的方式介绍了小代码段所需要的概念,每个小代码段都 建立在上一个小代码段的基础上。这样编写的原因是,读者可能很难从在线帮助数据库中选择 出相关主题,学习到这样完整的新概念集合。在 2012 年,图书仍是学习新概念的重要方式。 本书尽可能覆盖了 SQL Server 的所有核心元素,希望它能与先前编写的 Professional SQL Server Programming 一书一样成功。在学习完本书后,读者应继续学习,成为一名高级的 SQL Server 编程人员,并在未来的几年中能从容面对数据库编程的独特挑战。 0.1 本书读者对象 本书书名中的“入门”一词容易引起误解。本书的确非常适合初学者,但它也适合入门之 后的进一步学习。其内容是初学者所必须了解的,但由于其内容繁多,读者不可能全部记牢, 因此您可在需要时回顾并参考相关内容,这对于中、高级用户来说也同样适用。 初学者一般希望从头开始学习。而本书主要内容都是按“需要掌握”哪类知识来编排的。 除了 XML、Reporting Services 和 Integration Services 之外,本书的每一章都是易于理解的基本 内容,这样读者在遇到 SQL Server 问题时,很容易找到相关内容来学习。甚至这 3 个主题也是 SQL Server 开发人员所必须掌握的。 SQL Server 2012 编程入门经典(第 4 版) IV 对于中级用户,可以跳过本书的前 7 章或前 8 章。但我们仍建议浏览这几章,以弥补这方 面知识的漏洞。也可以先浏览一下这几章,然后快速跳过熟悉的内容,去阅读不了解的内容。 高级用户除了可利用本书作为很好的参考资料之外,可能会希望学习第 12 章以及后面的 章节。事实上,第 12 章以后的内容更加新颖(如新的调试、事务、XML、BI、Reporting Services 以及 Integration Services 内容等)。 0.2 本书内容简介 从本书书名可以看出,本书涵盖的 SQL Server 2012 的内容是明确地面向开发人员的。 SQL Server 2012 是发展 20 余年的数据库管理系统的最新版本。它是在对 SQL Server 7.0 版本重新设计的基础上建立的——这个版本添加了一些强大的分析和报表功能。本书着重满足 各层次开发人员的核心编程需求。另外,本书主要围绕 SQL Server 2012 版本讲解,但也会涉 及向后兼容性的问题,因为这可能影响设计或编码选择。 0.3 本书内容安排 本书内容按照从基本到高级的顺序安排,同时假定您已经是富有经验的开发人员(除了数据 库方面)。要学习本书,您必须具有编程的基本知识,如变量、数据类型以及过程式编程。当然, 您不必学过查询。 本书尤其适合于开发人员使用。为了简洁起见,本书会简化或完全忽略那些面向数据库管 理员而非开发人员的内容。当然本书也涉及数据库管理的问题,因为这些问题在开发过程中需 要考虑到,本书将在第 21 章对几个数据库管理相关的问题作简单的介绍。 在客户端开发方面,本书尽量做到与语言无关。通常忽略所使用的语言,如 VB、C#、C++、 Java,以及其他语言(本书侧重于服务器端的开发),而在涉及时也对不同语言一视同仁。 在学习顺序方面,首先学习 SQL 基本对象,然后是基本的查询和连接知识。接着学习数据 库的其他对象,并讨论与数据库物理设计有关的内容。然后学习如何编写更健壮的 SQL Server 脚本代码、存储过程、用户自定义函数与触发器。在简要介绍商务智能后,再进一步学习 SQL Server 的一些外围功能。本书最后要学习的是数据库管理方面的重要内容,有助于维护用户开 发的数据库。 注意:本书是一个教程,在网上还包括一些参考内容,读者学习完了本书后, 可能希望了解这些内容。这部分内容包括系统函数的完整参考和一个非常简单 的连接示例文档,它们可在 www.wrox.com 上获得。在该站点上,根据本书英 文版的 ISBN 978-1-118-102282 来搜索。 前 言 V 0.4 使用本书的条件 使用本书需要安装 SQL Server。由于本书大量使用各种 SQL Server 2012 管理工具,因此推 荐安装包含所有模块功能的 SQL Server 版本,而不是 SQL Server Express。虽说如此,本书还是 侧重于介绍脚本编写,因此即使是 SQL Server Express 用户,也能学习本书的大部分内容。另外 还需要 AdventureWorks 样本数据库,用于 BI 和报表的 AdventureWorks 数据库,以及安装一些自 定义数据库。访问这些数据库的指令参见本书配套网站(www.wrox.com)上的 ReadMe 文件。 虽然 Visual Studio 对于学习本书也很有用,但是所需要的大部分 Visual Studio 特性都包括 在 SQL Server 产品附带的 Business Intelligence Studio 中。 0.5 源代码 在练习书中的示例时,可以选择手动输入代码或者使用本书附带的源代码文件。书中 用到的所有源代码都可以从 www.wrox.com 下载。进入站点 http://www.wrox.com 后,只需 要找到本书的英文书名(使用 Search 搜索框或书名列表),单击本书详细信息页面上的 Download Code 链接,就可以得到本书所有的源代码。 就 下载完代码后,用您喜欢的压缩工具把它解压缩。此外,也可以去 Wrox 的主下载页 面 www.wrox.com/dynamic/books/download.aspx 找到本书或 Wrox 出版的其他书籍的代码。 0.6 勘误表 尽管我们竭尽所能来确保在正文和代码中没有错误,但人无完人,错误难免会发生。 如果您在 Wrox 出版的书中发现了错误(例如拼写错误或代码错误),我们将非常感谢您的反 馈。发送勘误表将节省其他读者的时间,同时也会帮助我们提供更高质量的信息。 要找到本书的勘误表页面,可以进入 www.wrox.com,使用 Search 搜索框或书名列表 定位本书,然后在本书的详细信息页面上单击 Book Errata 链接。在这个页面上可以查看为 本书提交的、Wrox 编辑粘贴上去的所有错误。完整的书名列表(包括每本书的勘误表)也可 以从 www.wrox.com/misc-pages/booklist.shtml 上获得。 如果您在本书的勘误页面上没有看到您发现的错误,可以到 www.wrox.com/contact/ techsupport.shtml 上填写表单,把您发现的错误发给我们。我们会检查这些信息,如果属实, 就把它添加到本书的勘误页面上,并在本书随后的版本中更正错误。 注意:因为很多书的书名都相似,所以用英文版的 ISBN 搜索更为容易。本书 英文版的 ISBN 是 978-1-118-10228-2。 SQL Server 2012 编程入门经典(第 4 版) VI 0.7 p2p.wrox.com 如果想和作者或同行进行讨论,请加入 http://p2p.wrox.com 上的 P2P 论坛。该论坛是 一个基于 Web 的系统,您可以发布有关 Wrox 图书及相关技术的消息,与其他读者或技术 人员交流。该论坛提供了订阅功能,当您感兴趣的主题有新帖子发布时,系统会发送邮件 来通知。Wrox 的作者、编辑、其他业界专家和像您一样的读者都会出现在这些论坛中。 在 http://p2p.wrox.com 网站上,您会找到很多不同的论坛,它们不但有助于您阅读本 书,还有助于开发自己的应用程序。加入论坛的步骤如下: (1) 进入 http://p2p.wrox.com,单击 Register 链接。 (2) 阅读使用条款,然后单击 Agree 按钮。 (3) 填写加入该论坛必需的信息和其他您愿意提供的信息,单击 Submit 按钮。 (4) 您将收到一封电子邮件,描述如何验证您的账户和完成加入过程。 加入之后,就可以发布新的消息和回复其他用户发布的消息。可以随时在 Web 上阅读 论坛里的消息。如果想让某个论坛的新消息以电子邮件的方式发给您,可以单击论坛列表 中论坛名称旁边的 Subscribe to this Forum 图标。 要了解如何使用 Wrox P2P 的更多信息,请阅读 P2P FAQ,其中回答了论坛软件如何 使用的问题,以及许多与 P2P 和 Wrox 图书相关的问题。要阅读 FAQ,单击任何 P2P 页面 上的 FAQ 链接即可。 注意:不加入 P2P 也可以阅读论坛里的消息。但是如果要发布自己的消息, 就必须加入。 目 录 第 1 章 RDBMS 基础:SQL Server 数据 库的构成········································1 1.1 数据库对象概述···························2 1.1.1 数据库对象································2 1.1.2 事务日志····································6 1.1.3 最基本的数据库对象:表·········7 1.1.4 文件组 ·······································8 1.1.5 数据库关系图····························8 1.1.6 视图 ···········································9 1.1.7 存储过程····································9 1.1.8 用户自定义函数······················10 1.1.9 序列 ·········································10 1.1.10 用户和角色····························10 1.1.11 规则 ·······································10 1.1.12 默认值 ···································11 1.1.13 用户自定义数据类型 ············11 1.1.14 全文目录································11 1.2 SQL Server 数据类型 ················· 11 1.3 SQL Server 对象标识符 ·············15 1.3.1 需要命名的对象······················16 1.3.2 命名规则··································16 1.4 本章小结·····································17 第 2 章 SQL Server 管理工具·················19 2.1 使用联机丛书获得帮助 ·············20 2.2 SQL Server 配置管理器 ·············21 2.2.1 服务管理··································21 2.2.2 网络配置··································21 2.2.3 协议 ·········································23 2.2.4 客户端 ·····································24 2.3 SQL Server Management Studio ········································· 26 2.3.1 启动 Management Studio·········27 2.3.2 通过“查询”窗口交互··········30 2.4 SQL Server Data Tools (以前称为 BIDS) ······················· 36 2.5 SQL Server 集成服务(SSIS)······· 37 2.6 SQL Server Reporting Services (SSRS)········································ 37 2.7 SQL Server Analysis Services (SSAS)········································ 38 2.8 Bulk Copy Program(bcp)············· 38 2.9 SQL Server Profiler····················· 38 2.10 sqlcmd······································· 39 2.11 PowerShell ································ 39 2.12 本章小结··································· 39 第 3 章 T-SQL基本语句··························41 3.1 基本 SELECT 语句 ···················· 42 3.1.1 SELECT 语句与 FROM 子句 ···························42 3.1.2 WHERE 子句 ··························46 3.1.3 ORDER BY 子句·····················48 3.1.4 使用 GROUPBY 子句 聚合数据 ·······························51 3.1.5 使用 HAVING 子句给分组 设置条件·································59 3.1.6 使用 FOR XML 子句 输出 XML ·····························61 3.1.7 通过 OPTION 子句 利用提示·································61 SQL Server 2012 编程入门经典(第 4 版) VIII 3.1.8 DISTINCT 和 ALL 谓词 ·········61 3.2 使用 INSERT 语句添加数据······64 3.2.1 多行插入··································69 3.2.2 INSERT INTO… SELECT 语句 ··························69 3.3 用 UPDATE 语句更改获得的 数据············································71 3.4 DELETE 语句·····························73 3.5 本章小结·····································75 第 4 章 连接··············································77 4.1 连接 ············································78 4.2 内部连接·····································79 4.3 使用外部连接检索更多数据 ·····86 4.3.1 简单的外部连接······················86 4.3.2 处理更复杂的外部连接 ··········92 4.4 完全连接·····································97 4.5 交叉连接·····································98 4.6 JOIN 语句的早期语法结构······ 100 4.6.1 内部连接的早期语法结构···· 100 4.6.2 外部连接的早期语法结构···· 101 4.6.3 交叉连接的早期语法结构···· 102 4.7 联合 ·········································· 103 4.8 本章小结··································· 106 第 5 章 创建和修改数据表 ··················· 109 5.1 SQL Server 中的对象名 ··········· 109 5.1.1 模式名(也称为所有权)········· 110 5.1.2 数据库名······························· 112 5.1.3 通过服务器命名··················· 113 5.1.4 回顾默认值··························· 113 5.2 CREATE 语句··························· 113 5.2.1 CREATE DATABASE··········· 114 5.2.2 构建数据库··························· 118 5.2.3 CREATE TABLE ·················· 120 5.3 ALTER 语句 ····························· 131 5.3.1 ALTER DATABASE ············· 131 5.3.2 ALTER TABLE······················135 5.4 DROP 语句······························· 139 5.5 使用 GUI 工具·························· 139 5.5.1 使用 Management Studio 创建数据库···························140 5.5.2 回到代码:使用 Management Studio 创建脚本····················144 5.6 本章小结··································· 145 第 6 章 键和约束··································· 147 6.1 约束的类型······························· 148 6.1.1 域约束···································148 6.1.2 实体约束 ·······························149 6.1.3 参照完整性约束····················149 6.2 约束命名··································· 149 6.3 键约束······································· 150 6.3.1 主键约束 ·······························150 6.3.2 外键约束 ·······························153 6.3.3 唯一约束 ·······························164 6.4 CHECK 约束 ···························· 165 6.5 DEFAULT 约束 ························ 166 6.5.1 在 CREATE TABLE 语句中 定义 DEFAULT 约束············167 6.5.2 在已存在的表中添加 DEFAULT 约束 ····················167 6.6 禁用约束··································· 168 6.6.1 在创建约束时忽略无效的 数据 ······································168 6.6.2 临时禁用已存在的约束········171 6.7 规则和默认值··························· 172 6.7.1 规则·······································173 6.7.2 删除规则 ·······························174 6.7.3 默认值···································174 6.7.4 删除默认值 ···························175 6.7.5 确定哪些表和数据类型使用 给定的规则或默认值 ···········175 目 录 IX 6.8 用于实现数据完整性的 触发器 ······································ 176 6.9 如何选择··································· 176 6.10 本章小结································· 177 第 7 章 更复杂的查询 ··························· 179 7.1 子查询的概念··························· 180 7.2 关联子查询······························· 184 7.2.1 关联子查询的工作原理 ······· 184 7.2.2 处理 NULL 数据—— ISNULL 函数······················· 188 7.3 派生表······································· 189 7.4 使用通用表表达式(CTE) ········· 192 7.4.1 使用 WITH 关键字··············· 192 7.4.2 使用多个 CTE ······················ 194 7.4.3 递归 CTE······························ 195 7.5 使用 EXISTS 运算符················ 195 7.5.1 使用 EXISTS 筛选数据········ 195 7.5.2 以其他方式使用 EXISTS····· 197 7.6 混合数据类型: CAST 和 CONVERT················ 199 7.7 使用 MERGE 命令同步数据 ··· 202 7.7.1 实际使用 MERGE 命令 ······· 202 7.7.2 BY TARGET 和 BY SOURCE ························ 205 7.8 使用 OUTPUT 子句 收集受影响的行 ······················ 206 7.9 研究窗口化函数······················· 208 7.9.1 ROW_NUMBER··················· 208 7.9.2 RANK、DENSE_RANK 和 NTILE······························ 211 7.10 一次一个数据块: 特殊的查询分页 ···················· 212 7.11 性能方面的考虑 ····················· 213 7.11.1 测量性能··························· 214 7.11.2 JOIN、子查询和 CTE 的对比························ 215 7.12 本章小结································· 216 第 8 章 规范化和其他基本设计问题···· 219 8.1 理解表······································· 219 8.2 保持数据“规范”··················· 220 8.2.1 准备工作 ·······························221 8.2.2 第一范式 ·······························222 8.2.3 第二范式 ·······························225 8.2.4 第三范式 ·······························226 8.2.5 其他范式 ·······························228 8.3 理解关系··································· 229 8.3.1 一对一关系 ···························229 8.3.2 零或一对一关系····················230 8.3.3 一对一或一对多关系············230 8.3.4 一对零、一对一或 一对多关系···························231 8.3.5 多对多关系 ···························232 8.4 数据库关系图··························· 234 8.4.1 表···········································236 8.4.2 在数据库关系图中 创建关系·······························242 8.5 反规范化··································· 245 8.6 超出规范化的一些规则 ··········· 245 8.6.1 保持简单 ·······························245 8.6.2 选择数据类型 ·······················246 8.6.3 尽量进行存储 ·······················246 8.7 创建一个快速示例··················· 247 8.7.1 创建数据库 ···························247 8.7.2 添加数据库关系图和 初始表 ··································247 8.7.3 添加关系 ·······························251 8.7.4 添加一些约束 ·······················253 8.8 本章小结··································· 253 第 9 章 SQL Server 存储和索引 结构 ·········································· 255 9.1 SQL Server 存储机制 ··············· 255 SQL Server 2012 编程入门经典(第 4 版) X 9.1.1 数据库 ·································· 255 9.1.2 区段 ······································ 256 9.1.3 页 ·········································· 256 9.1.4 行 ·········································· 257 9.1.5 稀疏列 ·································· 257 9.2 理解索引··································· 258 9.2.1 平衡树(B-树) ························ 259 9.2.2 SQL Server 中的数据 访问方式······························ 261 9.3 创建、修改和删除索引 ··········· 269 9.3.1 CREATE INDEX 语句·········· 269 9.3.2 创建 XML 索引···················· 274 9.3.3 随约束创建的隐含索引 ······· 275 9.3.4 在稀疏列和地理空间列上 创建索引······························ 275 9.4 明智地选择——在何时何地 使用何种索引··························· 276 9.4.1 选择性 ·································· 276 9.4.2 注意成本:少即是多 ··········· 279 9.4.3 选择群集索引······················· 279 9.4.4 列顺序问题··························· 281 9.4.5 覆盖索引······························· 282 9.4.6 过滤索引······························· 282 9.4.7 修改索引······························· 283 9.4.8 删除索引······························· 286 9.4.9 从查询计划中获取提示 ······· 286 9.4.10 索引未被使用的原因 ········· 286 9.4.11 使用数据库引擎 调整向导···························· 287 9.5 维护索引··································· 287 9.5.1 碎片 ······································ 288 9.5.2 确定碎片和页拆分的 可能性··································· 288 9.6 本章小结··································· 292 第 10 章 视图········································· 295 10.1 创建简单的视图 ··················· 295 10.2 使用 T-SQL 编辑视图 ·········· 307 10.3 删除视图······························· 307 10.4 在 Management Studio 中 创建和编辑视图 ·················· 307 10.4.1 在 Management Studio 中创建视图····················308 10.4.2 在 Management Studio 中 编辑视图 ·······················310 10.5 审查:显示现有的代码 ······· 311 10.6 保护代码:加密视图 ··········· 312 10.7 关于模式绑定······················· 314 10.8 使用 VIEW_METADATA 使 视图看起来像表一样··········· 314 10.9 索引(物化)视图 ···················· 314 10.10 索引聚集视图····················· 317 10.11 本章小结····························· 320 第 11 章 编写脚本和批处理 ················· 323 11.1 脚本的基础知识 ··················· 324 11.1.1 使用 USE 语句选择 数据库环境····················324 11.1.2 声明变量························325 11.1.3 设置变量中的值 ············326 11.1.4 系统函数回顾················328 11.1.5 检索标识值····················329 11.1.6 生成序列························333 11.1.7 使用@@ROWCOUNT ···336 11.2 将语句分组到批处理中 ······· 337 11.2.1 自成一行························338 11.2.2 每个批处理单独发送到 服务器 ···························338 11.2.3 GO 不是 T-SQL 命令·····339 11.2.4 批处理中的错误 ············339 11.2.5 何时使用批处理 ············339 11.3 从命令提示符运行: sqlcmd ·································· 343 目 录 XI 11.4 动态 SQL:用 EXEC 命令 动态生成代码 ······················ 347 11.4.1 动态生成代码 ··············· 347 11.4.2 理解动态 SQL 的 危险性··························· 349 11.5 使用控制流语句 ··················· 353 11.5.1 IF…ELSE 语句 ············· 353 11.5.2 CASE 语句···················· 357 11.5.3 用 WHILE 语句 进行循环······················· 362 11.5.4 WAITFOR 语句············· 364 11.5.5 使用 TRY/CATCH 块 处理错误 ······················· 364 11.6 本章小结······························· 367 第 12 章 存储过程································· 369 12.1 创建存储过程: 基本语法······························ 370 12.2 使用 ALTER 修改 存储过程······························ 371 12.3 删除存储过程······················· 372 12.4 参数化存储过程 ··················· 372 12.4.1 声明参数······················· 372 12.4.2 通过返回值确认成功或 失败 ······························ 378 12.4.3 如何使用 RETURN······· 378 12.5 错误处理······························· 380 12.5.1 处理内联错误 ··············· 381 12.5.2 利用@@ERROR··········· 382 12.5.3 在存储过程中使用 @@ERROR ·················· 383 12.5.4 在错误发生前 处理错误······················· 386 12.5.5 手动引发错误 ··············· 392 12.5.6 重新抛出错误 ··············· 396 12.5.7 添加自定义的 错误消息······················· 397 12.6 存储过程的优点··················· 399 12.6.1 创建可调用的进程 ········399 12.6.2 为了安全性而使用 存储过程 ·······················399 12.6.3 存储过程和性能············400 12.7 扩展存储过程(XP) ··············· 402 12.8 递归简介······························· 402 12.9 调试······································· 404 12.9.1 启动调试器····················405 12.9.2 调试器的组成················405 12.9.3 使用断点进行中断 ········408 12.9.4 使用调试器····················410 12.10 理解.NET 程序集 ··············· 413 12.11 使用存储过程的时机 ········· 413 12.12 本章小结····························· 414 第 13 章 用户自定义函数····················· 417 13.1 UDF 的定义·························· 417 13.2 返回标量值的 UDF ·············· 418 13.3 返回表的 UDF······················ 424 13.3.1 内联 UDF·······················424 13.3.2 理解确定性····················430 13.4 调试用户自定义函数 ··········· 433 13.5 数据库中的.NET ·················· 433 13.6 本章小结······························· 434 第 14 章 事务和锁································· 435 14.1 事务······································· 435 14.1.1 ACID 事务·····················436 14.1.2 事务操作简介················436 14.1.3 BEGIN TRAN················437 14.1.4 COMMIT TRAN············437 14.1.5 ROLLBACK TRAN·······437 14.1.6 SAVE TRAN ··················437 14.2 SQL Server 日志的 工作方式······························ 439 14.2.1 失败和恢复····················440 SQL Server 2012 编程入门经典(第 4 版) XII 14.2.2 激活隐式事务 ··············· 442 14.3 锁和并发······························· 442 14.3.1 锁的用途······················· 443 14.3.2 可以锁定的资源 ··········· 446 14.3.3 锁升级和锁对性能的 影响 ······························ 446 14.3.4 锁定模式······················· 447 14.3.5 锁的兼容性 ··················· 448 14.3.6 指定一种特有的锁定 类型——优化器提示 ··· 449 14.4 设置隔离级别······················· 450 14.4.1 READ COMMITTED···· 451 14.4.2 READ UNCOMM- ITTED··························· 451 14.4.3 REPEATABLE READ ··· 452 14.4.4 SERIALIZABLE ··········· 452 14.4.5 SNAPSHOT ·················· 452 14.5 处理死锁(1205 错误)············ 453 14.5.1 SQL Server 判断死锁的 方式 ······························ 453 14.5.2 选择死锁牺牲者的 方式 ······························ 453 14.5.3 避免死锁······················· 453 14.6 本章小结······························· 456 第 15 章 触发器····································· 459 15.1 触发器的概念······················· 460 15.1.1 ON 子句 ························ 461 15.1.2 WITH ENCRYPTION 选项 ······························ 461 15.1.3 FOR | AFTER ················ 462 15.1.4 FOR|AFTER 子句与 INSTEAD OF 子句 ······· 463 15.1.5 NOT FOR REPLICATION 选项····· 464 15.1.6 AS 子句························· 464 15.2 使用触发器实施数据 完整性规则 ·························· 464 15.2.1 处理来自于其他表的 需求 ·······························465 15.2.2 使用触发器来检查 更新的变化····················466 15.2.3 将触发器用于自定义 错误消息 ·······················468 15.3 触发器的其他常见用途 ······· 468 15.4 触发器的其他问题 ··············· 468 15.4.1 触发器可以嵌套············469 15.4.2 触发器可以递归············469 15.4.3 触发器不能防止体系 结构的修改····················469 15.4.4 可以在不删除的情况下 关闭触发器····················470 15.4.5 触发器的激活顺序 ········470 15.5 INSTEAD OF 触发器··········· 472 15.6 性能考虑······························· 472 15.6.1 触发器的被动性············472 15.6.2 触发器与激活的进程 之间不存在并发问题····472 15.6.3 使用 IF UPDATE()和 COLUMNS_ UPDATED()···················473 15.6.4 保持触发器短小精悍 ····475 15.6.5 选择索引时不要忘记 触发器 ···························475 15.6.6 不要在触发器中 进行回滚 ·······················475 15.7 删除触发器··························· 476 15.8 调试触发器··························· 476 15.9 本章小结······························· 477 第 16 章 初识 XML································ 479 16.1 XML 基础····························· 480 16.1.1 XML 文档的各个 组成部分 ·······················481 16.1.2 名称空间························489 目 录 XIII 16.1.3 元素内容······················· 491 16.1.4 有效与格式良好—— 架构和 DTD·················· 492 16.2 SQL Server 提供的 XML 支持 ···························· 492 16.2.1 将列定义为 XML 类型 ······························ 493 16.2.2 XML 架构集合 ············· 494 16.2.3 创建、修改和删除 XML 架构集合 ············· 496 16.2.4 XML 数据类型方法······ 498 16.2.5 强制执行架构集合 之外的约束··················· 504 16.2.6 用 XML 格式检索 关系数据······················· 505 16.2.7 RAW 选项····················· 506 16.2.8 AUTO 选项 ··················· 509 16.2.9 EXPLICIT 选项············· 510 16.2.10 PATH 选项··················· 512 16.2.11 OPENXML 函数 ········· 517 16.3 XSLT 简介 ···························· 521 16.4 本章小结······························· 523 第 17 章 商务智能基础························· 525 17.1 商务智能的概念 ··················· 525 17.1.1 BI 优于其他方案 ·········· 526 17.1.2 根据数据做出决策········ 526 17.1.3 不同的专家有不同的 观点 ······························ 528 17.1.4 通过过去的业绩预测 未来的行为··················· 528 17.1.5 谁使用 BI······················ 528 17.2 数据仓库······························· 528 17.2.1 数据仓库的目标 ··········· 529 17.2.2 OLTP 与 OLAP ············· 529 17.2.3 Kimball 与 Inmon·········· 530 17.3 维度建模:为什么是 规范化的······························ 533 17.3.1 度量值和度量指标 ········533 17.3.2 事实 ·······························534 17.3.3 维度 ·······························536 17.4 ETL ······································· 542 17.4.1 提供数据························542 17.4.2 强制一致性····················543 17.4.3 通过定义权威数据来 解决冲突 ·······················544 17.4.4 2 个、3 个或 更多个阶段····················544 17.4.5 在 ETL 过程中 处理并发 ·······················545 17.4.6 缓存提取的内容—— 操作数据存储(ODS) ·····546 17.5 使数据可操作: BI 报表技术 ························· 546 17.5.1 目标 ·······························547 17.5.2 关键性能指示器············547 17.5.3 仪表盘 ···························549 17.5.4 记分卡 ···························549 17.5.5 明细表 ···························549 17.5.6 下钻 ·······························550 17.5.7 钻取 ·······························550 17.5.8 临时报表························551 17.6 本章小结······························· 551 第 18 章 BI存储和报表资源 ················ 553 18.1 T-SQL 中的 BI······················ 554 18.1.1 AdventureWorks 数据仓库 ·······················554 18.1.2 使用 T-SQL 分析函数 ···555 18.1.3 columnstore 索引 ···········563 18.2 SQL Server Analysis Services ································ 564 18.3 建立第一个多维数据集 ······· 565 18.3.1 数据源 ···························571 18.3.2 数据源视图····················572 SQL Server 2012 编程入门经典(第 4 版) XIV 18.3.3 多维数据集 ··················· 572 18.3.4 度量值组······················· 573 18.3.5 维度······························· 574 18.3.6 多维数据集的 其他内容······················· 576 18.4 自助式 BI:用户工具 ·········· 576 18.4.1 报表构建器 ··················· 577 18.4.2 PowerPivot:带有 SharePoint 和 Excel 的 轻型 BI·························· 577 18.4.3 Power View···················· 579 18.5 小结······································· 579 第 19 章 Reporting Services ··············· 581 19.1 Reporting Services 101·········· 581 19.2 理解 SSRS 报表的 生命周期······························ 582 19.2.1 在 SSRS 中开发报表 ···· 582 19.2.2 管理报表······················· 583 19.2.3 把报表发布给用户········ 583 19.3 理解 Reporting Services 体系结构······························ 583 19.3.1 SSDT:报表设计器······ 584 19.3.2 SSRS Windows 服务 ····· 584 19.3.3 报表管理器 ··················· 584 19.3.4 报表构建器 ··················· 584 19.3.5 报表查看器 ··················· 584 19.4 构建简单的报表模型 ··········· 585 19.4.1 创建项目······················· 585 19.4.2 给数据模型 添加内容······················· 593 19.4.3 构建和部署 数据模型······················· 598 19.4.4 创建报表······················· 599 19.5 报表服务器项目 ··················· 607 19.5.1 建立报表服务器项目···· 607 19.5.2 部署报表······················· 612 19.6 本章小结······························· 613 第 20 章 使用 Integration Services 进行集成 ································ 615 20.1 理解问题······························· 615 20.2 使用导入/导出向导生成 基本的程序包 ······················ 616 20.2.1 启动向导························616 20.2.2 选择数据源····················617 20.2.3 完成向导························620 20.3 程序包基础知识··················· 622 20.3.1 “连接管理器”面板 ····623 20.3.2 程序包编辑器面板 ········625 20.4 执行程序包··························· 632 20.4.1 在 Development Studio 中 执行程序包····················633 20.4.2 在 SSMS 中执行············633 20.4.3 使用执行包实用工具 ····635 20.4.4 使用 SQL Server Agent 执行程序包····················638 20.4.5 从程序中执行程序包 ····638 20.5 关于程序包的补充说明 ······· 638 20.6 本章小结······························· 638 第 21 章 扮演管理员的角色················· 641 21.1 调度作业······························· 642 21.1.1 创建操作员····················643 21.1.2 创建作业和步骤············644 21.2 登录和用户··························· 651 21.2.1 安全模式························652 21.2.2 创建登录和用户············652 21.2.3 许可和拥有权················654 21.3 备份和恢复··························· 654 21.3.1 创建备份························655 21.3.2 恢复模式························658 21.3.3 还原 ·······························658 21.4 索引维护······························· 660 目 录 XV 21.5 数据归档······························· 662 21.6 基于策略的管理 ··················· 663 21.7 用 POWERSHELL 自动完成 管理任务······························ 663 21.7.1 PowerShell····················· 663 21.7.2 安装和启用 PowerShell ···················· 664 21.7.3 cmdlet 和模块················664 21.7.4 Sqlps ······························664 21.7.5 部署和执行····················666 21.8 本章小结······························· 667 附录 A 习题答案····································· 669 RDBMS 基础: SQL Server 数据库的构成 本章内容简介: ● 理解组成 SQL Server 数据库的对象 ● 学习 SQL Server 2012 中可以使用的数据类型 ● 了解如何命名对象 数据库包括哪些内容?当然包括数据(不存储任何数据的数据库是没有什么作用的)。但实 际上,关系数据库管理系统(Relational Database Management System,RDBMS)不仅仅是数据。 目前高级的 RDBMS 不仅可以存储数据,而且可以管理数据,如限定输入到系统内的数据类型, 还能方便地从系统中读取数据。如果仅仅是想将数据安全保存,则可使用任意数据存储系统。 RDBMS 不仅可以存储数据本身,还可以定义数据类型和数据业务规则。 不要将“数据业务规则”与更通用的业务规则两者相混淆,后者可以应用于整个系统(例如, 用户在登录之前不能看到任何数据,或在每月的第一天自动调整财务系统中的本期)。实际上, 系统的任意层都可以实施这类规则(目前,这在 n 层系统的中间层或客户层已经很常见)。但这 里所涉及的业务规则特指与数据本身有关的规则。例如,不存在金额为负值的销售订单。使用 RDBMS,可将这些业务规则与数据库本身的完整性进行集成。 本书主要讲述保存数据的数据库的概念以及向数据库输入和从中提取数据的最佳方法,这 些是本书全部内容的基础。本章概述本书其余的内容,所介绍的内容将涵盖本书后面的章节, 为您学习后面的内容提供一个指导。本章内容包括: ● 数据库对象 ● 数据类型 ● 确保数据完整性的其他数据库概念 第 章 1 SQL Server 2012 编程入门经典(第 4 版) 2 1.1 数据库对象概述 RDBMS(如 SQL Server)包含许多对象。对于 Microsoft 所认定的可称为对象(或不能称为对 象)的事物是否满足对象的标准定义,这里不做深究,但是对于 SQL Server 来说,它常包括以 下重要的数据库对象: ● 数据库本身 ● 事务日志 ● 表 ● 索引 ● 文件组 ● 数据库关系图 ● 视图 ● 存储过程 ● 用户自定义函数 ● 序列 ● 用户 ● 角色 ● 程序集 ● 报表 ● 全文目录 ● 用户自定义数据类型 1.1.1 数据库对象 在给定的 SQL Server 中,数据库实际上是最高层对象(从技术角度来说,服务器本身可以 看作一个对象,但从实际“编程”的角度看,不能称其为对象,因此本书不作深究)。在 SQL Server 中,大部分(但不是所有)其他对象都是数据库对象的子对象。 数据库通常至少包括一组表对象,一般也包括其他一些对象,如存储过程和视图。存储过 程和视图是对存储在数据库表中的数据的特定分组。 在一个数据库中可以存储什么类型的表,而在不同的数据库中又可以存储什么类型的表 注意:如果熟悉 SQL Server,您现在就可能要问:“登录名或 SQL Agent 任务发生 了什么变化?”SQL Server 有其他一些对象(如前面所列的对象)用于支持数据库。 除了链接服务器和 Integration Services 程序包之外,这些对象主要由数据库管理员 管理,因此在设计和编程时通常不需要多作考虑。通过 SQL 管理对象(SQL Management Object,SMO)可以对它们进行编程,不过这一内容超出了本书的讨论 范围。尽管该规则也有例外之处,但作者通常将它们视为高级内容,将留到高级 版中作介绍。 第 1 章 RDBMS 基础:SQL Server 数据库的构成 3 注意:有人会问:“能否在同一台服务器上安装不同版本的 SQL Server,如 SQL Server 2008 和 SQL Server 2012?”答案是肯定的。可以将 SQL Server 2008 和 SQL Server 2012 混合安装在一台服务器上。本书不推荐采用这种配置,但是如果有需 要的话,可以这样安装。 呢?这个问题将在本书的后面章节详细讨论,现在可以简单地认为:通常被认为属于同一系统 的或明显相关的数据要存储在同一数据库中。RDBMS(如 SQL Server)在一台服务器上可能有多 个用户数据库,当然也可能只有一个用户数据库。单个 SQL Server 上可容纳多少数据库的决定 因素有容量(CPU 能力、磁盘 I/O 限制、内存等)、自主权(您希望某个人具有系统所在的服务器 的管理权,而其他人也具有另一台不同服务器的管理权)或者仅仅是您公司或客户具有多少数据 库。有些服务器仅有一个产品数据库,而其他服务器则有多个数据库。另外记住:目前仍在生 产的任何版本的 SQL Server (SQL Server 2000 被取代之前已使用了 5 年之久,因此假设大部分 软件店有该版本或更高版本的 SQL Server 销售)都可以在同一台物理服务器上有 SQL Server 的 多个实例,并有单独的登录和管理权限。 安装好的 SQL Server 第一次启动时至少包括 4 个系统数据库: ● master ● model ● msdb ● tempdb 安装了以上所有数据库的服务器才能正常运行(事实上,如果缺少其中任意一个数据库,服 务器都无法运行)。根据安装选项的不同,服务器也会不同,可能包括以下样本数据库: ● ReportServer:为 Reporting Server 配置和模型存储需求提供服务的数据库 ● ReportServerTempDB:用于 Reporting Server 的工作数据库 ● AdventureWorks:样本数据库 ● AdventureWorksDW:用于 Analysis Services 的样本 除系统安装的样本数据库外,还可参考一些老版本的样本数据库(通过网络搜索或使用其他 教程): ● pubs ● Northwind 因为在本书的前一版中已经不再使用这些样本数据库,所以此处不再进一步处理它们,但 仍然会提及这些数据库,这主要是因为它们给我们留下了许多美好的回忆,同时也因为您可能 在其他地方发现它们。 注意:这一版中的样本要么是原创的,要么是来自于较新的 AdventureWorks 样本 数据库。新的 AdventureWorks 样本数据库当然更加健壮,并提供了可在 SQL Server 2012 中轻松利用的任何样本,但问题在于其复杂性。AdventureWorks 数据库对于 数据库培训来说是极其复杂的。其功能可能只在个案中使用,并且是用作主要功 能。因此,我们认为 AdventureWorks 并不一定可用作其他类似应用程序中的模板。 SQL Server 2012 编程入门经典(第 4 版) 4 1. master 数据库 任意 SQL Server 都有 master 数据库,而不管其是哪一版本或是自定义修改的。master 数据 库保存一组特殊的表(系统表)用于系统的总体控制。例如在服务器上新建一个数据库时,将会 在 master 数据库的 sysdatabases 表中放入对应的条目。所有扩展的存储过程和系统存储过程都 存储在 master 数据库中,而不论该存储过程是使用于哪一个数据库的。既然几乎所有描述服务 器的信息都存储于 master 数据库中,那么显然该数据库对于系统至关重要,不能删除它。 过去,系统表(包括 master 数据库中的系统表)总在必要时提供系统配置信息,如某些对象 在对其执行操作之前是否存在。Microsoft 多年来一直告诫开发人员不要直接使用系统表,但由 于几乎没有其他选择,大部分开发人员忽视了这一劝告。但所幸的是,Microsoft 通过系统和信 息模式视图的形式提供了其他选择,现在可在需要时直接利用这些视图获取系统元数据。例如, 如果试图在特定数据库中创建已经存在的对象,系统会提示出错。如果想要解决该问题,则可 以测试 sys.objects 表,确认是否存在对应该数据库的表对象的记录项。如果有,则在重新创 建之前删除它。 2. model 数据库 顾名思义,model 数据库是指可以基于该模型得到一个副本。model 数据库构成任何新建 数据库的模板。也就是说,如果想要改变新建标准数据库的样式,则可以根据需要更改 model 数据库。例如,可以向构建的每个数据库中加入一组审计表。也可以将一些用户组复制到在系 统上新建的每一个数据库中。注意,由于 model 数据库作为其他任意数据库的模板,因此系统 中必须保留该数据库,禁止删除它。 在更改 model 数据库时要注意如下几点: ● 任意新建的数据库至少要比 model 数据库大。也就是说,如果将 model 数据库大小更 改为 100MB,就不能新建比 100MB 小的数据库。 ● 添加对象或更改设置时也会引发类似的问题,从而可能导致预料之外的结果。同样, 对于绝大部分 SQL Server 系统的安装,强烈建议不要修改这一项。 3. msdb 数据库 msdb 数据库是 SQL Agent 进程存储任意系统任务的地方。如果计划对一个数据库每夜进行 备份,则在 msdb 数据库中有一个记录项。如果每次执行任务时调度存储过程,则在 msdb 数据 库中会有一个记录项。SQL Server 中的其他大部分子系统以类似的方式使用 msdb。SSIS 程序 包和基于策略的管理定义就是使用 msdb 的其他进程的示例。 警告:如果您很豪爽,就可能会对自己说:“我迫不及待地想要弄乱系统表!”不 要尝试这样做。实际上以任何形式使用系统表都非常危险。Microsoft 不能绝对保 证不同版本的 master 数据库的兼容性。而事实上,master 数据库几乎都会被更改。 不过有几个对象(如系统函数、系统存储过程和 information_schema 视图)可用于检 索存储在系统表中的大部分元数据。 虽说如此,有时还是必须使用系统表,但通常最好还是敬而远之,避免接触它。 第 1 章 RDBMS 基础:SQL Server 数据库的构成 5 4. tempdb 数据库 tempdb 数据库是服务器的主要工作区域之一。在执行一个复杂或者大型的查询操作时, SQL Server 可能需要创建一些中间表来完成该操作,这项工作就是在 tempdb 数据库中进行的。 只要创建自己的临时表,这些表就会创建在 tempdb 数据库中,即使您是在当前数据库中创建 这些表的(别名是在本地数据库中自动创建以供引用的,而物理表在 tempdb 中创建)。只要需要 临时存储数据,就很可能是将数据存储在 tempdb 数据库中。 tempdb 数据库与其他任意数据库不同。不仅数据库中的对象是临时的,连数据库本身也是 临时的。每次启动 SQL Server,tempdb 数据库是系统中唯一完全重建的数据库。 5. ReportServer 数据库 这个数据库只有在安装了ReportServer的情况下才存在(它不必与数据库引擎具有相同的服 务器,但请注意,如果是不同的服务器,那么它要求单独的许可)。ReportServer 数据库存储 Reporting Server 实例的任何持久化元数据。注意,它是只可用于给定 Reporting Server 实例的 操作性数据库,只能通过 Reporting Server 修改或访问。 6. ReportServerTempDB 数据库 除了存储非持久化数据(如有关正在运行的报表的工作数据)外,该数据库的基本作用与 ReportServer 数据库相同。同样,它也是纯粹操作性的数据库,只能通过 Reporting Server 访问 或修改。 7. AdventureWorks 数据库 在有 AdventureWorks 数据库之前,SQL Server 就包括了其他样本数据库。老样本数据库有 不足之处,如它们包含了一些较差的设计实践。此外,老的样本过于简单,集中阐明某些数据 库概念,而不是将 SQL Server 作为一个产品或将数据库视为一个整体。这里不再探讨 AdventureWorks 数据库是否存在相同问题。AdventureWorks 数据库确实试图解决这些问题。 在 SQL Server 2005 开发之初,Microsoft 就想要实现一个更加健壮的样本数据库,使其尽 可能作为其产品的一个样本。AdventureWorks 数据库就是这一想法的结果。尽管该数据库对于 初学者过于复杂,但是其作为样本数据库确实是一个杰作。虽然该数据库不包括所有的内容, 但还是一个相当完整的样本,具有更接近实际的数据量、复杂的结构和用于展示产品大部分特 征的样本的部分。从这一方面看,该数据库是非常不错的。 AdventureWorks 是中心数据库,本书中的示例将广泛使用该数据库。 注意:从技术上来说,可以在 tempdb 数据库中实际创建自己的对象,但是我们强 烈反对进行该操作。可以在系统中任意有权访问的数据库内创建临时对象,该对 象也会存储在 tempdb 数据库中。直接在 tempdb 数据库中创建对象没有任何意义, 只会增加在数据库之间引用对象时产生的混乱。这也是不要尝试做的一件事情。 SQL Server 2012 编程入门经典(第 4 版) 6 8. AdventureWorksDW 数据库 AdventureWorksDW 数据库是 Analysis Services(分析服务)样本数据库。DW 表示数据仓 库(Data Warehouse),大部分 Analysis Services 项目都建立在数据仓库的基础上。Microsoft 预 见性地将分析样本数据库与事务样本数据库联系在一起,以提供展示两者协同运行的一组完 整样本。 由于决策支持数据库将在本书的第 17 章和第 18 章中详细介绍,而且您将使用决策支持数 据库,因此在启动并操作 Analysis Services 时需要注意该数据库。比较一下这两个数据库之间 的区别。虽然它们服务于同一个虚构的公司,但是具有不同的目的。 9. pubs 数据库 pubs 数据库是我们所熟悉的。它是原来的样本数据库之一,在 SQL Server 2005 之前作为 安装数据库的一部分随 SQL Server 一起提供。pubs 数据库是目前唯一能从 Microsoft 网站上单 独下载安装的样本数据库。当前的许多培训资料和书籍仍涉及该数据库,但 Microsoft 并未对 其还可用多长时间作出承诺。pubs 数据库与 SQL Server 操作绝对无关,它仅仅是为培训或实 验提供一个一致的环境。虽然读者并不需要用 pubs 数据库来操作本书中的示例,但可能希望 下载并安装它来练习网络上的其他一些示例和教程。 10. Northwind 数据库 如果您有 Access 或者 Visual Basic 方面的编程经验,就很可能已经对 Northwind 数据库不陌 生了。Northwind 数据库是从 SQL Server 7.0 版本开始添加进来的,但是在 SQL Server 2005 的基 本安装中被删除了。与 pubs 数据库类似,Northwind 数据库的下载可与 SQL Server 的基本安装分 开进行(其下载和安装与 pubs 样本数据库的下载和安装一并进行)。与 pubs 数据库一样,Northwind 数据库也不用于练习本书中的示例,但可很方便地用于练习网络上的其他一些示例和教程。 在 SQL Server 2012 中使用 pubs 和 Northwind 数据库 这两个数据库已经存在了很长时间,对它们的支持正在逐渐变少。您仍然可以使用这两个 数据库,但是需要执行转换,本书中不会详细介绍该转换。如果您确实想要定位并使用这些样 本数据库,那么确实可以这么做,但是需要进行一些额外的工作。 1.1.2 事务日志 不管您相信与否,数据库文件本身并不是“事情发生最多的场所”。数据当然要从数据库 文件读取,但实际上,任意数据库的更改起初不进入数据库本身,而是不断地被写入到事务日 志。在后面的某个时间点上,数据库发出检查点,就是在该时间点,日志中所有的更改才被传 播到实际的数据库文件中。 数据库采用随机访问配置,但日志是自然连续的。数据库文件的随机访问特点允许快速访 问,而日志的连续特征又使得发生的事件可按照适当的顺序来跟踪。日志不断积累已经提交的 更改,并且一次性地将多次更改写入到物理数据库文件。 第 14 章将深入讨论更改如何写入到日志,现在要记住的是:日志是数据进入磁盘上的最 第 1 章 RDBMS 基础:SQL Server 数据库的构成 7 先位置,且稍后会被传播到实际的数据库。需要数据库文件和事务日志一起来完成数据库功能。 1.1.3 最基本的数据库对象:表 数据库包括许多对象,但最重要的对象非表莫属。表类似会计账簿或 Excel 电子表格。表 由称为域的数据(列)和实体数据(行)构成。数据库中实际的数据都存储在表中。 表的定义也包含了描述表中包含数据的特性的元数据(数据描述信息)。每一列都具有该列 可存储数据类型的一组规则。表中任一列内的数据违反了规则时,系统会拒绝插入一行,或拒 绝对已有行进行更新,又或者禁止删除行。 以 AdventrueWorks 数据库中的 Production.Location 表为例(如图 1-1 所示的视图来自 SQL Server Management Studio。SQL Server Management Studio 是 SQL Server 的基本工具,第 2 章将 介绍该工具的使用)。 图 1-1 如图 1-1 所示的表由 5 列数据构成。与表中的数据的多少(即使是 0)无关,列的数目是不变 的。图 1-1 所示的表有 14 条记录。在增删数据时,表中记录的数目会随之增减,但是每一条记 录(或行)的数据类型由列的数据类型定义和限制。 1. 索引 索引是仅在特定表或视图框架内存在的对象。索引的功能非常类似百科全书中的目录。索引 中有以某一特定方式排序的查找(或键)值,使用索引是快速查找数据库中实际信息的一种方法。 利用索引可以加速信息的查找。索引分为如下两类: ● 群集索引——每个表只能有一个群集索引。如果是群集索引,则该索引的含义为:群集 索引对应的表按照其索引进行物理排序。如果为百科全书做索引,则群集索引是书的页 码(按页码顺序存储百科全书中的信息)。 SQL Server 2012 编程入门经典(第 4 版) 8 ● 非群集索引——每个表可以有多个非群集索引。非群集索引的含义与您在平时听到的 “索引”的含义更接近。非群集索引是指用于找到数据的其他一些值。对于百科全书, 非群集索引指的是百科全书后面的关键字索引。 注意,具有索引的视图(索引视图)在有非群集索引之前至少必须有一个群集索引。 2. 触发器 触发器是存在于表框架内的对象。触发器是在操作表时(如进行插入、更新或删除等)自动 执行的一段逻辑代码。 触发器有多种用途,但主要用于在输入时复制数据或在更新时检查数据,确保数据满足相 应条件。 3. 约束 约束是仅限于表中存在的另一种对象。顾名思义,约束就是限制表中的数据满足某种条件。 约束在某种方式上类似触发器,它尽可能解决数据完整性问题。但它们也有所不同,各自具有 不同的优点。 1.1.4 文件组 默认情况下,数据库中所有的表及其他对象(日志除外)要存储在一个文件中。默认情况下, 该文件是一些所谓的主要文件组的成员。不过,并不一定要保持这样的配置。 SQL Server 允许定义约 32 000 个辅助文件(如果需要更多的辅助文件,则可能不是 SQL Server 本身的问题)。这些辅助文件可加入到主要文件组中,或者作为一个或多个用户自定义文件组的 一部分而创建。仅可以有一个主要文件组,但可以有多达 255 个用户自定义文件组。通过 CRE- ATE DATABASE 或 ALTER DATABASE 命令的选项可创建用户自定义文件组。 1.1.5 数据库关系图 在讲述规范化和数据库设计时会详细讲述数据库关系图,现在只需要清楚数据库关系图是 数据库设计的可视表示,它包括了各种表、每一张表的列名以及表之间的关系。开发人员应该 听过实体-关系图(entity-relationship diagram,ERD)。在 ERD 中,数据库分成两部分:实体(如 “供应商”和“产品”)和关系(如“供应”和“购买”)。 如图 1-2 所示的数据库关系图表示 AdventureWorks 数据库中的各表之间的关系。该数据库 关系图也描述了数据库的许多其他特征(如果您是第一次接触它,可能不会注意到这一点)。注 意标识键的小图标和无穷大符号。这些符号描述了两张表之间的本质关系。本书第 6 章和第 8 章会详细讲述关系,而且在本书的后面部分会深入讲述数据库关系图。 注意:遗憾的是,SQL Server 内含的数据库设计工具保留的很少。实际上,该工 具使用的数据库关系图方法并未遵循 ER 数据库关系图中公认的标准。但这些数 据库关系图工具事实上提供了所有的“必需”内容,因此也可从它们入手。 第 1 章 RDBMS 基础:SQL Server 数据库的构成 9 图 1-2 1.1.6 视图 视图是一种虚拟表。除了视图本身不包含任意数据外,视图的使用基本上与表的使用类似。 事实上视图仅仅是表中所存储数据的一个预先计划的映射和表示。视图以查询的形式存储在数 据库中。这种查询需要从一个或多个表中获取一些列(但不是所有列)的数据。为了显示视图中 的数据,读取的数据可能不需要(取决于视图的定义)满足特定条件。 SQL Server 2000 版本面世之后,应用视图的主要目的是控制视图用户查看的数据。这有两 方面的作用:安全和易于使用。使用视图可以控制用户所见的数据,因此,如果某些用户只能 访问表的一部分数据(如工资详细资料),则可以创建允许任何用户访问的只包含这两列数据的 视图。此外,可以剪裁视图,使得用户不必搜索任何不必要的信息。 除了这个最基本的视图用途之外,还能够创建所谓的索引视图(indexed view)。索引视图除了可 以创建索引之外,其他都与视图相同。这一点对使用性能有一定影响(包括正面影响和负面影响): ● 对于引用多个表的视图来说,索引视图可读性更好,因为它预先构造了表之间的连接。 ● 由于预先计算好视图中执行的聚合(aggregation),并将其作为索引的一部分存储;因此 聚合只要执行一次(在插入或更新行时),然后就可从索引信息中直接读取它。 ● 由于视图上的索引必须及时更新,因此插入和删除索引需要更多的系统开销;如果更 新影响到索引的键列或群集键,则更新也需要更多的系统开销。 第 10 章将深入探讨索引视图对系统性能的影响。 1.1.7 存储过程 存储过程(或 sprocs)是 SQL Server 编程功能的基础。存储过程通常是组成一个逻辑单元的 Transact-SQL(用于 Microsoft SQL Server 数据库查询的语言)语句的有序集合。存储过程允许使 用变量和参数,也可使用选择和循环结构。与向服务器发送单条语句相比,使用存储过程有以 下几个优点: SQL Server 2012 编程入门经典(第 4 版) 10 ● 不使用长字符串文本而使用短存储过程名,可减少运行存储过程中的代码所需要的网 络通信流量。 ● 预先优化和预编译,节省每次运行存储过程的时间。 ● 通常考虑安全原因,或仅仅是隐藏数据库的复杂性,将过程封装。 ● 可从其他存储过程调用,使得它们在某些限定意义上重用。 尽管存储过程是 SQL Server 编程功能的核心,但要小心使用它们。通常情况下,它们是一种 解决方案,但通常也不是唯一的解决方案。在选择存储过程作为解决方案前,要确保它们是正确的 选择。 1.1.8 用户自定义函数 用户自定义函数(UDF)与存储过程非常相似,不同之处有以下几点: ● 返回值的数据类型包括大部分 SQL Server 数据类型。不包括的返回值类型有:text、 ntext、image、cursor 和 timestamp。 ● 没有“副作用”。基本上,用户自定义函数不能超出函数的作用范围,如更改表、发 送电子邮件或者更改系统或数据库参数。 UDF 类似于标准编程语言(如 VB.NET 或 C++)中使用的函数。函数可以有多个输入变量, 并且有一个输出值。但 SQL Server 的 UDF 与许多过程语言中的函数也有所不同,传送到函数 中的所有变量都是按值传递的(除了用作参数的表变量)。UDF 输入值的传递与变量按引用传递 或是指针的传递不同。但是 UDF 可以返回一种特殊的数据类型——表。第 13 章将深入讲述该 部分内容。 1.1.9 序列 序列是 SQL Server 2012 中新增加的对象类型。序列的作用是提供任意数量的进程都可以 访问的序号源,同时保证任意两个进程不会同时获取相同的下一个值。因为序列是单独存在的 对象(没有绑定到任何表),所以它可以有多种用途,第 7 章中将详细介绍这些用途。 1.1.10 用户和角色 用户和角色关系密切。用户(user)等价于登录名。简言之,该对象表示登录 SQL Server 的 某人的标识符。登录 SQL Server 的任何人都必须映射(直接或间接映射,取决于使用的安全模 型)到一个用户。用户依次属于一个或多个角色(role)。SQL Server 中可以直接赋予用户或角色 执行某种操作的权限,一个或多个用户可属于同一角色。 1.1.11 规则 规则和约束都用于限制插入到表中的数据类型的信息。如果更新或插入记录违反了规则, 则插入或更新操作被拒绝。此外,规则可用于定义用户自定义数据类型上的限制条件。与约束 不同,规则不限于特定表。它们是独立对象,可绑定到多个表,甚至绑定到特定数据类型(从而 间接用于表中)。 目前,Microsoft 对于一些版本已考虑弃用规则。规则应该考虑向后兼容,而在新的开发中 第 1 章 RDBMS 基础:SQL Server 数据库的构成 11 应该避免使用。 1.1.12 默认值 SQL Server 中有两种类型的默认值,包括对象本身的默认值,以及描述表中特定列的元数 据的默认值(非真正对象)。与约束和规则类似,规则是对象,而约束不是对象,是元数据。当 插入一条记录时,如果没有提供该列的值,且该列具有其默认值,则自动插入默认值。第 6 章 将深入研究两种类型的默认值。 1.1.13 用户自定义数据类型 用户自定义数据类型是系统定义数据类型或.NET 程序集中方法定义的复杂数据类型的扩 展。用户自定义数据类型几乎可定义任意数据。虽然 SQL Server 2000 及其更早版本也有用户 自定义数据类型的概念,但是它们被限制在现有的数据类型的筛选中。自 SQL Server 2005 发 行后,可以将.NET 程序集绑定到您自己的数据类型上,即能够定义一种数据类型,用于存储(在 合理范围之内).NET 对象中可以存储的任意对象。事实上,SQL Server 2008 中新添加的空间数 据类型(Geographic 和 Geometric)就是使用基于.NET 程序集的用户自定义类型实现的。.NET 程 序集无疑是一个高级主题,它已经超出了本书的讨论范围。 1.1.14 全文目录 全文目录是数据映射,用于加速列中特定文本块的搜索,并且该列需要启用全文搜索功 能。在 SQL Server 2008 之前,全文目录存储在数据库外部(因此导致一些重大的备份和恢复问 题)。从 SQL Server 2008 开始,全文目录已集成到主数据库引擎和存储机制中。由于其复杂的 特性,因此全文索引已经超出了本书的讨论范围。 1.2 SQL Server 数据类型 在熟悉了 SQL Server 数据库中的基本对象之后,下面介绍 SQL Server 中的数据类型。注意: 由于本书面向开发人员,而几乎不存在还不清楚数据类型的开发人员,所以这里假设您已经清 楚数据类型的工作原理,只介绍特定的 SQL Server 数据类型。 SQL Server 2012 所有自带的数据类型如表 1-1 所示。 注意:考虑到 Microsoft 已在 SQL Server 2008 中引入一些新的弃用处理功能,我 们怀疑在一些版本中弃用的功能(如规则)将最终在 SQL Server 的下一版中删除。 因此,需要强调一下,在新开发中不要使用规则。事实上,很早前就开始移除它 们了。 注意:要谨慎使用用户自定义数据类型!您所使用的数据类型对于数据和数据存 储来说非常重要。虽然能够定义任意对象的功能很优秀,但需要大量性能开销。 所以要考虑清楚,确信是需要自定义数据类型,然后再进行多次测试! SQL Server 2012 编程入门经典(第 4 版) 12 表 1-1 数据类型 数据类型名称 类 别 长度(以字 节为单位) 数 据 特 点 Bit 整型 1 该大小容易产生误导。表中的第 1 个 Bit 数据类型 占 1 个字节。其余 7 个位也用作 Bit 数据类型。允 许 null 值以使其占用一个额外字节 Bigint 整型 8 可处理日常用到的越来越大的数,其取值范围为 –263 ~ 263–1 的整数 Int 整型 4 取值范围为–2 147 483 648 ~ 2 147 483 647 的整数 SmallInt 整型 2 取值范围为–32 768 ~ 32 767 的整数 TinyInt 整型 1 取值范围为 0~255 的整数 Decimal/Numeric 十进制/数字型 可变 固定精度,取值范围为–1038–1 ~ 1038–1。两者含义 相同 Money 货币 8 货币单位,取值范围为–263 ~ 263,精确到 4 个小数 位。注意货币单位可以是任意货币,不限于美元 SmallMoney 货币 4 货币单位,取值范围为–214 748.364 8 ~ +214 748.364 7 Float (ANSI 的 Real) 近似小数 可变 接受一个 1~53 的参数(如 Float(20))来决定其大小与 精度。注意参数值表示位数,不是字节数。 取值 范围为–1.79E + 308 ~ 1.79E + 308 DateTime 日期/时间 8 日期与时间数据,取值范围为 1753 年 1 月 1 日~ 9999 年 12 月 31 日,精确到 0.03 秒 DateTime2 日期/时间 可变(6~8) 新扩展的 DateTime 典型数据类型。支持更大的日 期范围和更高的时间部分精度(精确到 100 纳秒)。 与 DataTime 一样,它不包含时区信息,但与.NET DateTime 数据类型相对应 SmallDateTime 日期/时间 4 日期与时间,取值范围为 1900 年 1 月 1 日~2079 年 6 月 6 日,精确到分钟 DateTimeOffset 日期/时间 可变 (8~10) 类似于 DateTime 数据类型,但有一个相对于 U-TC 时间的–14:00~+14:00 的偏移量。时间在内部存储为 UTC 时间,任何比较、排序或索引都将基于该统一 的时区 Date 日期/时间 3 只存储 Gregorian 日历定义的 0001 年 1 月 1 日~9999 年 12 月 31 日的日期数据。采取 ANSI 标准日期格式 (YYYY-MM-DD),但会从其他一些格式隐式转换 Time 日期/时间 可变(3~5) 只存储用户可选精度为 100 纳秒(默认)的时间数据 Cursor 特殊数字 1 指向游标的指针,只占用一个字节。记住组成实际 游标的结果集也占用内存,占用内存的大小取决于 结果集 Timestamp/ rowversion 特殊数字 (二进制) 8 给定数据库中的唯一特殊值。即使 UPDATE 语句 没有引用 timestamp 列(时间标记),其值在插入或更 新记录时也会由数据库自动设置(不允许直接更新 timestamp 字段) 第 1 章 RDBMS 基础:SQL Server 数据库的构成 13 (续表) 数据类型名称 类 别 长度(以字 节为单位) 数 据 特 点 UniqueIdentifier 特殊数字 (二进制) 16 特殊的全局唯一标识符(GUID),必须保证其在内存 空间和时间内的唯一性 Char 字符 可变 定长字符数据。比设定长度短时使用空格填充,为非 Unicode 数据,指定的最大长度为 8000 字符 VarChar 字符 可变 长度可变的字符数据。比设定长度短时不使用空格填 充,为非 Unicode 数据。允许最大长度为 8000 字符, 但可以使用 max 关键字表示其长度可足够大(数据长 度可达 231 字节) Text 字符 可变 从 SQL Server 2005 开始向后兼容支持该类型。可 使用 varchar(max)代替 NChar Unicode 可变 定长 Unicode 字符数据。比设定长度短时使用空格 填充。指定的最大长度为 4000 字符 NVarChar Unicode 可变 可变长度的 Unicode 字符数据。比设定长度短时不 使用空格填充。允许最大长度为 4000 字符,但可 以使用 max 关键字表示其长度可足够大(数据长度 可达 231 字节) Ntext Unicode 可变 可变长度的 Unicode 字符数据。类似 Text 数据类型, 仅用作向后兼容。可使用 nvarchar(max)代替 Binary 二进制 可变 定长二进制数据,最大长度为 8000 字节 VarBinary 二进制 可变 可变长度二进制数据,最大特定长度为 8 000 字节, 可使用 max 关键字使其作为 BLOB(大对象)字段(数 据长可达 231 字节) Image 二进制 可变 从 SQL Server 2005 开始向后兼容支持该类型。可 使用 varbinary(max)代替 Table 其他 特殊 主要用于操作结果集,通常作为用户自定义函数 (UDF)的结果输出或作为存储过程的参数。在表的定 义中不作为可用的数据类型(不可以嵌套表) HierarchyID 其他 特殊 维护层次结构定位信息的特殊数据类型。提供特定 于层次结构需要的特殊功能。允许进行深度、父/ 子关系和索引比较。实际大小随层次结构中的节点 数和平均深度而变 Sql_variant 其他 特殊 与 VB 和 C++中的变量基本无关。其实质是用于保 存大多数其他 SQL Server 数据类型的容器。当列或 函数需要处理多种数据类型时可使用这种数据类 型。与 VB 不同的是,使用这种数据类型要将其显 式转换为更具体的数据类型 XML 字符 可变 定义一个字符字段用作 XML 数据。用于针对 XML 模式的数据验证和使用特殊的面向 XML 的函数 CLR 其他 可变 随 CLR 对象的特性而变,CLR 对象支持基于自定义数 据类型的 CLR。随 SQL Server 2012 附带的空间数据类 型 GEOMETRY 和 GEOGRAPHY 实现为 CLR 类型 SQL Server 2012 编程入门经典(第 4 版) 14 表 1-1 中的大多数数据类型与其他编程语言中的数据类型相同。例如,SQL Server 中的整 型(int)等价于 Visual Basic 中的长整型(long),对于 C++中的大部分系统和编译器组合来说,它 等价于有符号整型。 通常,SQL Server 数据类型与绝大多数其他现代编程语言中对应数据类型的使用类似。数 的相加为和,而字符串的相加为字符串的连接。如果混合使用或混合赋值不同数据类型的变量 或字段,那么许多数据类型就会隐式(或自动)转换。大部分其他数据类型可以显式转换(只要 指定想要转换成何种数据类型)。有些数据类型之间不能相互转换。图 1-3 中的图表包含了所 有可能的不同转换。 图 1-3 为什么要进行数据类型转换呢?下面举一个简单的示例。如果要输出“Today's date is ##/##/####”的短语,其中##/##/####表示当前日期,则可能进行如下编程: 注意:SQL Server 中没有无符号数值数据类型的概念。 第 1 章 RDBMS 基础:SQL Server 数据库的构成 15 SELECT 'Today' 's date is ' + GETDATE() 代码段 Chap01.sql 问题是该语句生成如下结果: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. 这并不是真正想要的结果。下面使用 CONVERT()函数: SELECT 'Today' 's date is ' + CONVERT(varchar(12), GETDATE(),101) 该语句输出的结果为: --------------------------------- Today's date is 01/01/2012 (1 row(s) affected) 像 GETDATE()函数输出这样的日期与时间(date/time)数据类型不能自动转换成字符串数据 类型,如“Today's date is”,而您会经常遇到这种转换。CAST 和 CONVERT()函数可将 SQL Server 的许多数据类型相互进行转换。本书后面的章节会讲述 CAST 和 CONVERT()函数。 简而言之,SQL Server 中的数据类型与其他编程环境中的数据类型的功能大多相同。数据 类型可以帮助避免编程的错误,确保提供的数据和定义的数据类型一致(记住日期 1/1/1980 与数 字 1/1/1980 不同),而且确保可执行期望的操作。 NULL 数据 如果表中有一行的某一列没有任何数据,您是否知道该列的值?例如,让一条记录存储某 一年的公司业绩信息,假设字段之一为公司业绩比前一年的增长百分比,但是在数据库中没有 上一年的业绩信息记录,则可能在 PercentGrowth 列中输入 0。这样做是否会提供正确的信息 呢?不知道的人可能认为 0 表示增长 0%。而实际上是不知道上一年的业绩信息。 不确定的值可设置为 NULL。几乎在我每一次上编程课时,都至少有一个学生问我怎么定 义 NULL 值。这是一个很好的问题,因为根据定义,NULL 值也就是不确定的值。它可能是 1, 也可能是 347,还可能是–294 等。简而言之,NULL 的含义是未定义的或者是不能应用的值。 1.3 SQL Server 对象标识符 到这里为止,本书介绍了与 SQL Server 中对象相关的内容。下面介绍 SQL Server 中对象的命名。 注意:本书后面还将更加详细地讲解 Transact-SQL 语句,但是上述示例的输出结 果显而易见。 SQL Server 2012 编程入门经典(第 4 版) 16 1.3.1 需要命名的对象 基本上,SQL Server 中的所有对象都需要命名,下面是部分命名对象的列表。 ● Stored procedures(存储过程) ● Tables(表) ● Columns(列) ● Views(视图) ● Rules(规则) ● Constraints(约束) ● Defaults(默认值) ● Indexes(索引) ● Filegroups(文件组) ● Triggers(触发器) ● Databases(数据库) ● Servers(服务器) ● User Defined Functions(用户自定义函数) ● Sequences(序列) ● Logins(登录名) ● Roles(角色) ● Full-text catalogs(全文目录) ● Files(文件) ● User Defined Types(用户自定义类型) 这个列表还可不断扩展。除了行(行不是真正的对象)之外的所有对象都具有名称。命名的 技巧是使名称有用且实际。 1.3.2 命名规则 如本章前面所述,SQL Server 中的命名规则非常随意,命名允许名称中内嵌空格,甚至是 关键字。但是,命名过于自由也容易带来麻烦。 主要的命名规则如下所示: ● 对象的名称必须以 Unicode 3.2 规范定义的任意字母开头。包括大部分西文字母 A~Z 和 a~z。“A”与“a”是否相同取决于服务器配置的方式,但不管 A 或 a 作为对象名的开 头都符合规则。名称中除了开头字母之外的其他部分几乎可以是任意字符。 ● 正常对象的名称可多达 128 个字符,而临时对象的名称可多达 116 个字符。 ● 与 SQL Server 关键字相同或包含内嵌空格的名称必须使用双引号("")或方括号([])。哪 一个单词被视为关键字取决于为数据库设置的兼容级别。 注意:只有设置了 SET QUOTED_IDENTIFIER ON,双引号才可以作为列名中的 分隔符。使用方括号([])避免了用户进行其他错误设置。 第 1 章 RDBMS 基础:SQL Server 数据库的构成 17 这些规则通常称为标识符以及 SQL Server 中的任意对象的命名规则,但如果是 SQL Server 的本地化版本(适用于特定语言、方言或地区),则会稍有不同。对于特定对象类型存在其他规则。 1.4 本章小结 像生活中的许多事物一样,许多小的数据库对象造就了 RDBMS 的强大功能。几乎所有的 人(只要看到本书)都能想到使用列和行的方式来存储数据,即使他们还不清楚这些列和行的组 合应该称之为表。但是少量表却不能构成真正的数据库。目前的 RDBMS 之所以如此强大,是 因为数据库对象使您能将与数据相关的功能和业务规则放到带有数据的数据库中。 与其他大部分编程环境一样,数据库数据也具有类型。在 SQL Server 的编程中要对数据类 型作必要的考虑。复习一下可用的数据库数据类型,思考该数据类型如何映射到您熟悉的任何 编程环境中相对应的数据类型。 练习题 1. master 数据库的主要作用是什么? 2. datetime 和 datetime2 数据类型之间的两个主要区别是什么? X 本章内容总结 主 题 概 念 对象类型 新安装的 SQL Server 会创建系统数据库并允许在数据库内部和外部创建许多类型的对象 数据类型 SQL Server 提供许多不同的数据类型,这些数据类型可用于高效地和正确地存储信息 标识符 您可以为对象提供最多 128 个字符的名称(对于临时对象则是 116 个字符),并且名称以字 母开头 注意:这里值得注意的是对象的命名。SQL Server 可以在命名中嵌入空格,甚至 有时可以使用关键字来命名。注意,要避免这种命名方式!在使用 SELECT 语句 时,嵌入空格的列名可能使得标题更好看,但还有其他方法同样能达到这种效果。 在列名中使用内嵌空格或关键字容易引发 bug 和混淆,甚至产生其他严重后果。 本书后面会解释为什么 Microsoft 允许这种命名,这里只需要记住在命名中使用 内嵌空格或关键字等于自找麻烦(本书会多次提到这一点)。 第 章 T-SQL 基本语句 本章内容简介: ● 如何从数据库检索数据 ● 如何向表中插入数据 ● 如何适当地更新数据 到目前为止,我们已经介绍了 SQL Server 中最繁琐的内容。相比于基本对象和工具,T-SQL 语言的学习要简单得多。但在建房子之前必须打好地基,而前两章内容的学习就是在打“地基”。 在使用数据库前,必须清楚如何访问数据库的数据,然后才能学习如何以最佳方式来存储数据。 本章介绍最基本的 Transact-SQL(T-SQL)语句。T-SQL 是 SQL Server 自己的结构化查询语 言(Structured Query Language,SQL)的“方言”。本章要介绍的 T-SQL 语句如下所示: ● SELECT ● INSERT ● UPDATE ● DELETE 以上 4 条语句是 T-SQL 的基础内容。后面几章还会继续介绍许多其他的 T-SQL 语句,但 这 4 条语句构成了 T-SQL 最基本的数据操作语言(Data Manipulation Language,DML),因为在 进行数据操作时,通常这 4 条语句(如读取和修改)比其他命令(如给用户授权或创建表)的使用 频率要高得多。 此外,SQL 提供了许多运算符和关键字,用于优化数据查询。本章将介绍一些最基本的运 算符和关键字。 SQL Server 2012 编程入门经典(第 4 版) 注意:尽管 T-SQL 语言是 SQL Server 独有的,但通常使用的语句并不是 T-SQL 语 言。T-SQL 语言在很大程度上还是遵循 ANSI/ISO 标准的(该标准最初由 ANSI 制定, 之后被 ISO 接管。由于长时间都是由 ANSI 领导,因此人们仍称其为 ANSI 标准), 即 T-SQL 语言遵循广泛采用的开放式业界标准。这意味着对于开发人员来说,本书 要介绍的大部分 SQL 语言可直接转换至其他基于 SQL 的数据库服务器,如 Sybase(很早就与 SQL Server 共享相同的基本代码)、Oracle、DB2 和 MySQL。但要 知道,每个 RDBMS 会将标准范围扩大并改进,因而使用的标准往往超过 ANSI/ISO 标准。我们也将注意区分 ANSI 标准与非 ANSI 标准的适用范围。有时要在性能与 其他 RDBMS 系统的可移植性之间作出权衡。但在大部分情况下,使用 ANSI 标准 的方法与其他方法同样快速。因此,通常应选择遵循 ANSI 标准的 SQL 语言。 3.1 基本 SELECT 语句 如果您在此之前未使用过 SQL 语言,或者还未真正理解它,那么要注意学习本章的内容! SELECT 语句及其语法结构是 SQL Server 执行全部命令的基础。SELECT 语句的基本语法规则 如下: SELECT [ALL|DISTINCT] [TOP () [PERCENT] [WITH TIES]] [FROM ] [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] [[FOR XML {RAW|AUTO|EXPLICIT|PATH [()]}[, XMLDATA] [, ELEMENTS][, BINARY base 64]] [OPTION (, [, ...n])] 上述语法结构很复杂,以下将依次介绍。 注意:从技术上讲,TOP 表达式周围的小括号是可选的。而 Microsoft 要求它们是 “强制的”,并指出省略小括号实际也是支持的,但只是为了向后兼容。这意味着, Microsoft 在后面的版本中仍会有这一要求,因此如果不需要支持 SQL Server 以前 的版本,强烈建议使用小括号作为查询中的 TOP 表达式的定界符。 3.1.1 SELECT 语句与 FROM 子句 动词 SELECT 是整条语句的谓语部分,即告诉 SQL Server 执行什么操作。SELECT 表示仅 仅读取数据信息,而不修改数据信息。所选择的内容由紧跟在 SELECT 后面的表达式或列列表 确定。稍后将介绍相关示例。 接着,要加入更具体的限制,如 SQL Server 从哪里读取数据。FROM 语句指定要读取的数 据所在的一个表或几个表的名称。了解了这些概念,就可以建立一条基本的 SELECT 语句。启 42 第 3 章 T-SQL基本语句 动 SQL Server Management Studio,看一下下面这条简单的 SELECT 语句。 SELECT * FROM INFORMATION_SCHEMA.TABLES; 代码段 Chap03.sql 让我们看一下您在此处请求的内容。您已经请求 SELECT 信息;当工作在 SQL Server Management Studio 中时,您也可以将该语句认为是显示所选择的信息。其中符号“*”看上去 很奇怪,它是一个通配符,实际功能与其他地方使用的含义相同。使用“SELECT *”可以告 诉 T-SQL 您想要选择表中所有的列。然后,FROM 子句表示要输出信息的来源,这里是 “INFORMATION_SCHEMA.TABLES”。 注意:INFORMATION_SCHEMA 是特定的访问路径,用于显示系统数据库及其内 容的相关元数据。INFORMATION_SCHEMA 有几个可在句点后指定的部分,如 INFORMATION_ SCHEMA.SCHEMATA 或 INFORMATION_SCHEMA.VIEWS。由于 系统的元数据的特定访问路径都保存于此,因此不必使用系统表。 试一试 使用 SELECT 语句 让我们更多地使用 SELECT 语句。将 AdventureWorks 数据库设置为当前数据库。回顾第 2 章所学的内容,只需要在 Management Studio 的“查询”窗口顶部工具栏的下拉列表框中选择 AdventureWorks 条目,如图 3-1 所示。 图 3-1 注意:如果找不到列出各种数据库的下拉列表框,则单击一次“查询”窗口。SQL Server Management Studio 工具栏是区分上下文的,即工具栏根据“查询”窗口当 前的操作而改变。如果“查询”窗口当前不是活动窗口,则可能有另一组工具栏 显示(这组工具更适合于其他任务)。只要“查询”窗口为活动窗口,就会切换到适 合查询需求的一组工具栏。 43 SQL Server 2012 编程入门经典(第 4 版) 选择 AdventureWorks 数据库之后,先查看该数据库的一些实际数据。使用以下语句查询: SELECT * FROM Sales.Customer; 代码段 Chap03.sql 在“查询”窗口中输入以上语句后,单击工具栏上的“执行”按钮(F5 键是“执行”命令 的快捷键),查看 SQL Server 返回的结果。该查询列出当前数据库(AdventureWorks 数据库)中 Sales.Customer 表内所有列的每一行数据。如果在运行该查询语句之前没有更改系统设置或 AdventureWorks 数据库中的数据,则在单击“消息”选项卡后,将显示如下的消息: (19820 row(s)affected) 对于 SELECT 语句,此处显示的数字表示查询返回行的数目。在状态栏(结果面板之下)的 右侧可看到相同的信息,同时还有其他一些有用的信息,如用户登录时使用的登录名、最后一 次运行查询时使用的当前数据库(即使改变了数据库下拉框中的数据库,当前数据库仍将保持不 变,直到在这个查询窗口运行下一个查询),以及执行该查询使用的时间。 示例说明 让我们看一下 SELECT 语句的一些细节信息。注意,SELECT 语句中的 SELECT 和 FROM 都是大写,而 SQL Server 并不要求全部采用大写,可以写成 SeLeCt 和 frOM,而该语句功能 不变。通常习惯采用全部大写,以增加程序的可读性。许多 SQL 编码器对所有的命令和关键字 采用约定的全部大写形式,而表名、列名和非常数变量名使用大小写混合形式。读者所选择或施 加的标准可能不同,但至少要遵守保持一致的规则。 注意:我和本书上一版作者 Bob 的一个共同特征是,我们都倾向于各持己见。我 现在仍然坚持自己的观点,并且为此找到了很充分的理由。要读取代码或记住拼 写不一致的表名是很痛苦的事情。当某人要查看您的代码,或者更重要的是要使 用您的列名或表名时,不应该占用他很长时间,让他凭经验来猜测您的代码。保 持拼写一致是最简单不过的事情,但我曾使用的几乎所有数据库中都没有做到这 一点。切记拼写要保持一致! SELECT 语句告诉“查询”窗口要完成什么工作,而符号“*”表示所要的信息(“*”表示 所有的列)。然后是 FROM 子句。 FROM 子句定义数据的来源。FROM 之后接一个或多个表名。在上面的查询中,所有的数 据都来自 Customer 表。 下面的语句表示选择更具体的信息,想要按照姓显示所有客户列表。 SELECT LastName FROM Person.Person; 代码段 Chap03.sql 返回结果如下所示: 44 第 3 章 T-SQL基本语句 Abbas Abel Abercrombie ... Zukowski Zwilling Zwilling 注意,为了简化起见,中间行使用了省略号,实际会返回 19 972 行。因为想要显示每个客 户的姓名,所以选择了所有的姓名。 注意:许多 SQL 编码人员养成了简化查询语句的习惯,通常在选择条件中使用符 号“*”来选择所有的列。这一习惯要改掉!输入符号“*”比输入想要的列名要 节省时间,但检索的数据也比实际需要的多。此外,SQL Server 必须计算“*”表 示多少列以及哪些列,这会降低应用程序和网络的使用性能。 在过去,我们必须完整地输入(因此必须完全记住)想要的每个列名。现在 SSMS 中内置了 IntelliSense,因此您只需要记住前几个字符。简而言之,良好的规则就是选 择所需要的列,不多不少。 试试另一个简单的查询。代码如下所示: SELECT Name FROM Production.Product; 代码段 Chap03.sql 同样,假设没有修改样本数据库中的数据,SQL Server 应返回 AdventureWorks 数据库中可 用的 504 种不同产品的列表: Name ---------------------------------------- Adjustable Race Bearing Ball BB Ball Bearing ... ... Road-750 Black, 44 Road-750 Black, 48 Road-750 Black, 52 您选择的列正好是 SELECT 语句后的 SELECT 列表。简而言之,SELECT 列表由查询中请 求输出的列名组成。 注意:您选择的列正好是 SELECT 语句后的 SELECT 列表。 45 SQL Server 2012 编程入门经典(第 4 版) 3.1.2 WHERE 子句 接下来介绍 WHERE 子句。WHERE 子句用来设定返回记录的限制条件。上述的查询中返回 的记录包括表中的每一行,即所要查看的信息没有任何限制条件。不加限制条件的查询在填充列 表框和组合框时非常有用,并且在其他想要提供域列表(domain listing)的情况下也非常有用。 注意:不要将域列表与 Windows 域的列表相混淆。域列表是要排除一些选择的列 表。例如,如果想要某人提供给您美国一个州的信息,则您可能要提供给他一个 列表,限制其选择范围在 50 个州。这样才能保证所选选项的信息有用。在讨论数 据库设计以及实体约束和域约束时,还会进一步涉及域的概念。 下面考虑更具体的信息。假设只想要列出指定产品名称的信息,而不是所有产品名的列表。 例如,使用查询语句返回 ProductID 为 356 的产品的名称、编号和订购点。 分解并构建该查询语句的过程如下:先考虑要返回的信息,因此使用 SELECT 语句。有关 所需信息的查询语句是期望返回产品的名称、编号和订购点,因此要清楚这些信息对应的列名。 另外还要清楚可以在哪个/哪些表中检索这些列。 图 3-2 如果您还没有记住数据库模式,那么下面就要考虑要 用到的表。之前已经使用过 Production.Product 表,因而您 清楚所要的信息就在那个表中。Production.Product 表包括 几列。可从 Management Studio 的 Production.Product 表的 “对象资源管理器”树中快速查看所要列选项的列表。 要在 Management Studio 中打开显示列表的窗口,可 单击 AdventureWorks 数据库下的“表”成员,然后将 Production.Product 表和 Columns 节点展开。如图 3-2 所 示,其中显示了各列及其数据类型以及是否可为 NULL 值。此外,本章后面的内容还会介绍查找这些信息的一 些其他方法。 没有名为 product name 的列,但确实有可能是我们 所要查询的 Name 列。其他两列很容易确认,列名只是 将两个单词之间的空格省去。 因此,所需要的信息可以通过 FROM 从 Products 表 查询,而 Name 列、ProductNumber 列和 ReorderPoint 列 用于从指定列获取信息: SELECT Name, ProductNumber, ReorderPoint FROM Production.Product 代码段 Chap03.sql 但以上查询语句仍不能返回所要的结果;它仍返回了太多的信息。运行上面的查询语句, 其返回了表中的每一个记录,而不是想要的那一行。 46 第 3 章 T-SQL基本语句 如果该表仅仅有少许的几个记录,则通过快速浏览返回的结果就能解决问题。毕竟浏览的 只是一个短的列表。但是,如果表包括的记录非常多,则该方法不可行。在任何有意义的系统 中,几乎不存在只有少量几行记录的表。您不希望被迫浏览 10 000 个记录。如果表有 100 000 或 1 000 000 个记录,那么要浏览所有的信息几乎是不可能的,而且返回结果要花费很长时间。 下面介绍如何在应用程序中设计这种查询以及快速而直接地找到所要的信息。 条件语句可以用来限制查询产品标识符为 356 的产品的结果,即 WHERE 子句。WHERE 子句跟在 FROM 子句之后,它定义要显示的记录必须满足的条件。本例中查询的条件是 ProductID 等于 356,因此查询语句为: SELECT Name, ProductNumber, ReorderPoint FROM Production.Product WHERE ProductID = 356 代码段 Chap03.sql 针对 AdventureWorks 数据库运行该查询语句,返回结果如下: Name ProductNumber ReorderPoint --------------- ----------------- ------------ LL Grip Tape GT-0820 600 (1 row(s) affected) 上面返回的结果正好是所要的信息,不多也不少。此外,该查询比第一个查询运行起来更快。 表 3-1 列出了 WHERE 子句中可以使用的所有运算符。 表 3-1 WHERE 子句的运算符 运 算 符 示 例 用 法 功 能 =、>、<、>=、 <=、<>、!=、!>、!< = = 'Bob' 标准的比较运算符,与其他编程语言中 的对应运算符功能基本相同,但要注意 以下两点: (1) “大于”、“小于”和“等于”可 能因选择的对照顺序不同而改变。如在 不区分大小写的情况下,“ROMEY”= “ romey ”;但在区分大小写时, “ROMEY”<> “romey”。 (2) != 和 <> 都表示“不相等”。而 !< 和 !> 分别表示“不小于”和“不大于” AND、OR、NOT = AND >= != "MyLiteral" OR = "MyOtherLiteral" 标准布尔逻辑运算符。可以用于在一条 WHERE 子句中结合多个条件。它们在 语句中运算的先后顺序为 NOT、AND、 OR。如果需要改变运算顺序,可以使用 小括号。注意这里不支持 XOR 47 SQL Server 2012 编程入门经典(第 4 版) (续表) 运 算 符 示 例 用 法 功 能 BETWEEN BETWEEN 1 AND 5 第一个值在第二个值与第三个值之间时 其值为 TRUE,其等价于 A≥B AND A≤ C。指定的值可以为列名、变量或字面量 LIKE LIKE "ROM%" 可使用%和_作为通配符。%表示可以代 替任意长度的字符串。_表示可以代替任 意的单个字符。[ ]符号用于指定一个字 符、字符串或范围,要求所匹配对象为 它们中的任一个(如[a-c]表示 a、b 和 c。 而[ab]表示 a 或 b)。^运算符的作用与 NOT 运算符相同,表示下一个字符是要 被排除的 IN IN (数字列表) IN ("A", "b", "345") 关键字 IN 左边的表达式与其右边的任 意值匹配时返回 TRUE。IN 常用于子查 询。第 7 章将进一步介绍该运算符 ALL、ANY、 SOME (比较运算符) (子查询) 子查询中的全部值/任意值满足比较运 算符(如<、>、=、>=)的条件时返回 TRUE。ALL 指示表达式要匹配结果集 中的所有值。ANY 和 SOME 功能相似, 在表达式匹配结果集中的任意值时返回 TRUE EXISTS EXISTS (子查询) 子查询返回至少一行记录时为 TRUE。 第 7 章将进一步介绍该运算符 注意:这些不是 SQL Server 中全部的运算符。它们只是适用于 WHERE 子句的运 算符。有一些只用于赋值(而不是比较)的运算符,它们不适用于 WHERE 子句。 3.1.3 ORDER BY 子句 在前面运行的几个查询示例中,多数查询结果按照字母顺序输出。输出结果的顺序是否纯 属偶然呢?答案会让您大吃一惊:确实纯属偶然。如果在查询中未指定查询结果的排列顺序, 则由 SQL Server 决定数据的输出顺序。输出结果的顺序通常使得 SQL Server 以最低系统开销 收集数据。通常的输出顺序按照表的物理存储顺序,或者按照 SQL Server 用于查找数据的索 引的顺序。 48 第 3 章 T-SQL基本语句 注意:Microsoft 的示例有一个令人讨厌的创建习惯,就是数据按照字母顺序出现 (有关其原因说来话长,但作者希望不要出现这种情况),但大部分数据不是按照这 样的顺序出现。简单地说,其原因与 Microsoft 插入数据的顺序有关。由于 Microsoft 通常是按字母排列顺序插入数据,ID 列也是按字母顺序(纯属偶然)。而大部分表 在物理上也是按 ID 顺序排列(第 9 章将更详细地介绍数据的物理排序),数据最终 按字母顺序呈现。如果 Microsoft 以更随机的名称顺序插入数据—现实情况很可 能是这样—那么在未指定按名称排序时,名称就会混乱排列。 ORDER BY 子句用来定义返回数据的排列顺序。在 ORDER BY 子句中可以使用任意的组 合列,只要按照 FROM 子句可以在表中找到这些列或它们的派生列。 考虑以下查询语句: SELECT Name, ProductNumber, ReorderPoint FROM Production.Product; 运行后产生以下结果: Name ProductNumber ReorderPoint ----------------------- ---------------- ------------ Adjustable Race AR-5381 750 Bearing Ball BA-8327 750 ... ... Road-750 Black, 48 BK-R19B-48 75 Road-750 Black, 52 BK-R19B-52 75 (504 row(s) affected) 果然,查询结果集按照 ProductID 次序进行排序。这是由于 SQL Server 认为使用索引来查 看数据最合理,而索引是按照 ProductID 来对数据记录进行排序的。这样就使得查询的系统开 销(就 CPU 和 I/O 而言)最低。当表中的记录变得越来越大时,SQL Server 可能会选择完全不同 的执行计划,而数据的排序也完全不同。可将查询语句作如下更改,但排序结果的顺序不变: SELECT Name, ProductNumber, ReorderPoint FROM Production.Product ORDER BY Name; 代码段 Chap03.sql 注意,以上查询语句中没有使用 WHERE 子句,是否需要使用 WHERE 子句由查询功能确定。 不过记住一点:如果确实用到了 WHERE 子句,则 WHERE 子句要放在 ORDER BY 子句之前。 但是,以上查询语句是否有 ORDER BY 子句对输出结果没有影响。但下面的查询语句将 根据 ProductNumber 的顺序来更改输出结果的排列顺序: 49 SQL Server 2012 编程入门经典(第 4 版) SELECT Name, ProductNumber, ReorderPoint FROM Production.Product ORDER BY ProductNumber; 代码段 Chap03.sql 这次输出的结果有很大的不同。输出的数据是相同的,但它们基本重新排列了: Name ProductNumber ReorderPoint ------------------------ ---------------- ------------ Adjustable Race AR-5381 750 Bearing Ball BA-8327 750 LL Bottom Bracket BB-7421 375 ML Bottom Bracket BB-8107 375 ... ... Classic Vest, L VE-C304-L 3 Classic Vest, M VE-C304-M 3 Classic Vest, S VE-C304-S 3 Water Bottle - 30 oz. WB-H098 3 (504 row(s) affected) SQL Server 还是选择系统开销最小的方法来输出查询的结果,但是由于查询语句的更改, 实际需要执行的特定任务也随之变动。 也可以使用数据类型为 numeric 的字段来对输出结果进行排序(注意,这次是查询一个新的表): SELECT Name, SalesPersonID FROM Sales.Store WHERE Name BETWEEN 'g' AND 'j' AND SalesPersonID > 283 ORDER BY SalesPersonID, Name DESC; 代码段 Chap03.sql 查询结果如下: Name SalesPersonID -------------------------------------- -------- ------------- Inexpensive Parts Shop 286 Ideal Components 286 Helpful Sales and Repair Service 286 Helmets and Cycles 286 Global Sports Outlet 286 Gears and Parts Company 286 Irregulars Outlet 288 Hometown Riding Supplies 288 Good Bicycle Store 288 50 第 3 章 T-SQL基本语句 Global Bike Retailers 288 Instruments and Parts Company 289 Instant Cycle Store 290 Impervious Paint Company 290 Hiatus Bike Tours 290 Getaway Inn 290 (15 row(s) affected) 对于上述查询语句要注意几点。其中包括了几个知识点,混合使用了多个 WHERE 条件子 句和一个 ORDER BY 子句;另外,在 ORDER BY 子句中作了一些调整。 ● ORDER BY 子句按多列排序。实现的方法是用逗号分隔要排序的列。这里是先按 SalesPersonID 排序,然后再基于 Name 添加一个子排序。 ● 加入了新的关键字 DESC。DESC 关键字使 SQL Server 的输出结果按 Name 降序排列, 而不是按照默认的升序排列(如果想要输出结果为升序排列,则可使用 ASC 关键字)。 注意:通常基于返回的一列来对结果进行排序,但值得注意的是, ORDER BY 子 句可基于查询使用的任何表中的任意列来排序,而不管其是否在 SELECT 列表中。 3.1.4 使用 GROUPBY 子句聚合数据 把 ORDER BY 子句与本章前面的 SELECT 语句的输出结果相比,我们能够改变查询结果 的顺序。先回顾一下 SELECT 语句的整个语法结构: SELECT [TOP () [PERCENT] [WITH TIES]] [FROM ] [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] [[FOR XML {RAW|AUTO|EXPLICIT|PATH [()]}[, XMLDATA] [, ELEMENTS][, BINARY base 64]] [OPTION (, [, ...n])] 为何本书先介绍位于 GROUP BY 子句之后的 ORDER BY 子句?原因有以下两点: ● ORDER BY 子句比 GROUP BY 子句更常用,因此我们希望您更多地实践前者。 ● 您要理解,只要按照 SQL Server 规定的顺序(按照语法结构定义),就可灵活应用 FROM 子句之后的所有子句。 GROUP BY 子句用于聚合信息。下面介绍一个没有使用 GROUP BY 子句的简单查询。假 设您想要知道一组给定订单中的零件订购数量。 SELECT SalesOrderID, OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672); 代码段 Chap03.sql 51 SQL Server 2012 编程入门经典(第 4 版) 产生的结果集如下所示: SalesOrderID OrderQty ------------ -------- 43660 1 43660 1 43670 1 43670 2 43670 2 43670 1 43672 6 43672 2 43672 1 (9 row(s) affected) 尽管只查询了 3 个订单,但可得到所有订单的每条订购详情。这时可使用计算器,也可使 用 GROUP BY 子句和聚合函数。这里将使用 SUM()函数: SELECT SalesOrderID, SUM(OrderQty) FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672) GROUP BY SalesOrderID; 代码段 Chap03.sql 这次返回了预期结果: SalesOrderID ------------ ----------- 43660 2 43670 6 43672 9 (3 row(s) affected) 按照期望,SUM 函数返回和,但是返回哪些数据之和呢?空白的列标题显然无法起到帮助 作用。可为结果提供一个别名,从而更容易使用它。稍微修改该查询,为输出提供一个列名: SELECT SalesOrderID, SUM(OrderQty) AS TotalOrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672) GROUP BY SalesOrderID; 代码段 Chap03.sql 得到的是相同的基本输出,但为分组的列提供了一个列名: SalesOrderID TotalOrderQty ------------ ------------- 43660 2 52 第 3 章 T-SQL基本语句 43670 6 43672 9 (3 row(s) affected) 注意:如果您只是想快速得到结果,就不需要像这里所做的那样为分组列使用别 名,但您所编写的许多查询可能要为其他大型程序的元素提供信息。利用这些查 询的代码将需要某种引用分组列的方法;在这种情况下为列命名有用的别名是很 关键的。稍后将讨论别名。 如果不使用 GROUP BY 子句,那么 SUM 函数将返回指定列的所有行中的值之和。而这里 使用了 GROUP BY 子句,因此 SUM 函数的结果是每一组中的值之和。 注意:在使用 GROUP BY 子句时,SELECT 列表中的所有列必须为聚合列(SUM、 MIN/MAX、AV G 等)或是 GROUP BY 子句中包括的列。同样,如果在 SELECT 列表中使用聚合列,SELECT 列表必须只包括聚合列,否则必须有一个 GROUP BY 子句。 也可以基于多列进行分组,这时只要在多个列名之间加逗号。例如,假设要查看每个销售 人员与前 10 名客户签订的订单数量。这时可以在 GROUP BY 子句中使用 SalesPersonID 和 CustomerID 两列(稍后将介绍 COUNT()函数的使用)。 SELECT CustomerID, SalesPersonID, COUNT(*) FROM Sales.SalesOrderHeader WHERE CustomerID <= 11010 GROUP BY CustomerID, SalesPersonID ORDER BY CustomerID, SalesPersonID; COUNT 函数用于计数,这里计算了给定销售人员与给定客户签订的订单数量: CustomerID SalesPersonID ---------- ------------- -------- 11000 NULL 3 11001 NULL 3 11002 NULL 3 11003 NULL 3 11004 NULL 3 11005 NULL 3 11006 NULL 3 11007 NULL 3 11008 NULL 3 11009 NULL 3 11010 NULL 3 (11 row(s) affected) 53 SQL Server 2012 编程入门经典(第 4 版) 聚合函数 聚合函数常用于 GROUP BY 子句,用于聚合分组的数据。如在上述的查询示例中,返回 了 OrderQty 列之和。对每一分组上选择的列进行计算并返回和,而分组由 GROUP BY 子句定 义。在 SUM 示例中,只有 SalesOrderID 一个分组。聚合函数可在更大范围内使用,这里只给 出了最常见的用法。 注意:聚合函数在和 GROUP BY 子句一起使用时显示出强大的功能,但聚合函数 的使用不限于分组查询;如果查询语句中使用了聚合函数,而没有使用 GROUP BY 子句,则聚合函数用于聚合整个结果集(匹配 WHERE 子句的所有行)。当不使 用 GROUP BY 子句时,在 SELECT 列表中某些聚合函数只能与其他的聚合函数 一起使用,即聚合函数必须在使用 GROUP BY 子句时才能在 SELECT 列表中与列 名配对。例如,不使用 GROUP BY 子句,SELECT 列表中的 AVG 只能与 SUM 对 应,但不能对应特定列。 1) AVG AV G 函数用于计算平均值。试着运行之前返回订单数量的查询,不过这次改为返回每个订 单的平均数,而不是每个订单的数量之和: SELECT SalesOrderID, AVG(OrderQty) FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672) GROUP BY SalesOrderID; 代码段 Chap03.sql 注意,返回结果完全改变: SalesOrderID ------------ ------------- 43660 1 43670 1 43672 3 (3 row(s) affected) 可对订单号为 43672 的 3 项记录之和求平均值进行验算:9/3=3。 2) MIN/MAX 顾名思义,MIN/MAX 用于计算所选择列分组的最小值与最大值。同样,对上一个查询示 例采用 MIN 函数进行修改: SELECT SalesOrderID, MIN(OrderQty) FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672) GROUP BY SalesOrderID; 代码段 Chap03.sql 54 第 3 章 T-SQL基本语句 返回的结果如下所示: SalesOrderID ------------ ----------- 43660 1 43670 1 43672 1 (3 row(s) affected) 再将查询示例改为采用 MAX 函数: SELECT SalesOrderID, MAX(OrderQty) FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672) GROUP BY SalesOrderID; 代码段 Chap03.sql 返回的结果如下所示: SalesOrderID ------------ ----------- 43660 1 43670 2 43672 6 (3 row(s) affected) 如果要在查询语句中同时使用 MIN 和 MAX 函数,那么也很简单,如下所示: SELECT SalesOrderID, MIN(OrderQty), MAX(OrderQty) FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672) GROUP BY SalesOrderID; 代码段 Chap03.sql 现在输出的结果多出了一列,产生了一点小问题: SalesOrderID ------------ ---------- ----------- 43660 1 1 43670 1 2 43672 1 6 (3 row(s) affected) 问题出在哪儿呢?虽然返回了所有需要的列,但现在不仅仅包括一列聚合信息,问题是如 何确定哪一列数据对应哪一个聚合数。当然,在这个示例中,可以确定两列分别是由 MAX 函 数产生的最大值和由 MIN 函数产生的最小值。但结果对应于哪一列通常并不会这么简单。这 时可以使用别名,它更改返回的结果集中的列名。可以使用 AS 关键字来创建别名: 55 SQL Server 2012 编程入门经典(第 4 版) SELECT SalesOrderID,MIN(OrderQty) AS MinOrderQty, MAX(OrderQty) AS MaxOrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672) GROUP BY SalesOrderID; 代码段 Chap03.sql 这样,下面所示的返回结果的含义会更明显: SalesOrderID MinOrderQty MaxOrderQty ------------ ----------- ----------- 43660 1 1 43670 1 2 43672 1 6 (3 row(s) affected) 注意,AS 关键字是可选的。它曾经(SQL Server 6.5 版本之前)为无效的关键字。删除两个 AS 关键字对查询的执行结果没有任何影响。同时注意还可以对任意列(甚至是表名,第 4 章会 演示)命名别名,而不仅仅是聚合函数。 再次运行最后一个查询语句,这次在某处不使用关键字 AS 为每一列命名别名: SELECT SalesOrderID AS 'Order Number', MIN(OrderQty) MinOrderQty, MAX(OrderQty) MaxOrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660, 43670, 43672) GROUP BY SalesOrderID; 代码段 Chap03.sql 尽管有些别名没有使用 AS 关键字,但输出的每一列的列名都改为别名: Order Number MinOrderQty MaxOrderQty ------------ ----------- ----------- 43660 1 1 43670 1 2 43672 1 6 (3 row(s) affected) 注意:必须承认,我通常在使用别名时也不使用 AS 关键字,而我也承认这是不 好的习惯。在 AS 关键字可用之前,我已经开始使用 SQL Server,但是旧习难改(因 而很容易忘记用 AS 关键字)。但是我强烈建议使用 AS 关键字。这样可使程序的 可读性更好,还可以使编写的代码符合 ANSI/ISO 标准。 对此进行说明的原因在于,我希望读者能按标准使用 AS 关键字,但同时也 希望读者了解一些其他使用方式,这样读者在看到一些稍有不同的用法时,就不 会感到困惑。 56 第 3 章 T-SQL基本语句 3) COUNT(表达式|*) COUNT(*)函数用于计算查询中返回的行数。先看一个最常见的查询示例: SELECT COUNT(*) FROM HumanResources.Employee WHERE HumanResources.Employee.BusinessEntityID = 5; 代码段 Chap03.sql 返回的结果与之前的示例查询返回的结果有所不同: ----------- 1 (1 row(s) affected) 观察与以前返回的查询结果的不同。第一,返回的所有列变为一个函数调用的结果,没有 默认列名。如果需要列名,则必须提供别名。第二,没有返回任何行记录。返回的结果表示查 询中满足 WHERE 条件的行数,查询的表由 FROM 子句确定。 注意:可以使用这个基本的查询语句来验证表中匹配 WHERE 条件的实际行数。 尝试运行不使用 WHERE 子句的查询: SELECT COUNT(*) FROM HumanResources.Employee; 代码段 Chap03.sql 如果没有对 Employee 表进行任何删除或插入操作,则应该返回如下结果集: ----------- 290 (1 row(s) affected) 数字 290 表示 Employee 表中的总行数。记住这一点,后面会用到。 现在可采用两种方法使用 COUNT 函数。前面已经讨论了使用带“*”选项的 COUNT 函数, 下面介绍使用表达式(通常为列名)的 COUNT 函数。 先用传统方法运行 COUNT 函数,但采用的是一个新表: SELECT COUNT(*) FROM Person.Person; 代码段 Chap03.sql 该表数据量较大,因此返回的 COUNT 数也较大: 57 SQL Server 2012 编程入门经典(第 4 版) ----------- 19972 (1 row(s) affected) 现在修改查询语句,选择对指定列进行计数: SELECT COUNT(AdditionalContactInfo) FROM Person.Person; 代码段 Chap03.sql 返回的结果与上一个示例的结果有所不同: ----- 10 Warning: Null value is eliminated by an aggregate or other SET operation. (1 row(s) affected) 新的结果带来了一个问题:既然每一行都有 AdditionalContactInfo 列,那么与通常的行计 数相比,为什么两次查询得到的 AdditionalContactInfo 列的计数不相同呢?答案很明显,不是 每一行的 AdditionalContactInfo 列都有值。简而言之,除了 COUNT(*)函数,以其他任意形式使 用的 COUNT 函数都忽略 NULL 值。下面验证正是 NULL 值导致了差异: SELECT COUNT(*) FROM Person.Person WHERE AdditionalContactInfo IS NULL; 代码段 Chap03.sql 产生的结果如下所示: ----------- 19962 (1 row(s) affected) 下面执行一个简单的算术: 10 + 19 962 = 19 972 AdditionalContactInfo 字段中定义了值的 10 条记录,加上 AdditionalContactInfo 字段中值为 NULL 的 19 962 行,正好是总行数 19 972。 注意:除了 COUNT(*)函数外,所有的聚合函数都忽略 NULL 值。要考虑到这一 点对聚合结果的重要影响。在求平均值时,许多用户把数值类型字段中的 NULL 值看作为 0,但实际上 NULL 值与 0 并不等同,并不能这样使用。如果对有 NULL 值的列执行 AV G 函数或其他聚合函数, NULL 值将不会计入聚合值中,除非使用 COALESCE()或 ISNULL()等函数,将 NULL 值转换成非 NULL 值。在第 7 章中将 继续深入介绍这一点,但在 T-SQL 编程或者数据库设计时要注意该问题。 为什么说这在数据库设计中很重要?因为它会关系到是否允许在字段中使用 NULL 值,要考虑到可能对数据库执行的查询方法和如何进行聚合操作。 58 第 3 章 T-SQL基本语句 COUNT 函数可与 GROUP BY 子句联合使用。 注意:对于下一个示例而言,需要加载和执行与可下载源代码一起打包的 BuildAndPopulateEmployee2.sql 文件(可从 wrox.com 网站获取)。 下列示例中对 Employee 表的所有引用应指向新的 Employee2 表,而不是 Employee 表。 例如,老板要求获取每位经理管理的职员人数。之前使用的语句可以是把表中所有的行累加(使 用 COUNT(*)),也可以是把表中列值不是 NULL 的所有行求和(使用 COUNT(ColumnName))。当加 入 GROUP BY 子句时,聚合函数执行的结果不变,只是聚合结果是针对每一个分组,而不是 整个表。这样,可以使用下列查询语句获得这一职员人数: SELECT ManagerID, COUNT(*) FROM HumanResources.Employee2 GROUP BY ManagerID; 代码段 Chap03.sql 注意只使用了 ManagerID 来进行分组,COUNT()函数是聚合函数,因此不必包括在 GROUP BY 子句中。 ManagerID ----------- ---------- NULL 1 1 3 4 3 5 4 (4 row(s) affected) 上述结果表明 ManagerID 为 1 的经理手下有 3 名职员,ManagerID 为 4 的经理有 3 名职员, ManagerID 为 5 的经理有 4 名职员。此外,返回结果表明有一个 Employee 记录的 ManagerID 字段为 NULL 值,即该职员不受任何人管理(估计是公司总裁)。 从技术上来说,可以使用不带任何聚合函数的 GROUP BY 子句,但这样的语句可能毫无 意义。要将所有行分组,SQL Server 必须要对所有的行执行操作,但从功能上说,可以使用 DISTINCT 选项来达到完全相同的效果(这点稍后会介绍),而且运行速度更快! 以上介绍了如何使用分组进行操作,下面介绍一个易于混淆的概念。当然,学完 3.1.5 节 之后,该概念对您可能只是小菜一碟。 3.1.5 使用 HAVING 子句给分组设置条件 以上介绍的查询条件都是针对指定行进行的。如果一行中的给定列没有特定的值,或者该 列值不在给定值的范围,则整行都被忽略。在使用分组之前确实存在这种情况。 如果要将查询条件放到分组之后,那么应该如何操作呢?换句话说,如果您想要将每一行 都添加到组中,但是随后想要表明只有在完全填满分组之后才应用查询条件,则应该如何操作 呢?这时就可以使用 HAVING 子句。 59 SQL Server 2012 编程入门经典(第 4 版) HAVING 子句仅用于带有 GROUP BY 子句的查询语句中。WHERE 子句应用于每一行(在 变成一个组的某一部分之前),而 HAVING 子句应用于分组的聚合值。 下面将 3.1.4 节中的 GROUP BY 查询稍做修改,查询要求返回分配给每位经理管理的 EmployeeID 的职员数目: SELECT ManagerID AS Manager, COUNT(*) AS Reports FROM HumanResources.Employee2 GROUP BY ManagerID; 代码段 Chap03.sql 第 4 章会介绍如何把 Manager 列中的姓名输出到 EmployeeID 列。该公司显然有 3 位经理。 除了一个不属于经理支配的人之外,所有的人都要向这 3 位经理汇报,这个人可能是公司的总 裁(可以使用查询语句来验证这一点,但这里暂且相信假设成立)。 由于查询中没有使用 WHERE 子句,因此 GROUP BY 子句对表中的每一行都进行操作, 且每一行都包括在一个分组中。下面将 WHERE 子句添加到该查询语句,看看查询结果会发生 什么变化: SELECT ManagerID AS Manager, COUNT(*) AS Reports FROM HumanResources.Employee2 WHERE EmployeeID != 5 GROUP BY ManagerID; 代码段 Chap03.sql 但返回的查询结果与预期的结果有点不同: Manager Reports ----------- ----------- NULL 1 1 3 4 2 5 4 (4 row(s) affected) 没有从结果集中排除任何行,但 ManagerID 4 相应的结果少 1(这和 ManagerID 5 有关系)。 WHERE 子句过滤掉 EmployeeID 为 5 的一行。由此可见,EmployeeID 5 由 ManagerID 4 管理, 因此 ManagerID 4 的总人数少 1(EmployeeID 5 不再计算在内)。ManagerID 5 不受影响,因为我 们是用 EmployeeID 而不是 ManagerID 进行排除。关键要知道是在应用 GROUP BY 子句之前排 除了 EmployeeID 5。 换个角度思考,看看如何解答下面这个问题:哪些经理要管理 3 个以上的职员?可以根据 不使用 WHERE 子句的 COUNT 查询结果来解决该问题,但如何通过编程来实现呢?即如何使 用查询语句来返回要管理 3 个以上的职员的经理?如果试图用 WHERE 子句来解决该问题,就 会发现根据聚合函数返回的结果行不能解决该问题—— 因为在聚合函数执行之前,WHERE 子 句已经由系统执行完成。下面使用 HAVING 子句来查询: 60 第 3 章 T-SQL基本语句 SELECT ManagerID AS Manager, COUNT(*) AS Reports FROM HumanResources.Employee2 WHERE EmployeeID != 5 GROUP BY ManagerID HAVING COUNT(*) > 3; 代码段 Chap03.sql 试一试执行该查询,下面的结果与想要的结果更加相近: Manager Reports ----------- ----------- 5 4 (1 row(s) affected) 只有 1 位经理要管理 3 个以上的职员。 3.1.6 使用 FOR XML 子句输出 XML SQL Server 自身有大量支持 XML 的功能。它在满足 XML 数据存储和操作需求方面提供了 相当健壮的支持,从有效索引 XML 数据到根据模式文档验证 XML。 在 SQL Server 的 XML 支持功能中,最早就有的一项功能是可与 SELECT 语句一起使用的 FOR XML 子句。使用它,可使查询结果以 XML 格式输出,另外可用许多选项精确地指定 XML 输出的样式。因为 XML 的讨论要涉及 XML 本身,所以这里暂且不详细介绍 XML 子句,而在 本书第 16 章再详细介绍。目前还是先学习基本知识。 3.1.7 通过 OPTION 子句利用提示 OPTION 子句是一种用来最优化运行 SQL 查询的方法,它重写了 SQL Server 的某些理念。 因为 SQL Server 通常能做到最优化查询,所以使用 OPTION 子句反而可能画蛇添足。不过,还 是有必要对它有所了解。 本书后面的章节涉及锁定时会大量介绍查询提示,这里您只要理解 OPTION 子句影响提示 信息,掌握 OPTION 子句几乎不需要什么基础,所以这里不做介绍。 3.1.8 DISTINCT 和 ALL 谓词 DISTINCT 和 ALL 谓词也是操作语句中的重要概念,与重复数据的处理有关。 例如,假设想要显示在一次销售中订单数量大于 30 的所有产品的 ID 列表。可以很容易使 用以下查询语句来从 SalesOrderDetail 表中获得信息: SELECT ProductID FROM Sales.SalesOrderDetail WHERE OrderQty > 30; 代码段 Chap03.sql 结果就是从 SalesOrderDetail 表中返回订单数量大于 30 的每一行的匹配 ProductID: 61 SQL Server 2012 编程入门经典(第 4 版) ProductID ----------- 709 863 863 863 863 863 863 863 715 863 ... ... 869 869 867 (31 row(s) affected) 从技术角度来说,结果满足要求,而实际返回的结果并不符合要求。返回结果中有很多 重复行。尽管我们可以查看哪些产品是在一次销售中订单数量大于 30,但返回的行数和重复 行的数目实在太多。与前面讨论的问题一样,SQL 对此有解决方法,即采用 SELECT 语句中 的 DISTINCT 谓词。 对上述查询语句稍做修改后再运行一次: SELECT DISTINCT ProductID FROM Sales.SalesOrderDetail WHERE OrderQty > 30; 代码段 Chap03.sql 这次返回的结果才是真正的一次订单数量超过 30 的 ProductID: ProductID ----------- 863 869 709 864 867 715 (6 row(s) affected) 此时返回结果的数量大大减小,而且返回的内容也更合理。该查询的另一个优点是比第一个 查询语句的实际执行速度更快。本书后面讨论性能问题时会深入探讨其中的原因,这里只要知道, 因为不需要返回所有的行,所以对于满足查询的要求来说,也减少了 SQL Server 的工作量。 到此还没有介绍完 DISTINCT 谓词。DISTINCT 谓词不仅能用于 SELECT 语句,还能用于 62 第 3 章 T-SQL基本语句 聚合函数的表达式中。注意比较以下 3 个查询语句。 第一个查询语句用于获取 AdventureWorks 数据库中 SalesOrderDetail 表的行数: SELECT COUNT(*) FROM Sales.SalesOrderDetail; 代码段 Chap03.sql 如果 SalesOrderDetail 表未做修改,那么此时返回的结果应为 121 317 行。 下面运行相同的查询,但是对指定列进行计数: SELECT COUNT(SalesOrderID) FROM Sales.SalesOrderDetail; 代码段 Chap03.sql 由于 SalesOrderID 列为该表的键的一部分,因此其值不能为 NULL(有关索引的内容将在 第 9 章进行更多介绍)。所以本查询返回的结果与使用 COUNT(*)查询返回的结果相同,都为 121 317 行。 注意:键(key)是数据库的一个术语,用于描述一列或多列,这些列用于标识表中 的一行。实际上有几种不同的键(本书第 6、8、9 章将详细介绍),通常“key”是 指表的主键。主键是能唯一标识行的一列或列组。当使用主键来标识行时,可以 肯定返回的结果只有 1 行记录,因为在同一个表中不存在两行有相同的主键。 下面的示例对查询语句做如下更改: SELECT COUNT(DISTINCT SalesOrderID) FROM Sales.SalesOrderDetail; 代码段 Chap03.sql 这次返回完全不同的结果: ----------- 31465 (1 row(s) affected) 因为在聚合发生之前将所有的重复行先排除,所以返回的结果行更少。 注意:可以在任意聚合函数中使用 DISTINCT 谓词,即使在聚合函数中使用 DISTINCT 谓词没有实际意义。例如,在 AV G 函数中使用 DISTINCT 就没有任何 意义。 这里顺便介绍 ALL 谓词。很少在查询语句中使用 ALL 谓词。毫无疑问,ALL 与 DISTINCT 谓词含义相反。DISTINCT 谓词用于过滤掉重复的行,而 ALL 指包括所有的行。ALL 是任意 SELECT 语句的默认值,但有 UNION 子句的 SELECT 语句除外。第 4 章会详细介绍 ALL 谓词 63 SQL Server 2012 编程入门经典(第 4 版) 对 UNION 用法的影响,这里只要记住 ALL 表示除了 DISTINCT 谓词之外的任意情况。 3.2 使用 INSERT 语句添加数据 现在,读者应该对基本 SELECT 语句有了很好的掌握。接下来开始讨论 SQL Server 的另一 个主要问题。如果不能先把数据输入到数据库,则无法得到数据库中大量的数据。可以使用 INSERT 语句来解决这个问题。 INSERT 语句的整体语法结构如下所示: INSERT [TOP ( ) [PERCENT] ] [INTO] [()] [ OUTPUT ] { VALUES () [,()] [, …n] | | EXEC | DEFAULT VALUES 这个结构看起来有些繁杂,因此需要稍作简化。INSERT 语句更基本的语法结构如下所示: INSERT [INTO]
[()] VALUES () [,()] [, …-n] 下面介绍各部分的功能。 在该结构中,INSERT 为该语句的实际操作,告诉 SQL Server 使用该语句执行什么功能, 而 INSERT 后面的部分仅用于说明该操作的详细信息。 INTO 关键字无真正含义,唯一的目的是增强整个语句的可读性。INTO 关键字完全可选, 这里强烈建议在语句中加入该关键字,因为可增强语句的可读性。在学完本节之后,同时试试 在该语句中加入 INTO 关键字和不加入该关键字。不加入 INTO 关键字的确省事,但读起来有 点奇怪—用不用 INTO 关键字由您决定。 语法结构中接下来是要插入数据的表(从技术角度来讲,可能会是表、视图或常见表的表达 式,不过这里只讨论向表中插入数据的情况)。 到目前为止,语法结构的含义还是相当简单的。接下来的列列表(column list)部分更困难。 显式列列表为可选项(在此明确规定用于接收数据值的列),但在不提供列列表时要特别谨慎。 如果不显式提供列列表,则要假定 INSERT 语句中的每一个值与表中的列一一对应(第一个值对 应第一列,第二个值对应第二列,依此类推)。而且必须根据每一列的顺序提供插入值,直到最 后一列不允许 NULL 值也没有默认值(稍后读者会明白其含义)。例外的是 IDENTITY 列,在提 供值时应忽略它(SQL Server 将自动填充它)。总之,列列表是一列或多列的列表,而 INSERT 语句要插入的数据安排在语句的下一部分。 最后提供要插入的值。有两种方式提供插入值,这里只介绍显式提供数据来插入一行。要 提供插入值,需要使用 VALUES 关键字,然后是要插入值的列表,圆括号里的各插入值之间用 逗号分隔。插入值列表中的项数必须与列列表中的列数完全匹配。插入值的数据类型必须能匹 64 第 3 章 T-SQL基本语句 配或隐式转换成与列对应的数据类型。 注意:是否要对所有列专门声明一个值?强烈建议每一次都要给每一列命名,即 使只是使用 DEFAULT 关键字或显式给定 NULL 值。DEFAULT 告诉 SQL Server 为该列使用默认值 (如果没有默认值,则会出错 )。 这样做的好处是增强了代码的可读性,可以很清楚地知道哪一列要插入哪一 个值。此外,显式给定每一列的值可以减少错误。如果某个人向该表中插入一列, 则列出每一列的 INSERT 语句仍然可以正常工作,但没有列出每一列的 INSERT 语句则会出错。更糟糕的是,如果重新排序列 (这种情况不常见,但有可能发生 ), 则在没有显式给定值的情况下,数据就会被加载到错误的字段中。 下面来做一些练习。 要练习 INSERT、UPDATE 和 DELETE 操作,首先要创建一些用于操作的表(AdventureWorks 有些庞大)。要尝试下面几个示例,需要运行一些还未讨论的语句。读者暂且不用为其内容而担 心,因为第 5 章将详细讨论它们。 可以自己输入这些代码并执行它们,或是使用本书的可下载文件中包含的 Chap3CreateExample Tables.sql 文件。 注意:下面这个代码块称为脚本。这个脚本由一个批处理组成。第 11 章将详细介 绍批处理。 /* This script creates a couple of tables for use with ** several examples in Chapter 3 of Beginning SQL Server ** 2012 Programming */ CREATE TABLE Stores ( StoreCode char(4) NOT NULL PRIMARY KEY, Name varchar(40) NOT NULL, Address varchar(40) NULL, City varchar(20) NOT NULL, State char(2) NOT NULL, Zip char(5) NOT NULL ); CREATE TABLE Sales ( OrderNumber varchar(20) NOT NULL PRIMARY KEY, StoreCode char(4) NOT NULL FOREIGN KEY REFERENCES Stores(StoreCode), OrderDate date NOT NULL, Quantity int NOT NULL, Terms varchar(12) NOT NULL, TitleID int NOT NULL ); 代码段 Chap03.sql 65 SQL Server 2012 编程入门经典(第 4 版) 图 3-3 下面的插入练习主要集中于刚才创建的 Stores 表,因此先回顾 一下 Stores 表的属性。为此,在 Management Studio 的“对象资源管 理器”窗口中,在运行上面的脚本时展开当前数据库(可能是 AdventureWorks 给出我们正在运行的其他示例脚本)的“表”节点, 然后展开“列”节点,如图 3-3 所示。注意要查找的是刚才创建的 dbo.Stores 表,而不是 AdventureWorks 附带的 Sales.Store 表。 该表中所有列的数据类型为 char 或 varchar。 SQL Server 假设要插入的数据与表的每列一一对应,插入语句忽 略列列表可选项。第一个示例如下所示: NSERT INTO Stores VALUES ('TEST', 'Test Store', '1234 Anywhere Street', 'Here', 'NY', '00319'); 代码段 Chap03.sql 如前所述,除非给定不同的列列表(稍后介绍如何给定列列表),否则给定的所有要插入 的值必须与表中定义的列一一对应。执行该查询语句后,返回提示信息:本查询插入了 1 行 数据。然后再运行一次本查询,此时得到下列出错提示信息: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__Stores__1387E197'. Cannot insert duplicate key in object 'dbo.Stores'. The statement has been terminated. 注意:系统为上述主键约束生成的名称在您的 SQL Server 实例上可能会有所不同。 在创建键时,请花费一些时间为其设置描述性的名称。 为何该语句第一次可以执行,而第二次执行出错呢?其原因是本表的 StoreCode 字段为主 键,该字段值不允许重复。只要更改该主键值,而剩下的其他列值不变,就可以将其插入到新 的一行。在有关数据库设计与约束的章节中会更详细地介绍主键。 可使用以下语句检查刚插入的数据: SELECT * FROM Stores WHERE StoreCode = 'TEST'; 代码段 Chap03.sql 返回的结果正是插入的数据: StoreCode Name Address City State Zip --------- ---------- -------------------- ------ ------ ----- TEST Test Store 1234 Anywhere Street Here NY 00319 (1 row(s) affected) 注意,作者删除了每一列列名后面的空格,使得整个输出结果更加整齐,但实际插入的数 66 第 3 章 T-SQL基本语句 据正是要插入的数据。 再对插入给定列的语句稍作修改: INSERT INTO Stores (StoreCode, Name, City, State, Zip) VALUES ('TST2', 'Test Store', 'Here', 'NY', '00319'); 代码段 Chap03.sql 注意,对以上语句插入数据值的行做了两处改动。第一,更改了要插入的主键值,这样插 入数据时不会出错。第二,省略了与 Address 列对应的值,这是因为在列列表中已经忽略了该 列。有几种情况可以忽略列列表中的列,而且不用在 INSERT 语句中提供与该列对应的插入数 据。目前,仅利用了 Address 列不是必须给定插入值的列这一事实,因为如果没有提供该列的 插入值,且该列没有默认值,则该列接受 NULL 值(稍后会详细介绍默认值)。当执行插入语句 时,该列被设置为 NULL 值。下面对 SELECT 语句稍做修改,再次运行并验证插入值: ELECT * FROM Stores WHERE StoreCode = 'TST2'; 代码段 Chap03.sql 返回的结果有一点不同: StoreCode Name Address City State Zip --------- -------- -------- ------- ------ ----- TST2 Test Store NULL Here NY 00319 (1 row(s) affected) 可以注意到,为省略的列插入了 NULL 值。 注意,忽略的列必须允许设置为 NULL 值。顾名思义,它意味着该列允许有 NULL 值。本 书会采用大量篇幅来介绍列是否可赋 NULL 值,现在只要记住,有些列可以设置 NULL 值,有 些列不能设置为 NULL 值。在插入数据时,对于允许设置为 NULL 值的列,可以省略插入数据。 如果列不能设置 NULL 值,则必须为以下 3 种情况之一,否则系统会提示出错信息,INSERT 命令被拒绝执行。 ● 列定义为默认值。默认值是在未提供插入值时自动插入的一个常量值。第 7 章会介绍 如何定义默认值。 ● 列定义为接受某种形式的系统生成值。最常用的系统生成值为 IDENTITY 值(在数据库 设计的章节将详细介绍),即系统通常从第 1 个记录开始计数,在到达第 2 个记录时增 加到 2,以此类推。这些值并不是真正的“行编号”,因为行以后可能会被删除,而这 样的行编号在某种情况下会有跳跃,但这些值还是可以作为每一行的标识符。其他较少 使用的默认值包括 SYSDATETIME()或从 SEQUENCE 接收的值(第 11 章将详细介绍 SEQUENCE 对象)。 ● 插入数据时已经提供了该列的值。 67 SQL Server 2012 编程入门经典(第 4 版) 出于全面介绍的考虑,再举一个INSERT语句的示例。这次要把一个新的销售记录插入Sales 表中。要查看 Sales 表的属性,可以像对 Stores 表那样打开其“属性”对话框,或者也可以运 行系统存储过程 sp_help。存储过程 sp_help 的功能是给出任意数据库对象、用户自定义数据类 型或 SQL Server 数据类型的信息。执行存储过程 sp_help 的语法结构如下: EXEC sp_help 要查看 Sales 表的属性,只要在“查询”窗口中输入以下命令: EXEC sp_help Sales; 代码段 Chap03.sql 返回如下信息: Column_name Type Computed Length Prec Scale Nullable ---------- ------ -------- ------ ----- ----- ------- OrderNumber varchar no 20 no StoreCode char no 4 no OrderDate date no 3 10 0 no Quantity int no 4 10 0 no Terms varchar no 12 no TitleID int no 4 10 0 no Sales 表有 6 列,特别注意 Quantity、OrderDate 和 TitleID 列;它们具有到现在为止的 INSERT 语句中还未使用过的数据类型。 本查询语句中需要注意的是插入数据的类型格式化问题。插入数值不需要使用引号,而插 入字符数据时要使用引号。但插入 date 数据类型时需要使用引号(实际上,其作为字符串输入, 然后再转换成日期)。 INSERT INTO Sales (StoreCode, OrderNumber, OrderDate, Quantity, Terms, TitleID) VALUES ('TEST', 'TESTORDER', '01/01/1999', 10, 'NET 30', 1234567); 代码段 Chap03.sql 该语句返回的信息为:(1 row(s) affected)。但要注意,在插入操作中移动了列顺序。虽然 插入语句的 VALUES 部分的数据需要与列列表匹配,但列列表可以为任意顺序;它不必按照物 理表中列出的列顺序。 注意:这里使用的 date 数据格式为 MM/DD/YYYY,该格式在美国很常用,但是 您可以使用其他广泛使用的格式 (如国际上更为常用的格式 YYYY-MM-DD)。服务 器上默认使用的格式会因您购买的是 SQL Server 本地化版本或者服务器上的设置 被更改而有所不同。 68 第 3 章 T-SQL基本语句 3.2.1 多行插入 一次插入多行是 SQL Server 2008 的新功能。要实现这一点,只需要添加额外的用逗号分 隔的插入值,如下所示: INSERT INTO Sales (StoreCode, OrderNumber, OrderDate, Quantity, Terms, TitleID) VALUES ('TST2', 'TESTORDER2', '01/01/1999', 10, 'NET 30', 1234567), ('TST2', 'TESTORDER3', '02/01/1999', 10, 'NET 30', 1234567); 代码段 Chap03.sql 该插入语句使用一条语句插入了两组值。要检查结果,可查询 Sales 表: SELECT * FROM Sales; 代码段 Chap03.sql 显然,得到了之前插入的一行及刚插入的两行: OrderNumber StoreCode OrderDate Quantity Terms TitleID --------------- --------- --------- --------- ------ ------ TESTORDER TEST 1999-01-01 10 NET 30 1234567 TESTORDER2 TST2 1999-01-01 10 NET 30 1234567 TESTORDER3 TST2 1999-02-01 10 NET 30 1234567 (3 row(s) affected) 注意:执行多个插入操作的功能可以潜在提升性能。以前,客户端应用程序必须 为要插入的每条数据行单独发出插入命令(也有其他一些解决方法,不过需要开发 人员作些思考和努力,很少有开发人员愿意这样做)。而使用这一方法可以大大减 少往返服务器的次数;记住,这也意味着应用程序将不向后兼容以前的 SQL Server 版本。 3.2.2 INSERT INTO…SELECT 语句 如何一次插入一块数据?前面讲到,可以显式执行多行插入,但如果要插入的数据块是从 其他数据源选择获得的,又该怎么办?其他数据源包括: ● 数据库中的另一个表 ● 同一台服务器上的一个完全不同的数据库 ● 来自另一个 SQL Server 的异构查询或其他数据 ● 同一个表(通常在这种情况下,是在执行数学计算或者 SELECT 语句中的其他调整)。 INSERT INTO…SELECT 语句可完成一次插入一个数据块的功能。其语法结构为 INSERT 语句与 SELECT 语句语法结构的组合,如下所示: 69 SQL Server 2012 编程入门经典(第 4 版) INSERT INTO
[]
SET = [, = ] [FROM ] [WHERE ] 71 SQL Server 2012 编程入门经典(第 4 版) 可在多个表上创建 UPDATE 语句,但仅会更新一个表的数据。即可以在多个不同表中构建 条件或检索数据,但在同一时刻只有一个表作为更新操作的主体。不要太担心这一点—— 连接 多个表还未介绍(第 4 章将介绍),因此这里不会涉及复杂的 UPDATE 语句。以下举一个简单的 UPDATA 语句示例。 首先对 3.2 节中 INSERT 语句插入的数据进行更新。再次运行查询以查看插入的 1 行数据(不 要忘记切换回 pubs 数据库): SELECT * FROM Stores WHERE StoreCode = 'TEST'; 代码段 Chap03.sql 返回结果如下所示: StoreCode Name Address City State Zip --------- ---------- -------------------- ----- ----- ----- TEST Test Store 1234 Anywhere Street Here NY 00319 更新 City 列的值: UPDATE Stores SET City = 'There' WHERE StoreCode = 'TEST'; 代码段 Chap03.sql 与 INSERT 语句的运行相似,SQL Server 没有返回更多的信息: (1 row(s) affected) 但当再次运行 SELECT 语句时,您会发现 City 列的值确实已更新: StoreCode Name Address City State Zip --------- ------- ------- ------- ----- ----- TEST Test Store 1234 Anywhere Street There NY 00319 注意,还可通过添加逗号和额外的列表达式来改变更多列的值。例如,下列语句将用来更 新两列: UPDATE Stores SET City = 'There', State = 'NY' WHERE StoreCode = 'TEST'; 代码段 Chap03.sql 如果可以选择,则 SET 子句还可以使用表达式,而不是使用之前示例中采用的确定值。例 如,通过将现有商店名与一个后缀相连接,可为所有商店名添加后缀: 72 第 3 章 T-SQL基本语句 UPDATE Stores SET Name = Name + ' - ' + StoreCode; 代码段 Chap03.sql 执行完上述更新语句后,对 Stores 表运行 SELECT 语句: SELECT * FROM Stores 代码段 Chap03.sql 这时将会发现 Name 列都添加了相应的 StoreCode 后缀: StoreCode Name Address City State Zip ---------- --------------- ------------------ ------ ---- ---- TEST Test Store - TEST 1234 Anywhere Street There NY 00319 TST2 Test Store - TST2 NULL Here NY 00319 返回的结果表明,一条简单的 UPDATE 语句功能的相当强大,而这仅是开始。后面的章 节将对 UPDATE 作进一步的探讨。 注意:SQL Server 功能非常强大,我们可以更新任一列数据(但有一些列不能更新, 如 timestamp),但在更新主键时应非常谨慎。更新主键会“孤立”其他数据(这些 数据引用更改的主键)。 例如,Stores 表中的 StoreCode 字段是主键。如要将 Stores 表中 StoreCode 字 段的数据 10 改为 35,且与 Stores 表中 StoreCode 记录相关的所有 StoreCode 值没 有从 10 更新为 35,则与 Stores 表相关的 Sales 表中的任何数据都可能被“孤立” 或“丢失”。当要执行该更新操作时,由于存在引用 Stores 表的约束,SQL Server 在这种情况下将禁止这种“孤立”操作的执行(第 7 章将详细介绍约束),但最好不 要更新主键。 3.4 DELETE 语句 DELETE 语句是本章介绍的所有语句中最为简单的语句。即使是最复杂的语法形式,该语 句中也没有列列表,通常只有表名和 WHERE 子句。DELETE 语句的完整语法结构如下所示: DELETE [TOP ( ) [PERCENT] ] [FROM] [ OUTPUT ] [FROM
] [WHERE | CURRENT OF [GLOBAL] ] 其基本语法非常简单: DELETE
73 SQL Server 2012 编程入门经典(第 4 版) [WHERE ] WHERE 子句的工作方式与其在其他语句中相同。由于要删除整行(而不是半行),因此不 需要提供列列表(使用 UPDATE 语句删除半行)。 由于 WHERE 子句的使用较简单,因此本节只对 3.2 节的插入数据作快速删除。首先,运 行 SELECT 语句,确保插入的第一行还在表中: SELECT * FROM Stores WHERE StoreCode = 'TEST'; 代码段 Chap03.sql 如果还未删除,那么应能找到之前使用原始 INSERT 语句插入的一行数据。可执行如下语 句进行删除: DELETE Stores WHERE StoreCode = 'TEST'; 代码段 Chap03.sql 可以注意到,SQL Sever 拒绝删除该行,因为其违反了参照完整性: Msg 547, Level 16, State 0, Line 1 The DELETE statement conflicted with the REFERENCE constraint "FK__Sales__StoreCode__1B29035F". The conflict occurred in database"AdventureWorks", table"dbo.Sales", column 'StoreCode'. The statement has been terminated. SQL Server 不允许删除作为外键约束一部分被引用的行。第 7 章会详细介绍外键,现在只 要记住,如果一行使用外键引用另一行(无论是否在同一个表中),则要先删除被引用行后才能 删除引用行。上一条 INSERT 语句在 Sales 表中插入了 StoreCode 为 TEST 的一行记录,这行记 录正在引用想要删除的记录。 在删除 Stores 表中的该行记录之前,必须先删除在 Sales 表中引用的该行记录: DELETE Sales WHERE StoreCode = 'TEST'; 代码段 Chap03.sql 再一次运行第一条 DELETE 语句可成功删除该行: DELETE Stores WHERE StoreCode = 'TEST'; 代码段 Chap03.sql 有两种方法来快速验证数据是否真正被删除。一种方法是在执行 DELETE 语句时会自动 弹出消息说明已经删除一行。另外一种方法是在再次运行 SELECT 语句时没有返回任何行。 74 第 3 章 T-SQL基本语句 再对 DELETE 语句稍作修改,就可以删除第二行记录: DELETE Sales WHERE StoreCode = 'TST2'; 代码段 Chap03.sql 删除语句非常简单!与本章中介绍的其他语句一样,在介绍更复杂的搜索条件时,本书会 再次介绍 DELETE 语句的使用。 3.5 本章小结 T-SQL 是 SQL Server 的 ANSI/ISO SQL 或结构化查询语言的“方言”。它在很大程度上遵 循 ANSI/ISO 标准,但同时又有很多其自身的扩展,后面几章会对此做更多的介绍。 为了实现向后兼容,SQL Server 语句有多种不同的语法结构,但功能相同,这时应尽可能 使用 ANSI 标准格式。当有不同的语法结构时,本书通常都会一一介绍,但读者还是应尽可能 使用 ANSI 标准格式。当后台或数据库服务器可能要更改时,这就显得特别重要。ANSI 代码在 新的数据库服务器上非常有可能运行,而只是 T-SQL 的代码则肯定不能运行。 在本章中,读者已掌握了 T-SQL 中的操作单个表的语句, 但实际上通常要从多个表中获得 信息。第 4 章将介绍如何使用 JOIN 语句来操作多个表。 练习题 1. 编写输出 AdventureWorks 数据库的 Product 表(Production 模式中)中所有行和列的数据 的查询语句。 2. 修改练习 1 的查询语句,仅搜索无 ProductSubcategoryID 的产品(提示:有 209 个产品, 需要搜索 NULL 值)。 3. 在 AdventureWorks 数据库的 Location 表(Production 模式中)中添加一行。 4. 删除刚刚添加的行。 X本章内容总结 主 题 概 念 SELECT SELECT 是所有 SQL DML(数据操作语言)语句中最基本的语句,它用于从表中检索数 据。您经常需要从表中选择(SELECT)满足某些条件的数据 GROUP BY 通过在希望聚合的字段上使用聚合函数(例如 SUM、MAX、MIN 和 AV G)并添加 GROUP BY 子句以按照其他字段分组,可以在 SELECT 语句中聚合数据,而不是精确地返回表 中显示的行 过滤 在执行聚合之前使用 WHERE 语句过滤结果,而在执行聚会之后使用 HAVING 语句过 滤结果。使用布尔测试完成过滤 DISTINCT 要删除结果中的重复行,可以使用 SELECT DISTINCT 语句。在返回的所有字段中检查重 复现象 75 SQL Server 2012 编程入门经典(第 4 版) (续表) 主 题 概 念 INSERT 要将数据放入表中,可以使用 INSERT 语句插入它。在 INSERT 语句中添加要插入的字 段列表、希望在其中加载数据的表以及使用 VALUES 语句输入的字面值列表,或者是 产生相配字段列表的 SELECT 语句 UPDATE 要修改表中的数据,可以使用 UPDATE table_name 语句。使用 SET field_name=value 指 定想要改变的值,并且添加 WHERE 条件以限制更新的表行 DELETE 可以使用 DELETE FROM table_name 从表中(永久)删除行。使用 WHERE 子句限制删除 的行 76 第 章 SQL Server 存储和索引结构 本章内容简介: ● 对数据检索进行索引的价值 ● 索引所需的成本 ● 如何构造索引 ● 索引的类型 ● 选择索引的一些指导原则 ● 如何表明何时选择了合适的索引 ● 让 SQL Server 告诉您使用什么索引的方式 索引是数据库规划和系统维护的一个关键部分。它们为 SQL Server(以及任何其他的数据库 系统)提供了查找数据和定位到数据物理位置的快捷方式的其他方法。通过添加正确的索引可以 大大减少查询的执行时间。但是,许多设计很差的索引实际上会增加查询运行所花费的时间。事 实上,索引正逐步成为 SQL Server 中最容易令人误解的对象,因此也是最容易管理不当的对象。 本章将从开发人员和管理员的角度学习索引,但是为了理解索引,也需要理解在 SQL Server 中是如何存储数据的。因此,本章将首先介绍 SQL Server 的数据存储机制。一旦您理解了索引 实际尝试组织的内容,查看它们的工作方式就会更有意义。 9.1 SQL Server 存储机制 SQL Server 中的数据可以看成是以某种层次结构形式存在的。这种层次结构相当简单。其 中的一些对象是将要直接处理的,因此很容易理解。少数其他的一些对象存在于内部,虽然在 某些情况下可以直接处理它们,但通常是不可以的。下面将逐个介绍这些对象。 9.1.1 数据库 数据库很容易理解。我常听见人们说:“数据库,我知道的。”是的,您可能确实了解它, 但这里将它作为单个实体特别列出,是因为(对于给定服务器来说)它是最高级别的存储机制。 SQL Server 2012 编程入门经典(第 4 版) 尽管不能显式创建一个数据库级锁,但这仍是可以建立锁的最高级别。 注意:锁是系统使用的一种占位标记。当使用 SQL Server(或其他数据库 )进行开发 时, 您将发现理解和管理锁对于系统而言是非常关键的。 注意:第 14 章将详细介绍锁,但是在介绍存储机制时会顺带讨论一下 SQL Server 中对象的可锁定性。 9.1.2 区段 区段(extent)是用来为表和索引分配空间的基本存储单元。它由 8 个连续的 8KB 数据页组 成,共计 64KB 大小。 基于区段(而不是实际使用空间)分配空间的概念对于习惯于操作系统存储原理的人而言可 能有些难以理解。关于区段的要点包括以下两方面: ● 一旦区段已满,那么下一记录将要占据的空间不是记录的大小,而是整个新区段的大 小。许多 SQL Server 的新手都会在空间估计上犯错误,一部分原因是由于一次分配一 个区段而不是一个记录。 ● 通过预先分配空间,SQL Server 节省了为每个记录分配新空间的时间。 只是因为向当前分配的区段添加了过多的行就要占用整个区段,这看起来是一种浪费,但 是这种方法所浪费的空间数量一般不是非常多。尽管如此,这些浪费的空间会累加——特别在 碎片较多的环境中——因此应当谨记这一点。 占据所有空间的好处是 SQL Server 省去了一些分配时间的系统开销。不需要在每次写入行 时都考虑分配问题,SQL Server 仅仅当需要新的区段时才处理额外的空间分配。 注意:不要将区段占用的空间和数据库占用的空间相互混淆。分配给数据库的那 些空间就是磁盘驱动器将要减少的可用空间。区段仅仅是在由数据库保留的整个 空间内依次进行分配的方式。 9.1.3 页 与区段是数据库中的分配单元类似的是,页是特定区段中的分配单元。每个区段包含 8 页。 页是在到达实际数据行之前所能达到的最后一个存储级别。尽管每个区段中的页数是固定 的,但每一页中的行数不是固定的——这完全取决于行的大小,而行的大小是可以变化的。可 以把页看做是表行和索引行数据的容器。通常不允许行跨页。 1. 页类型 有许多种不同的页类型。本书只涉及以下两种类型: ● 数据:数据页是不需要解释的——它们是表中的实际数据,但任何没有用 text-in-row 选 项、varchar(max)或 varbinary(max)定义的 BLOB 数据除外。 256 第 9 章 SQL Server 存储和索引结构 ● 索引:索引页也是相当直观的——它们既包括非群集索引的非叶级页和叶级页(本章后 面将研究这些内容),又包括群集索引的非叶级页。通过本章的后续介绍,读者将更了 解这些索引类型。 2. 页拆分 当页已满时,它会进行拆分。这意味着多个新页被分配——也意味着现有页上有近半的数 据被移到新页上。 在使用群集索引时,该过程会有例外。如果有一个群集索引,并且下一个插入的记录在物 理上将作为表中的最后一个记录,那么创建一个新页,然后将该新行添加到这个新页中,而不 需要重新定位任何现有数据。在研究索引时将介绍有关页拆分的内容。 9.1.4 行 您可能听说过很多有关“行级锁定”的内容,因此对该术语应该不会感到陌生。行最大可 达 8KB。 除了 8 060 字符的限制外,还有最大 1 024 标准列(非稀疏列)的限制。实际上,您会发现在突破 8 060 字符限制前用完列的情况并不多见。最多 1 024 列提供了小于 8 字节的平均列宽度。对于大多 数使用情况,将会很容易超出这个平均值(因此会在超出 1 024 列之前超出 8 060 字符)。而例外情况 往往出现在度量和统计信息中——其中有大量存储了数值样本的不同数据内容。即使在那些应用程 序中,也很难突破 1 024 列的限制。如果突破了,则要运用稀疏列,9.1.5 节将介绍它。 9.1.5 稀疏列 稀疏列(sparse column)是 SQL Server 2008 中引入的一种特殊的数据结构。它们主要用于处 理重复的场景,其中的列只是偶尔会用到。也就是说,它们大部分时候为空。许多时候,一旦 遇到少量这类列,就意味着之后可能会遇到大量这类列。使用稀疏列,可以将单个表中允许的 列的总数提高到 30 000。 在内部,标记为稀疏填充的列的数据嵌在单个列中——可以打破之前的 1 024 列的限制,而不 用进行较大的体系结构更改。 image、text、ntext、geography、geometry、timestamp 和所有用户自定义数据类型不能被标 记为稀疏列。 注意:尽管较新版本的 SQL Native Client 会自行处理稀疏列,但其他形式的数据 访问在访问稀疏列时会有不同的行为。在按名称选择列时,列的 sparse 属性是透 明的,但如果通过在选择列表中使用“ *”进行选择,不同的客户端访问方法可能 会以一致的 XML 列形式提供稀疏列,或是根本不显示这些列。如果您遇到该问题, 那么可能希望尽可能快地更新客户端库。 注意:稀疏列属于高级主题范畴,但作者的本意是让读者知道有这样一个概念, 并且明白它对于特定场景是可行的解决方案。 257 SQL Server 2012 编程入门经典(第 4 版) 9.2 理解索引 韦氏字典中将索引定义为“通常按字母顺序排列的一些指定数据(如作者、主题或者关键字) 的列表(如目录信息或著作正文的引用)”。 下面将在数据库上下文中采用一种更简单的表述,即索引是一种能快速访问数据的方法。 尽管如此,韦伯斯特的定义其实也不错——甚至对数据库而言也适用。 韦伯斯特定义中的关键词是“通常”。而“字母顺序”这一定义会随许多规则而变化。例 如,在 SQL Server 中,有许多不同的可用排列规则选项。其中一些选项如下所示: ● 二进制:按字符的数字表示形式排序(例如,在 ASCII 码中,用数字 32 表示空格,用 68 表示字母“D”,而用 100 表示字母“d”)。因为所有内容都表示为数字,所以处理 起来速度最快。 但是,它并不总是如人们所想象,在 WHERE 子句中进行比较时,使 用该选项会造成严重的混乱。 ● 字典顺序:这种排序方式与在字典中看到的排序方式一样,但是稍有不同。可以设置大 量不同的额外选项来决定是否区分大小写、音调和字符集。 如果告诉 SQL Server 注意区分大小写,那么“A”和“a”将不相等,这一点很容易理解。 同样,如果告诉 SQL Server 不区分大小写,那么“A”和“a”将相等。如果添加区分音调的选 项,情况就变得有些混乱。如果 SQL Server 注意区分变音符号,那么“a”和“á”是不同的, 同时和“à”也不同。令许多人感到更加困惑的是:排列规则顺序如何既影响数据的相等性, 又影响排序顺序(从而影响它们在索引中的存储方式)。 通过示例,看一下表 9-1 中一些排列规则选项的性质,以及它们对排序顺序和相等性的 影响。 表 9-1 排列规则顺序对相等性和排序顺序的影响 排列规则顺序 比 较 值 索引存储顺序 字典顺序、不区分大小写、不区分音调 (默认) A = a = à = á = â = Ä = ä = Å = å a, A, à, â, á, Ä, ä, Å, å 字典顺序、不区分大小写、不区分音调、 大写字母优先 A = a = à = á = â = Ä = ä = Å = å A, a, à, â, á, Ä, ä, Å, å 字典顺序、区分大小写、区分音调 A≠a, Ä≠ä, Å≠å, a≠à≠á≠â≠ä≠å, A≠Ä≠Å A, a, à, á, â, Ä, ä, Å, å 这里的要点在于:索引中的存储顺序取决于为数据建立的排列规则信息。可以在数据库或 列级设置排列规则,因此有相当细粒度的控制级别。如果假定服务器是不区分大小写的,那么 需要确保系统文档能进行处理,或者有良好的电话技术支持计划——特别是如果您正在美国之外 进行销售。假设您是独立软件供应商(ISV),并且将产品销售给准备在现有的服务器上进行安装 的顾客(对于顾客而言,这似乎是完全合理的事情),但现有的服务器碰巧是已设置为区分大小 写的老式服务器。您将会接到不满意的顾客的电话,要求相关的技术支持。 258 第 9 章 SQL Server 存储和索引结构 注意:一旦设置了排列规则顺序,改变它是非常困难的(但也是可能的),因此在设 置之前要确定所需的排列规则顺序。 9.2.1 平衡树(B-树) 平衡树或 B-树的概念当然不是随 SQL Server 引入的。实际上,在数据库系统内外有非常 多的索引系统使用平衡树。 平衡树仅是提供了一种以一致且相对低成本的方式查找特定信息的方法。其名称中的“平衡” 是自说明的。平衡树是自平衡的(只有很少的例外情况),这意味着每次树进行分支时都有接近一 半的数据在一边,而另一半数据在另一边。而名称中的“树”的概念也是很清楚的——如果绘制 该结构,然后倒置它,会发现该结构具有树的一般形状,因此称为树。 1. 搜索 B-树 平衡树始于根节点(这是另一个类似于树的方面,但还不止这些)。如果有少量的数据,这 个根节点可以直接指向数据的实际位置。在这种情况下,最终获得的结构如图 9-1 所示。 根 实际数据 图 9-1 因此,从根节点开始并浏览记录,直到找到以小于查找值的值开始的最后一页。然后获得 指向该节点的一个指针并且浏览它,直到找到想要的行。 然而,在大多数情况下,有很多通过根节点引用的数据,因此根节点指向中间的节点(或称 为非叶级节点)。非叶级节点是位于根节点和说明数据的物理存储位置的节点之间的节点。非叶 级节点可以指向其他非叶级节点或指向叶级节点。叶级节点是从中可获取对实际物理数据的引 用的节点。如同叶子是导航树的线路的末端一样,叶级节点是导航索引的线路的末端——从那 里,可以直接到达具有其上部数据的实际数据节点。 从图 9-2 中可以看出,也是开始于根节点,然后移动到以等于或小于查找值的最高值开始 的同时也在下一级中的节点。然后重复这个过程——查找具有等于或者小于查找值的最高起始 259 SQL Server 2012 编程入门经典(第 4 版) 值的节点。继续沿着树一级级往下,直到到达叶级——从中知道数据的物理位置,并且可以很 快地定位数据。 根 非叶级 叶级 图 9-2 2. 更新 B-树:页拆分简介 所有这些页在读取方面工作良好——但在插入时会有点棘手。回顾一下,“B-树”中的“B” 表示平衡(Balanced)。而且前面提到每次遇到树中的分支时,因为每一边都大约有一半的数据,所 以 B-树是平衡的。另外,由于添加新数据到树上的方法一般可避免出现不平衡,所以 B-树有时被 认为是自平衡的。 通过将数据添加到树上,节点最终将变满,并且将需要拆分。因为在 SQL Server 中一个节 点相当于一个页,所以这称为页拆分,如图 9-3 所示。 插入新记录,但是页面已满 因为新记录需要在中间, 所以页面必须拆分 在群集键中作为中间记录有序插入 图 9-3 260 第 9 章 SQL Server 存储和索引结构 注意:当发生页拆分时,数据自动地四处移动以保持平衡。数据的前半部分保留 在旧页上,而数据的剩余部分添加到新页——这样就形成对半拆分,使得树保持 平衡。 如果稍微考虑下这个拆分过程,将认识到它在拆分时增加了大量系统开销。不只是插入一 页,而是进行下列操作: ● 创建新页 ● 将行从现有页移动到新页上 ● 将新行添加到其中一页上 ● 在父节点中添加另一个记录项 但是,系统开销远不止这些。因为在进行树的排列,就可能有级联操作。创建新页时(因为 拆分的缘故),需要在父节点中建立另一个记录项。在父节点中的这个记录项在该级别也可能导 致页拆分,而且整个过程会重新开始。实际上,这种可能性扩展到所有节点,甚至会影响根节点。 如果根节点拆分,那么最终实际会创建两个额外的页。由于只能有一个根节点,所以之前 作为根节点的页被拆分成两页,而且成为树的新中间级别。然后创建全新的根节点,并且将有 两个记录项(一个指向旧的根节点,另一个指向拆分的页)。 显然,页拆分会对系统性能产生非常负面的影响,其表现是在服务器上的处理会暂停几秒 (此时页被拆分并改写)。 接下来将讨论一下如何防止页拆分。 注意:虽然叶级的页拆分是很常见的,但是在中间节点进行页拆分却很少发生。 当表增长时,索引的每一层将进行页拆分,但是,因为中间节点对于下一级节点 上的几个记录项只有一个记录项,所以当向树的上层移动时,页拆分的数量变得 越来越少。尽管如此,对于发生在叶级以上的拆分来说,在下一个较低级别上一 定已经有一个拆分——这意味着沿树而上的页拆分在本质上是累积的(而且严重 影响性能)。 虽然 SQL Server 有许多不同类型的索引(稍后将讨论),但是所有这些索引都以某种方式利 用这种平衡树方法。事实上,由于平衡树的灵活特性,所有索引在结构上非常类似。不过,它 们实际上还有一些重要的区别,并且这些区别会对系统的性能产生影响。 注意:对于 SQL Server 索引而言,树的节点以页的形式出现,但是实际上可以将 根节点、非叶级节点、叶级节点以及树结构的概念应用于 SQL Server 或数据库之 外的其他系统。 9.2.2 SQL Server 中的数据访问方式 从广义上讲,SQL Server 检索所需数据的方法只有两种: ● 使用表扫描 261 SQL Server 2012 编程入门经典(第 4 版) ● 使用索引 SQL Server 使用何种方法来执行特定查询将取决于可用的索引、所需的列、使用的连接以 及表的大小。 1. 使用表扫描 表扫描是相当直观的过程。当执行表扫描时,SQL Server 从表的物理起点处开始,浏览表 中的每一行。当发现和查询条件匹配的行时,就在结果集中包含它们。 您可能听说过有关表扫描的缺点,基本上这些情况是属实的。但是,有时表扫描实际上是 最快的访问方法。一般来说,当从相对较小的表中检索数据时,使用表扫描访问方法就非常好。 这时,表的确切大小将随表的宽度和查询的特性而有很大变化。 注意:您知道在查询的 WHERE 子句中使用 EXISTS(如果确实使用得当的话 )可以 大大提高性能的原因吗?当使用 EXISTS 运算符时,只要找到和条件匹配的记录, SQL Server 就会立即停止。如果有一个包含一百万条记录的表,并且在第三个记 录中找到了匹配的记录,那么使用 EXISTS 选项将避免读取 999 997 条记录!NOT EXISTS 以同样的方式工作。 2. 使用索引 当 SQL Server 采用索引时,该过程实际上与表扫描的工作方式相类似,但是有一些捷径。 在查询优化过程中,优化器查看所有可用的索引结构并选择最好的一个索引(这主要基于在 连接和 WHERE 子句中所指定的信息,以及 SQL Server 在索引结构中保存的统计信息)。一旦 选择了索引,SQL Server 将在树结构中导航至与条件匹配的数据位置,并且只提取它所需要的 记录。区别在于,因为数据是排序的,所以查询引擎知道它何时到达正在查找的当前范围的下 界。然后它可以结束查询,或者根据需要移至下一数据范围。 如果考虑一下迄今已经涉及的查询主题(特别是第 7 章),就可以注意到 EXISTS 选项的工 作方式有一些明显的类似之处。EXISTS 关键字允许查询在找到一个匹配记录时立刻停止运行。 使用索引所获得的性能与使用 EXISTS 类似甚至更好,因为数据查找过程的工作方式是类似的; 也就是说,服务器可能使用某种索引知道何时没有相关的内容,并且立即停止。更好的情况是, 通过使用索引,不必限于采用布尔逻辑的情况。可以将这种相同的概念应用到范围的开始和末 尾。可以收集一定范围内的数据,其优点本质上与使用索引查找数据的优点相同。此外,可以 对数据执行非常快速的查找(称为 SEEK),而不是在整个表中查找。 注意:不要因为前面对索引与 EXISTS 运算符所进行的比较就认为索引可完全取 代 EXISTS 运算符 (反之亦然 )。这两者不是相互排斥的,它们可以一起使用,并且 经常可以这样做。在这里同时提到它们,仅仅是因为它们有一些相似性,即能够 显示其工作何时完成,以及在到达表的物理末尾之前退出。 262 第 9 章 SQL Server 存储和索引结构 3. 索引类型和索引导航 尽管表面上在 SQL Server 中有两种索引结构(群集索引和非群集索引),但是实际上,就内 部而言,有 3 种不同的索引类型: ● 群集索引 ● 非群集索引,该索引又包括以下两种: z 堆上的非群集索引 z 群集索引上的非群集索引 物理数据的存储方式在群集索引和非群集索引中是不同的。而 SQL Server 遍历平衡树以到 达末端数据的方式在所有 3 种索引类型中也是不同的。 所有的 SQL Server 索引都有叶级和非叶级页。正如讨论平衡树时所提到的那样,叶级是保 存标识记录的“键”的级别,非叶级页是叶级的引导者。 索引在群集表(如果表有群集索引)或者堆(用于没有群集索引的表)上创建。 ● 群集表:群集表是在其上具有群集索引的任意表。后面将详细讨论群集索引,但是它们 对于表而言意味着以指定顺序物理存储数据。通过使用群集键唯一地标识独立的行—— 群 集键即定义群集索引的列。 注意:您可能会有这样一个疑问:“如果群集索引不是唯一的,那么将怎样?”也 就是说,如果索引不是唯一索引,那么群集索引如何用于唯一地标识一行?一切 都在内部处理:SQL Server 迫使任何群集索引为唯一的——即使您没有以这样的 方式定义。幸运的是,它并没有改变使用索引的方式。如果愿意的话,仍然可以 插入重复的行,但是 SQL Server 将在内部添加一个后缀到键上,以保证行具有唯 一的标识符。 ● 堆:堆是在其上没有群集索引的任意表。在这种情况下,基于行的区段、页以及行偏 移量(偏移页顶部的位置)的组合创建唯一的标识符,或者称为行 ID(Row ID,RID)。如 果没有可用的群集键(没有群集索引),那么 RID 是唯一必要的内容。 4. 群集索引 群集索引对于任意给定的表而言是唯一的——每个表只能有一个群集索引。不一定非要有 群集索引,但是如果查看索引类型,您会发现由于多种很显然的原因,它正成为最常被选用的 一种类型。 使群集索引变得特殊的方面是,群集索引的叶级是实际的数据——也就是说,数据重新排 序,按照和索引排序条件声明的相同物理顺序存储。这意味着,一旦到达索引的叶级,就到达 了数据。任何新记录都根据其正确的物理顺序插入到群集索引中。创建新页的方式随需要插入 记录的位置而变化。 如果新记录需要插入到索引结构中间,就会发生正常的页拆分。来自旧页的后一半记录被 移到新页,并且在适当的时候,将新记录插入到新页或旧页。 263 SQL Server 2012 编程入门经典(第 4 版) 如果新记录在逻辑上位于索引结构末端,那么创建新页,但是只将新记录添加到新页,如 图 9-4 所示。 插入新记录但页面已满。由于它 是最新插入的,因此将它添加到 全新页面,而不打乱现有数据 在群集键中作为最后的记录有序插入 图 9-4 5. 导航树 正如前面所指出的那样,在 SQL Server 中甚至索引也存储在平衡树中。在理论上,平衡树在 作为树分支的每个可能方向上总是具有一半的剩余信息。群集索引的平衡树形式如图 9-5 所示。 查找 158~400 之间的记录 根 非叶级 页级是 数据页面 图 9-5 正如所看到的那样,它看起来和本章前面讨论的较为一般的平衡树是基本相同的。在图 9-5 中,执行对数字 158~400 的范围查询(群集索引特别擅长的事情)。SQL 需要做的全部工作是导 航到第一个记录,并且包含在该页上的所有剩余的记录。之所以 SQL 知道需要该页的剩余部分, 是因为来自于上一级节点的信息会通知 SQL:它也需要来自于一些其他页的数据。因为这是有 264 第 9 章 SQL Server 存储和索引结构 序列表,所以可以确信它是连续的——这意味着如果下一页有应当包含的记录,那么这个页的 剩余部分必须被包含。不需要任何验证就可从那些页上提取数据。 因为所有搜索都是从根节点开始,所以首先导航到根节点。SQL Server 能够基于在 sys.indexes 系统元数据视图中保存的记录项定位根节点。 通过查找作为根节点的页,可以知道下一个需要检查的页是什么(即这里的第二个级别的第 二页)。然后继续这个过程。沿着树一步步往下,就到达越来越小的数据子集。最终,将到达索 引的叶级。对于群集索引来说,到达索引的叶级意味着 SQL Server 也到达了期望的行和期望的 数据。 注意:下列区别非常重要:使用群集索引,当完全导航至索引时,也意味着完全 导航至数据。而在使用非群集索引时,其产生的性能差别才真正显示出来 —特 别是当非群集索引建立在群集索引之上时。 6. 堆上的非群集索引 堆上的非群集索引和群集索引在大多数方面以类似的方式工作。尽管如此,它们具有一些 显著的差别,下面就对此进行讨论。 叶级不是数据——相反,它是一个可从中获得指向该数据的指针的级别。该指针以 RID 的 形式出现,如同在本章前面描述的那样,这种 RID 由索引指向的特定行的区段、页以及行偏移量 构成。即使叶级不是实际的数据(相反,它具有 RID),使用叶级也仅仅比使用群集索引多一个步 骤。因为 RID 具有行的位置的全部信息,所以可以通过 RID 直接到达数据。 然而,不要误以为“这个额外步骤”只有少量的系统开销,并且堆上的非群集索引将和 群集索引几乎一样快地运行。使用群集索引,数据在物理上是按照索引的顺序排列的。这意 味着,对于一定范围的数据,当找到在其上具有数据范围起点的行时,那么很可能有其他行 在同一页上(也就是说,因为它们存储在一起,所以在物理上已几乎到达下一个记录)。使用 堆,数据并未通过除索引外的其他方法链接在一起。从物理上看,绝对没有任意种类的排序。 这意味着,从物理读取的角度看,系统可能不得不从整个文件中检索记录。实际上,很可能 最终多次从同样的页中取出数据。SQL Server 没有方法知道它需要回到该物理位置,因为在 数据之间没有链接。使用群集索引,它知道这是物理上的排序,因此仅仅通过访问页一次就 完全获得数据。 注意:堆上的非群集索引和群集索引一样,通常任何已读取一次的页将仍然在内 存缓存中,而且同样被非常快地检索。此外,它添加了一些额外的逻辑操作来检 索数据。 图 9-6 显示了与使用群集索引执行的同样的查询,只是这次使用在堆上的非群集索引的方式。 265 SQL Server 2012 编程入门经典(第 4 版) 图 9-6 主要通过索引导航,但一切都按以前的方式工作。服务器从相同的根节点开始,并且遍历 树,处理越来越聚焦的页,直到到达索引的叶级。这里就有了区别。采用群集索引的方式,能 够正好在这里停止,而采用非群集索引的方式则需要做更多的工作。如果非群集索引是在堆上 的,那么只要再进入一个级别。获得来自叶级页的 RID,并且定位到该 RID——直到这时才可 以直接获得实际的数据。 7. 群集表上的非群集索引 使用群集表上的非群集索引时,还有一些类似性——但同样也有区别。和堆上的非群集索 引一样,索引的非叶级节点的工作与使用群集索引时相比几乎一样。区别出现在叶级。 在叶级,与使用其他两种索引结构所看到的内容有相当明显的区别:有另外一个索引来查 找。使用群集索引,当服务器到达叶级时,它可以找到实际的数据。使用堆上的非群集索引, 不能找到实际的数据,但是可以找到能够直接获得数据的标识符(仅仅多了一步)。使用群集表 上的非群集索引,可以找到群集键。也就是说,服务器找到足够的信息来利用群集索引。 最终结果如图 9-7 所示。 最终是两种完全不同的查找。 为什么服务器需要这个额外的步骤呢?请记住,群集索引定义了磁盘上数据行的实际物理 排序顺序。如果更新一行以使它的群集键改变,则该行必须移动到正确排序它的某一页。如果 在非群集索引中存储 RID(类似于在堆上执行的操作),该行的 RID 就会改变,并且必须更新每 个非群集索引。存储群集键可确保无论将该行移至何处,仍然可以通过索引定位它。 在图 9-7 中,服务器首先执行范围搜索,这一点与前面相同。在索引中执行一次单独的查 找,并且可以浏览非群集索引以找到满足条件(LIKE ‘T%’)的连续数据范围。这种能够直接到达 索引中的特定位置的查找称为 seek。 266 第 9 章 SQL Server 存储和索引结构 图 9-7 注意:需要重复这一点以引起特别关注。当搜索条件非常符合一个索引,从而索 引可以直接定位至数据中的特定位置时,这种查找就称为 seek。请牢记这个单词。 索引 seek 可以表明您的索引被适当地使用。 然后开始第二个查找——使用群集索引的查找。第二种查找非常迅速;问题在于它必须执 行多次。可以看到,SQL Server 从第一个索引查找中检索列表(所有名称以“T”开始的列表), 但是该列表在逻辑上并没有以任意连续的方式与群集键相匹配——每个记录需要单独地查找, 如图 9-8 所示。 267 SQL Server 2012 编程入门经典(第 4 版) 图 9-8 自然,这种多个查找的情况比一开始仅能使用群集索引引入了更多的系统开销。第一个索 引查找——通过非群集索引的方法——只需要非常少的逻辑读操作。 例如,如果有一个每行 1000 字节的表,而且执行了类似的查找(比如将要返回 5 行或 6 行 的内容);它将仅以 8~10 个逻辑读的顺序获取来自非群集索引的信息。然而,这仅使得我们可 以在群集索引中查找行。这些查找每个都将花费 3~4 个逻辑读,或者 15~24 个额外的读取。 乍看起来,这可能并不是大问题。 268 第 9 章 SQL Server 存储和索引结构 但是,如果逻辑读从最小值 3 上升到最大值 24,这意味着需要完成的工作量以 800%的速 度增长。 如果扩展一下思维——即来自非群集索引的值范围不是 5 或 6 行,而是 5000 或者 6000 行, 或者 500 000 或 600 000 行——那么这将带来巨大的影响。 注意:不要被与群集索引相比较的额外系统开销所吓倒——这并不是说不使用索 引,而是指出从读操作的角度看,非群集索引通常不如群集索引有效(有时在插入 时,它实际上是更好的选择)。索引通常(有一些例外)都是执行查找的最快方法。 本章后面将介绍使用何种索引以及为何这样选的原因。 9.3 创建、修改和删除索引 创建、修改和删除索引的操作和对其他对象(例如表)的操作一样。接下来就来看一下每个 DDL 语句,首先从 CREATE 开始。 可以采用以下两种方式创建索引: ● 通过显式的 CREATE INDEX 命令 ● 在创建约束时作为隐式对象 因为这两种方式都有一些可做什么和不可做什么的特性,所以要分别进行讨论。 9.3.1 CREATE INDEX 语句 CREATE INDEX 语句所做的事情与其听上去一样——用于在指定表或视图上基于声明的 列创建索引。 创建索引的语法如下所示,并且引入一些到目前为止还没有真正讨论的项: CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX ON
( [ASC|DESC] [,...n]) INCLUDE ( [, ...n] [WHERE ]) [WITH [PAD_INDEX = { ON | OFF }] [[,] FILLFACTOR = ] [[,] IGNORE_DUP_KEY = { ON | OFF }] [[,] DROP_EXISTING = { ON | OFF }] [[,] STATISTICS_NORECOMPUTE = { ON | OFF }] [[,] SORT_IN_TEMPDB = { ON | OFF }] [[,] ONLINE = { ON | OFF } [[,] ALLOW_ROW_LOCKS = { ON | OFF } [[,] ALLOW_PAGE_LOCKS = { ON | OFF } [[,] MAXDOP = [[,] DATA_COMPRESSION = { NONE | ROW | PAGE} ] [ON { | | DEFAULT }] 269 SQL Server 2012 编程入门经典(第 4 版) 注意:这里的许多选项都有遗留语法,因此可以使用这些语法支持以前版本的 SQL Server。但是,并不赞成使用该语法,并且它们也将在某个时候被删除——这里强 烈推荐尽可能使用较新的语法。 注意:对于创建XML索引而言,有一个类似但完全不同的语法。本节最后将 单独介绍它。 不严格地说,这条语句遵循前面已多次看到的相同的 CREATE 语法(而且将更多地看到这种语法)。主要问题是有一些在其他地方还没有看到过的干扰参数。 正如在第 15 章中将要看到的关于触发器的内容,必须在 CREATE 语句中添加额外的子句 以处理索引实际上不是孤立的对象的事实。CREATE INDEX 语句必须随表或者视图出现,并且 需要声明列所在(ON)的表。 在 ON
()子句之后,所有内容都是可选择的。可以混合 搭配这些选项。其中大多数选项很少使用,但是一些选项(例如 FILLFACTOR)会对系统性能和 行为产生明显的影响,所以逐个讨论它们。 1. ASC/DESC 这两个选项允许为索引选择升序和降序排列顺序。默认选项为 ASC,它是升序。 您可能会产生如下的疑问,为什么升序和降序对索引有关系呢——如果需要相反的排列顺 序,SQL Server 只需要反向查看索引即可。但是,生活并不总是如此简单。如果只处理一列,或 者所有列总以同样的方式排序,那么以反向顺序查看索引是可行的——但是如果需要在索引中混 合排序顺序,该怎么办呢?也就是说,如果需要一列按照升序排序,但是其他列按照降序排列, 这该如何处理?因为索引的列存储在一起,所以对一列反向查看索引也将倒转其他列的顺序。如 果显式地声明某一列是升序,而另一列是降序,那么将直接在物理数据中倒转第二列——突然间 就不必改变数据的访问方式了。 举一个简单的示例。设想一个生成报表的场景,在其中需要将雇员列表按照雇佣日期排序, 从最近雇佣的开始(降序),但是也想按照他们的姓排列(升序)。在不能使用索引的版本中,SQL Server 将不得不执行两个操作——一个用于第一列,另一个用于第二列。通过允许控制数据的 物理排列顺序,在组合列上获得了灵活性。 2. INCLUDE 这是 SQL Server 2005 及后续版本支持的选项。它的目的是为覆盖索引(covering indexes)提 供更好的支持。 当包含(INCLUDE)列而不是将列放在 ON 列表上时,SQL Server 仅仅在索引的叶级上添加 它们。因为在索引叶级上的每一行对应于一个数据行,所以所做的事情在本质上是将更多的原 始数据包含在索引的叶级上。如果对此作一下考虑,或许会猜测 INCLUDE 实际上仅应用于非 群集索引(群集索引已经是叶级的数据,所以这样做无意义)。 270 第 9 章 SQL Server 存储和索引结构 这样做有何关系?正如本书后面进一步讨论的那样,SQL Server 一旦有了它实际需要的内 容,就停止工作。所以,如果在遍历索引时没有继续访问实际的数据行就找到需要的所有数据, 就不必再到达数据行(考虑一下这样做的意义)。通过在索引中包含特定的列,可以在叶级“覆 盖”利用该特定索引的查询,从而节省了与使用索引指针到达数据页相关的 I/O。 注意:注意不要滥用该选项!当包含列时,将增加索引页的叶级的大小。这意味 着每页中的行数将更少,因此需要更多的 I/O 来查看相同数量的行。结果是,加 快一个查询的同时可能减慢了其他的查询。要考虑对系统各个部分的影响,而不 是仅仅考虑某个时候正在使用的特定查询。 3. WHERE WHERE 是 SQL Server 2008 中新增的可选子句,用于设置在索引中包含哪些行的条件。相 关的语法与 SELECT 语句中熟悉的 WHERE 子句的语法相同,但是带有一些限制。在本章后面 讨论过滤索引时将详细介绍该子句。 4. WITH WITH 非常简单——它只是告诉 SQL Server 将要提供一个或者多个跟在后面的选项。 5. PAD_INDEX 在语法列表中,该选项第一个出现——但是在理解了 PAD_INDEX 所做的工作后,会觉得 该选项有点奇怪。简而言之,该选项决定了第一次创建索引时索引的非叶级页将有多满(用百分 比表示)。不用在 PAD_INDEX 中声明百分比,因为它将使用后面的 FILLFACTOR 选项中指定 的百分比。设置不带有 FILLFACTOR 选项的 PAD_INDEX=ON 将是没有意义的(这就是先出现 该选项会有点奇怪的原因)。 6. FILLFACTOR 当 SQL Server 第一次创建索引时,默认情况下尽可能地将页填满,仅留两个记录的空间。 可以将 FILLTACTOR 设置为 1~100 之间的任意值。一旦索引构造完成,这个数字将表示页相 对满的程度的百分比。但是,要牢记在进行页拆分时,数据将仍然在两页之间对半分布——除 了定期重建索引(这是您应当做的事情)外,不能不断地控制填充百分比。 当需要调整页密度的时候,使用 FILLFACTOR。可以作如下几方面考虑: ● 如果是 OLTP 系统,那么需要较低的 FILLFACTOR,从而在不进行页拆分的情况下执 行更多的更新。 ● 如果是 OLAP 或者其他非常稳定的(就改变而言,这是指几乎没有添加和删除操作)系 统,那么需要尽可能高的 FILLFACTOR 以减少必须从磁盘中读取的页数。 ● 如果事务比例中等,且有很多基于它的报表类型查询,那么可能需要中等水平的 FILLFACTOR(不太低,也不太高)。 如果没有提供值,那么 SQL Server 将把页填充至差两行满为止,同时保证每页最少有一行 271 SQL Server 2012 编程入门经典(第 4 版) (例如,如果行是 8000 字符宽,那么每页只能放一行,所以无法达到差两行满)。 7. IGNORE_DUP_KEY IGNORE_DUP_KEY 选项几乎是一种回避系统的方法。简而言之,它使得唯一约束与其应 有的操作方式有些不同。 通常,唯一约束(或唯一索引)不允许任何种类的重复——如果事务尝试基于定义为唯一的 列创建重复值,那么事务将被回滚并拒绝。然而,一旦设置了 IGNORE_DUP_KEY 选项,就将 得到混合的行为。仍然接收错误消息,但是错误将仅仅是一种警告——记录仍然没有被插入。 从 IGNORE_DUP_KEY 的角度看,上面最后一行提到的“记录仍然没有被插入”是一个重 要概念。不会对该事务进行回滚(错误是警告错误,而不是关键错误),但重复的行将被拒绝。 为什么要这样做?这是在不干扰试图插入重复值的事务的情况下存储唯一值的方法。对于 任何正在插入将要重复的值的过程来说,重复行完全没有问题(没有来自于它的逻辑错误)。相 反,该过程可能的态度是:“只要知道那里有类似的行存在就行了—— 不关心它是否是尝试插入 的特定行”。 8. DROP_EXISTING 如果指定 DROP_EXISTING 选项,那么具有所讨论名称的任何现有索引将在构造新索引之 前被删除。当该选项和群集索引一起使用时,这个选项比简单删除并重新创建现有的索引更加 有效。如果重新构建与现有索引完全匹配的索引,那么 SQL Server 知道它不需要涉及非群集索 引,然而为了适应不同的行位置,显式删除和创建操作将导致重新构建所有非群集索引两次。 如果使用 DROP_EXISTING 改变索引的结构,那么 NCI 只被重新构建一次,而不是两次。此 外,不能简单地删除和重新创建由约束所创建的索引,例如,用于实现特定的 FILLFACTOR。 而 DROP_EXISTING 可用于完成这一工作。 9. STATISTICS_NORECOMPUTE 默认情况下,SQL Server 试图自动化在表和索引上更新统计信息的过程。通过选择 STATISTICS_NORECOMPUTE 选项,表明将负责更新统计信息。为了关闭这个选项,需要运 行 UPDATE STATISTICS 命令,但不使用 NORECOMPUTE 选项。 这里强烈建议不要使用这个选项。原因何在?查询优化器使用索引上的统计信息来指出索 引对于给定查询有多大用处。随着表中数据大量增多或减少,以及列中的特定值改变,索引上 的统计信息会不断变化。结合这两个事实,可以知道不更新统计信息意味着查询优化器将基于 过时的信息运行查询。打开自动更新统计信息功能意味着统计信息将周期性地更新(多长时间更 新一次取决于对表更新的本质和频繁程度)。相反,关闭自动更新统计信息功能意味着信息会过 时,或者需要设定时间表以手动运行 UPDATE STATISTICS 命令。 10. SORT_IN_TEMPDB 只有在 tempdb 存储在与包含新索引的数据库物理上分离的驱动器上时,该选项才有意义。 该选项在很大程度上是管理功能,因此我们并不打算过多地介绍这个主题,而只是简要概述它 是什么以及为什么它只有在 tempdb 位于独立的物理设备上时才有意义。 272 第 9 章 SQL Server 存储和索引结构 当 SQL Server 建立索引时,它必须执行多个读操作以处理各种索引构造步骤: (1) 遍历所有的数据。服务器构造对应于实际数据的每一行的叶行。类似于实际数据和最 后的索引,这些内容进入用于临时存储的页。这些中间页不是最终的索引页,而是在每次排序 缓冲区已满时临时存储的位置。 (2) 通过这些中间页单独运行,以将它们合并到索引的最终叶级页中。 (3) 当填充叶级页时,构建非叶级页。 如果没有使用 SORT_IN_TEMPDB 选项,那么中间页将被写入在其中存储数据库的相同物 理文件中。这意味着实际数据的读操作必须与构建过程的写操作竞争。这两种情况造成磁头需 要移动到一个不同的位置(读与写)。结果是磁头经常来回移动——这会花费时间。 另一方面,如果使用 SORT_IN_TEMPDB,那么中间页将被写入 tempdb 中,而不是写入数 据库自己的文件。如果它们在单独的物理驱动器上,这意味着在索引构建的读和写操作之间没 有竞争。但是要牢记,只有在 tempdb 位于与数据库文件分离的独立物理驱动器上,这才会有 效;否则,只是名称上发生改变,而 I/O 的竞争仍然是个问题。 注意:如果要使用 SORT_IN_TEMPDB,那么确保在 tempdb 中有用于支持大 文件的足够空间。 11. ONLINE 如果将这个选项设置为 ON,那么它将强制表对于一般的访问保持有效,并且不创建任何阻 止用户使用索引和/或表的锁。默认情况下,全索引操作将获得所需的锁(最终得到表锁),以便 对表进行完全和有效的访问。然而,副作用是这将会阻止用户(这是矛盾的;一方面可能正在建 立索引以使数据库更为有用,但是同时又使表变得不可用)。 现在,您可能会想:“如果时时这样设置,那么用户就不会受影响,这似乎是个不错的主 意。”实际上这是非常糟糕的想法。要记住,任何类似的索引构造都可能是 I/O 密集的操作,因 此它会或多或少地影响用户。而且在构建索引时为了不妨碍任何用户,需要许多额外的系统开 销。如果在构建索引时能让 SQL Server 自由控制表,那么将能更快地构建索引,而且由于构建 而影响系统的总时间将更少。 注意:只有 SQL Server 的企业版支持 ONLINE 索引操作。在其他的版本中,也能 够执行带有 ONLINE 指令的索引命令,但是它将被忽略。因此在使用较低版本的 SQL Server 时,如果使用 ONLINE 并且发现用户仍然被索引操作阻止在外,不要 对此感到惊讶。 12. ALLOW ROW/PAGE LOCKS 这是一个比 ONLINE 更长期的指令,而且也是非常高级的主题。出于本书的目的以及到目 前为止对锁的讨论,这里只作一个相当简单的解释。 到现在为止,本书不断地使用“锁”这个术语。正如前面所解释的那样,这是某种避免数 273 SQL Server 2012 编程入门经典(第 4 版) 据完整性冲突的占位符。这里的 ALLOW 设置用于确定索引是否允许行锁或页锁。这可以归入 极端性能调整的主题。 13. MAXDOP 该选项用于为构建索引重写关于最大并行度的系统设置。因为并行不在本书的讨论范围, 所以这里将简要介绍它。 简而言之,并行度是指将有多少进程用于一个数据库操作(在这种情况下,该操作是索引构 造)。有一个称为最大并行度的系统设置,允许限制每个操作中的处理器数。索引创建选项中 的 MAXDOP 选项允许将并行度设置为高于或者低于基本系统设置,只要合适就行。 14. DATA COMPRESSION 从 2008 版本开始,SQL Server 支持在表或索引中压缩数据。虽然压缩详情被隐藏,但其 效果是在磁盘上更少的空间内存储数据,而系统开销是压缩和解压缩数据所需的 CPU 周期。 因为磁盘访问是数据库性能的瓶颈,所以这可能是一个不错的想法。 尽管如此,该选项的默认值是 NONE,关于这一点有很好的理由:只有 SQL Server 企业版 支持数据压缩。如果您正在使用标准版或免费版,则不能使用数据压缩功能。 其他两个选项是是否按照行(ROW)或页(PAGE)压缩。具体如何选择取决于更新与扫描的百 分比,这一点非常重要。如何选择超出了本书的介绍范围;如果您希望使用该选项(并且您的具 体实现采用了 SQL Server 企业版),那么可以在线研究该主题,或者查阅更高级的书籍,例如 Adam Jorgensen 编写的 Professional Microsoft SQL Server 2012 Administration。 15. ON SQL Server 允许通过使用 ON 选项将数据和索引单独存放。从以下观点来看,这是很不错 的方法: ● 索引需要的空间可以分散到其他的驱动器中。 ● 用于索引操作的 I/O 不会加重物理数据检索的负担。 还有更多与之相关的内容,但是都涉及非常高级的主题。这些内容与数据和使用关系密切, 所以不在本书的讨论范围内。 9.3.2 创建 XML 索引 XML 索引是 SQL Server 2005 中的新增功能。对类似 XML 的非结构化的内容进行索引是 许多人尝试解决的问题,但是几乎没有人真正成功过,而如今却有了这样一个优秀的存储 XML 数据的工具,我们应该感到庆幸。接下来就讲述关于 XML 索引的所有内容。 这又是一个“先有鸡还是先有蛋”的问题,因为到目前为止在本书中还没有真正讨论过 XML。尽管如此,还是更多地将其看作索引主题,而不是 XML 主题。实际上,除了 WHERE、 IGNORE_DUP_KEY 和 ONLINE 外,XML 索引创建语法支持在前面的 CREATE 语句中所看到 的所有相同选项。 与迄今为止所看到的关系型数据不同,XML 往往是相对非结构化的数据。它利用标记来标 识数据,并且可以与模式关联,给基于 XML 的数据提供类型和验证信息。XML 的非结构化特 274 第 9 章 SQL Server 存储和索引结构 性需要“导航”或者“路径”信息的概念,以在 XML 文档中查找数据“节点”。在另一方面, 索引尝试提供数据的特定结构和顺序—这在一定程度上有冲突。 在 SQL Server 中,可以在类型为 XML 的列上创建索引。这样做的主要要求如下: ● 在包含需要索引的 XML 的表上必须具有群集索引。 ● 在创建“辅助”索引之前(稍后将有更多介绍),必须先在 XML 数据列上创建“主”XML 索引。 ● XML 索引只能在 XML 类型的列上创建(而且 XML 索引是可以在该类型的列上创建的 唯一一种索引)。 ● XML 列必须是基表的一部分—不能在视图上创建索引。 1. 主 XML 索引 在 XML 索引上创建的第一个索引必须声明为“主”索引。当创建主索引时,SQL Server 创建一个新的群集索引,这个群集索引将基表的群集索引和来自任何指定的 XML 节点的数据 组合在一起。 2. 辅助 XML 索引 这里没有任何特别之处——非常类似于指向群集索引的群集键的非群集索引,辅助 XML 索引以相似的方法指向主 XML 索引。一旦创建了主 XML 索引,就能在 XML 列上创建多达 248 个以上的 XML 索引。 9.3.3 随约束创建的隐含索引 这里称这个索引为“意外的索引”。这并不是说索引不应当在那里—如果需要创建索引 的约束,那么它肯定会在那里。只是我们已经多次看见这种糟糕的情况:系统中只有索引是以 这种方式创建的。通常,这说明了系统管理员和/或设计人员几乎忘记了索引的概念。 然而,您还会在这方面看到另一奇怪现象—管理员或设计人员知道如何创建索引,但并 不真正知道如何说明系统中有什么索引,以及它们的作用是什么。重复的索引就是这种情况的 典型代表。只要索引有不同的名称,SQL Server 就会非常乐于为您创建这些索引。 当向表中添加如下两种约束之一时,就会创建隐含索引: ● 主键约束 ● 唯一约束(也称为替换键) 到目前为止,因为我们已经学习了大量的 CREATE 语法,所以就不再赘述——但要注意, 当创建一个索引作为约束的隐含索引时,除{CLUSTERED|NONCLUSTERED}和 FILLFACTOR 外,所有选项都不允许使用。 9.3.4 在稀疏列和地理空间列上创建索引 毫无疑问,这些内容已超出入门书籍的范畴了,但事实是可在稀疏列和地理空间类型数据 上创建特殊索引。因此,如果要应用这些特殊需求类型的列,就需要将此谨记在心。 275 SQL Server 2012 编程入门经典(第 4 版) 9.4 明智地选择——在何时何地使用何种索引 到现在为止,您可能在想:“我将始终选择创建群集索引!”这样想有很多很好的理由。但 要记住,也有一些理由使得您不一定这么做。 选择包含何种索引以及不包含何种索引是一个困难的过程,而且,如果这样做还不够,就 必须对需要的索引类型做一些决定。由于只能有一个群集索引,后一种决策既较容易又较困难。 这意味着必须明智地选择以最有效地使用它。 9.4.1 选择性 索引,特别是非群集索引,主要在其中有相当高级别的选择性的情况下是有益的。所谓选 择性,指的是列中唯一值的百分比。列中唯一值的百分比越高,选择性就越高,从而索引的益 处就越大。 如果回顾关于非群集索引的章节—特别是关于非群集索引和群集索引比较的章节— 那么您将回忆起在非群集索引中的查询实际上只是开始。要找到真正的数据,仍需要对群集索 引再执行一次循环遍历。甚至使用堆上的非群集索引时,仍然需要最终执行多个单独的物理读 操作。 如果非群集索引中的一个查找将要在群集索引上产生多个额外的查找,那么进行表扫描可 能更好。这里可能产生的影响实际是非常惊人的。如果被索引列中的唯一性达不到 90%~95%, 那么就不值得考虑由非群集索引创建的循环过程。 这对群集索引几乎没有影响,因为一旦处于数据范围的起点处—唯一的或非唯一的—就 已经到达目的地。不需要再读取额外的索引页。另外,群集索引很可能拥有其他事物,可以有 更大用处。 注意:选择性规则的一个例外与外键有关。如果表中有一列是外键,那么在该列 上有一个索引很可能是有益的。为什么是外键而不是其他列?外键常常是与它们 引用的表连接的目标。不管选择性如何,索引在连接性能方面是非常有帮助的, 因为它们允许合并连接。合并连接从每个表中获取一行并比较,查看它们是否和 连接条件(连接的依据 )匹配。因为两个表中的相关列上存在索引,所以对两个行的 查找是非常快的。 这里的要点是选择性不是一切,但它是要考虑的重要问题。如果讨论的列不 是外键,那么在必须考虑的问题方面,选择性问题几乎仅次于像“多长时间使用 一次?”这样的问题。 试一试 查看使用哪个索引 在这个练习中,您将选择 AdventureWorks 数据库中某个最大的表,并且学习如何选择一个 或多个索引放入该表中。为了进行选择,一个关键的步骤是检查 SQL Server 是否实际地使用您 创建的索引。很可能本章的内容会让您认为应该凭借直觉创建有用的索引,但是我多年来从未 276 第 9 章 SQL Server 存储和索引结构 利用假设创建索引。记住,检查工作是无可取代的。 Production.TransactionHistory 表有超过 113 000 行,该表足够大,使得索引查找可以得到任 何查询所需的结果,因此它是您将使用的目标表。您将在该表中查找 ID 范围,跨越两个列选 择该范围:ReferenceOrderID 和 ReferenceOrderLineID。因为该表已经存在,所以它有跨越这两 个列的索引。 下面是两个查询,它们代表从表中检索数据所需的不同方式。第一个查询查找一定范围的 ReferenceOrderID 和特定的 ReferenceOrderLineID,而第二个查询执行相反的查找。 SELECT * FROM [Production].[TransactionHistory] WHERE ReferenceOrderLineID = 0 AND ReferenceOrderID BETWEEN 41500 AND 42000; SELECT * FROM [Production].[TransactionHistory] WHERE ReferenceOrderLineID BETWEEN 0 AND 2 AND ReferenceOrderID = 41599; 代码段 Chap09.sql 使用指向 AdventureWorks 的 SSMS,通过选择“查询”菜单中的“显示估计的查询计划” 选项,查看每个查询的估计执行计划。这些计划在一定程度上如图 9-9 所示。 图 9-9 两个查询计划之间有一些细微的区别,但它们都使用名为 IX_TransactionHistory_ ReferenceOrderID_ReferenceOrderLineID 的现有索引的索引查找。顾名思义,这个非群集索引 按照顺序对 ReferenceOrderID 和 ReferenceOrderLineID 进行索引。 接下来禁用该索引并按相反的顺序创建一个新的索引,然后再次检查查询计划。首先,您 可以按照如下所示禁用现有的索引: ALTER INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistory] DISABLE; 完成之后,创建新的测试索引。首先将该索引命名为 IX2,虽然这不是一种良好的命名约定, 但是通过它可以方便地在查询计划窗口中查看是否使用了该索引。不一定要对索引进行编号。 277 SQL Server 2012 编程入门经典(第 4 版) CREATE NONCLUSTERED INDEX [IX2_TransactionHistory_ReferenceOrderLineID_ReferenceOrderID] ON [Production].[TransactionHistory] ( [ReferenceOrderLineID] ASC, [ReferenceOrderID] ASC ); 代码段 Chap09.sql 可以看到,索引名称和索引定义中的列顺序已经颠倒。现在重新检查查询示例上的查询计 划,查看它们是否将使用这个新的索引。图 9-10 显示了新的查询计划,它与前一个查询计划非 常相似。 图 9-10 很明显的是,使用新的索引进行查找。这当然是好消息,但还是需要看看查询计划中的其 他代码: Missing Index (Impact 98.4691): CREATE NONCLUSTERED INDEX [] ON [Production].[TransactionHistory] ([ReferenceOrderID],[ReferenceOrderLineID]) SQL Server 希望您创建索引以实现更好的性能,并且该索引非常类似于刚才禁用的索引。 可以确信的是,适当地使用该索引,可以将运行至少一个这些查询的速度提高 98%。因此,在 使用新的 IX2 索引时,很难达到最优状态。使用 ALTER INDEX…REBUILD 命令重新启用原始 索引,并且通过适当地同时使用两个索引来最后一次运行查询计划。 ALTER INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistory] REBUILD; 代码段 Chap09.sql 在这段代码中发生了什么?第一个查询继续使用新的 IX2 索引,而第二个查询使用原始的 (现在重新构建的)索引。 在继续介绍之前,使用 DROP INDEX 删除测试索引(将 AdventureWorks 还原到原始状态)。 278 第 9 章 SQL Server 存储和索引结构 示例说明 在该示例中需要注意一些事项。 ● 对于两个查询来说,原始的索引都是可接受的。这是因为其中一个查询完全按照正确 的顺序执行。对于另一个查询来说,原始索引可接受的原因在于,ReferenceOrderID 的 选择性使得顺序变得不那么重要。 ● 相反顺序的索引用于两个查询。这是因为该索引的执行性能要好于表扫描,但是它远 没有达到最优状态,没有为一个查询产生缺失索引提示。 ● 缺失索引提示尽管在两个查询计划中重复出现,但是仅应用于一个查询。建议一次仅 使用一个索引,并且对于每个查询重复使用该索引。这仅是一种特性。 ● 性能并没有改变。对于选择使用相反顺序索引的查询,使用该索引和原始索引之间的 性能差异很小,因此没有必要为了实现这种很小的性能改进而增加索引维护成本(更新 成本)。 SQL Server 使用现有的索引,但是您需要明智地选择索引。此处的正确选择是尽量仅使用 原始索引,因为它可以很好地为两个查询提供服务。最重要的是,此处得到的经验是应该对预 期的工作量全面地测试索引用法。 9.4.2 注意成本:少即是多 记住,虽然索引在读取数据方面可提高性能,但是在修改数据时,它们实际上花费很高。 索引没有通过魔法来维护。每次对数据进行修改时,任何与该数据相关的索引也需要更新。可 以将执行更新时花费的时间认为是投资的金钱。每次通过索引读取数据时,您可以将节省的时 间视为投资回报;但是对于没有使用的每个索引,投资就没有任何回报。 当插入新行时,必须将新的条目放入表上的每一个索引中。另外也要记住,当更新行时, 是将该操作作为删除和插入进行处理——索引也必须被更新。但还有更多内容!当删除记录时, 必须再次更新所有索引——而不只是数据。对于创建的每个索引,意味着创建了一组必须更新 的条目。 顺便说一句,此处提及的条目使用复数形式—不只是一个。记住,平衡树有多个级别。 每次对叶级进行修改时,就可能发生页拆分,而且也必须修改一个或者多个非叶级页,以便引 用正确的叶级页。 有时—实际上是经常—要做的事情是不创建额外的索引。有时候,最好是基于对系统 而言至关重要的事务选择索引,并且使用讨论中的表。事务的代码中是否有 WHERE 子句?它 使用了什么列?需要进行排序吗? 9.4.3 选择群集索引 记住,只可以有一个群集索引,所以需要明智地选择它。 默认情况下,主键是和群集索引一起创建的。这通常是个不错的选择,但并不总是如此(实 际上,在有些情况下,这会带来严重的危害),并且如果这样做,那么将不能在其他任何地方使 用群集索引。这里的要点在于不要接受默认方式。在定义主键时要考虑一下——确实想要它作 为群集索引吗? 279 SQL Server 2012 编程入门经典(第 4 版) 如果确实要改变—也就是说,不想声明为群集索引,那么在创建表时只需要添加 NONCLUSTERED 关键字。例如: CREATE TABLE MyTableKeyExample ( Column1 int IDENTITY PRIMARY KEY NONCLUSTERED, Column2 int ) 代码段 Chap09.sql 一旦创建了索引,改变它的唯一方法是删除和重建它,所以需要一开始就做对。 要记住,如果改变了群集索引所在的列,那么 SQL Server 将需要对整个表完全重新排序(记 住,对于群集索引,表的排列顺序和索引顺序是相同的)。现在,考虑一个 5000 字符宽的表, 而且该表有几百万行——这是必须重新排序的非常多的数据。根据这种情况,应该会想到如下 一些问题: ● 它将要花费多长时间?可能是很长的时间,而且实际上没有好的方法来估计这种时间。 ● 是否有足够的空间?为了在群集索引上执行重新排序,额外需要的平均空间量将为表 已经占用的空间量的 1.2 倍(工作空间加上新的索引)。如果在处理大型的表,那么这可 能变成非常巨大的空间量—确保有足够的空间来操作。顺便说一句,所有这些活动 将在数据库中发生—因此这也会受到为数据库设置的最大尺寸和增长选项的影响。 ● 应当使用 SORT_IN_TEMPDB 选项吗?如果 tempdb 位于与主数据库不同的物理阵列 上,并且它有足够的空间,那么答案很可能是肯定的。 1. 正面观点 如果讨论的列常作为范围查询的对象,那么群集索引对于这类查询是很有用的。这类查询 通常使用 BETWEEN 语句或者符号。使用 GROUP BY 以及利用 MAX、MIN 和 COUNT 聚合函数的查询也是使用范围和偏好群集索引的查询的重要示例。群集索引适用于此处,这是 因为搜索可以直接到达物理数据中的特定点,可一直读数据,直到到达范围的末端,然后停止。 这种方法非常有效。 当想要数据基于群集键排序(使用 ORDER BY)时,群集也是极好的方法。 2. 反面观点 但在两种情况下,您可能不想创建群集索引。第一种情况很明显——当有更好的位置来使 用它时。听上去这有点重复,但是不要因为列看上去适合就将它用作群集索引(主键是常见的罪 魁祸首)——要确定没有更适合的其他列。 尽管如此,更加不适合使用群集索引的另一种情况是:在将要以非连续的顺序进行大量插 入时。还记得页拆分的概念吗?这里会进行页拆分,并且会消耗大量时间。 假设这样的场景:您正在创建一个记账系统。想要在事务文件中将事务号作为主键,但是 也希望事务号在一定程度上能够指示事务类型(它实际上有助于会计人员排除故障)。因此,您 280 第 9 章 SQL Server 存储和索引结构 想出了一种方案——在所有事务上添加前缀,表明它们来自什么子系统。它们类似于如下所示: ARXXXXXX Accounts Receivable Transactions GLXXXXXX General Ledger Transactions APXXXXXX Accounts Payable Transactions 其中,XXXXXX 是连续的数值。 这看起来像是一个好主意,所以实现它,并且让主键继续作为群集索引。 最初看起来,这一设置非常好。您将会有唯一的值,而且会计也因为能够根据事务号来推断 事务来自哪里而感到高兴。因为群集索引常用于查询事务 ID 的范围,所以其看起来是有意义的。 如果真是那么简单就好了。稍微考虑一下插入操作。使用群集索引时,最初有一个好的机 制来避免大量页拆分的系统开销。当插入一条新记录时,该记录将跟在表中最后一个记录后面, 即使发生页拆分,也只有该记录被移至新页——SQL Server 不必尝试移动任何旧的数据。不过, 现在已陷入麻烦中。 从总分类账(General Ledger)插入的新记录将接续在文件的末端(按照字母排列顺序,GL 在 后面,而且号码是连续的)。但是 AR 和 AP 事务有大问题——它们将执行非连续的插入。当 AP000025 插入且在页上没有空间的时候,SQL Server 将在表中看到 AR000001,而且知道它不 是连续的插入。在插入 AP000025 之前,来自旧页的一半记录将被复制到新页。 这样做的系统开销可能会令人感到惊讶。要记住正在处理的是群集索引,而群集索引是数 据。数据是以索引顺序排列的。这意味着,当把索引移动到新页时,也在移动数据。现在假设 正在典型的 OLTP 环境下运行这个记账系统(记账系统最具 OLTP 特征),其中有许多数据录入 人员正尽可能快地输入供应商发票或者客户订单。系统中将会经常发生页拆分,而且每次进行 页拆分时,该表的用户将在系统移动数据时经历短暂停顿。 幸运的是,有一些方法可避免出现这种情形: ● 选择在插入时是连续的群集键。可以为此创建一个标识列,或者也可以使用另一个列, 该列对于任何输入的事务来说(不管来自哪个系统)在逻辑上都是连续的。 ● 选择不在这个表上使用群集索引。对于类似这里的情形来说,这通常是最好的选择, 因为在堆上非群集索引中的插入一般比在群集键上的插入更快。 注意:尽管前面提到要倾向于使用连续的群集键以避免页拆分,但也要认识到这 会有开销。连续的群集键的一个不利方面是并发性 (两个或者更多的人试图同时访 问同一对象 )。因此,我们所要做的是权衡想要干什么、正在做什么,以及在其他 方面将要付出的代价。 这可能是为什么如此深入研究事情是如何进行的一个最佳示例。在明白是否使用了正确的 索引之前,需要考虑事情将如何完成。 9.4.4 列顺序问题 仅仅因为索引中有两个列,这不能说明索引对于任何引用其中一列的查询是有用的。 如果查询中使用了索引中列出的第一个列,那么可考虑使用该索引。好的方面是不必在每 281 SQL Server 2012 编程入门经典(第 4 版) 一列上一对一匹配——只需要第一个列匹配。当然,(按照顺序)匹配的列越多越好,但只需要 通过第一个列就可确定“不要使用”某索引。 可以这样考虑一下。假设在使用电话簿。所有项都按先姓后名的方式进行索引——如果只 知道要通电话的人的名是 Fred,那么这种排列顺序能带来任何好处吗?另一方面,如果只知道 他的姓是 Blake,那么索引将可以用来缩小查找范围。 索引构造过程中较为常见的一种错误是认为一个包含所有列的索引将对任何情况都是有 帮助的。实际上,这样做只是将所有数据又存储了一次。如果索引的第一个列没有在查询中的 JOIN、ORDER BY 或者 WHERE 子句中提及,那么索引将完全被忽略。 9.4.5 覆盖索引 到目前为止,您可能已经开始忘记非群集索引的概念。群集索引在性能方面确实好于表扫 描,并且您可以只有一个群集,但是群集查找(如果在表上没有群集,则是 RID 查找)的额外系 统开销也确实非常大。幸运的是,我们有其他的选择,但是与以往一样,这也会带来系统开销。 简单来说,覆盖索引包含查询所需要的所有数据。如同前面看到的那样,一旦 SQL Server 发现它需要的数据,就会停止查找。建立在索引基础上的几乎所有最终查找都采用这种处理方 式。如果只是仅需要在一个索引键中解析查询,或者需要将其包括在叶子中,就没有理由执行 这种查找。 最基础和最显而易见的覆盖索引是群集索引。表中的所有数据都在叶子中,因此群集覆盖 了所有查询。 更深层次的覆盖索引是可以仅使用它们的键值解析查询的索引。考虑外键列上的索引和对 表上的外键 ID 值使用 EXISTS 的查询。SQL 可以仅在索引中查找该 ID 值,一旦找到(或最终 都没有找到)就停止查找,返回 EXISTS 布尔值,而甚至不会查找关于该表的其他任何详情。 最后,您可以在索引的叶子中包括(INCLUDE)非键列。尽管这看起来可以解决群集键查找 的任何问题,但是它需要一定的开销。您的索引键保持相同的大小,因此查找速度依然很快, 但是叶子必须增大以容纳额外的数据。此外,每次插入、更新或删除数据时,必须抛弃更多的 位数以进行补偿。当然,对于必须快速执行的常见查询,这是极好的工具。 注意:我使用的一些 SQL Server 最佳思维方式有一点反常,它们看起来几乎就是 一种必要条件。如果您具有这种思维方式,那么可能已经设想在一个或多个非群 集索引中包括表中所有的列,并在相同的表上有效地创建多个群集索引。让我们 回到现实中。如果您没有谨慎处理,那么插入、更新、删除操作甚至从新索引读 取数据所产生的系统开销会严重影响系统性能。因此要尽量少使用索引。 9.4.6 过滤索引 到目前为止看到的所有索引有一个共同点:每个索引都针对表中的每一行在叶子中有一个 条目。然而,这并不是严格的必要条件,并且有时需要限制出现在索引中的行。 创建过滤索引只需要包括本章前面描述的 WHERE 子句。相应的语法非常简单,但是其用 282 第 9 章 SQL Server 存储和索引结构 途却很广泛。创建这种索引的原因何在? 让我们快速查看一下 AdventureWorks 中的 Sales.SalesOrderDetail 表的数据组成。特别地, 让我们了解一下特价商品的订单记数,如下所示。 SELECT SpecialOfferID, COUNT(*) OrderCount FROM Sales.SalesOrderDetail GROUP BY SpecialOfferID ORDER BY COUNT(*) DESC 可以看到,特价商品 1 到目前为止最畅销。当然,特价商品 1 是“没有特价的”商品—在搜 索特价商品相关信息时并不会找到该商品。如果您想要运行查询以按照产品检查特价商品,则 可以创建一个过滤索引,只包括实际应用特价的行。 CREATE INDEX ix_SalesOrderDetail_ProductID_filt_SpecialOffers ON Sales.SalesOrderDetail (ProductID) WHERE SpecialOfferID > 1; 代码段 Chap09.sql 您可以在运行包括兼容 WHERE 表达式的查询时使用该索引。关于过滤索引需要注意以下 几点: ● 索引深度远小于全表索引。您只是索引几千行,而不是索引超过十万行,因此遍历索 引的速度更快。 ● 因为索引只包含特价商品行,所以通过插入/更新/删除操作维护该索引的开销较低。改 变 SpecialOfferID 为 1 的行(有很多这样的行)完全不会影响该索引。 注意:过滤索引的一个相对常见的用途是有效地允许在可为 NULL 的列上设置唯 一约束。通过使用 WHERE IS NOT NULL 子句创建唯一索引,您可以 阻止重复的实际数据,并且仍然允许存在的 NULL 值。 9.4.7 修改索引 ALTER INDEX 命令在其用来做什么方面多少有些欺骗性。截至现在,ALTER 命令总是与 修改对象的定义有关。例如,ALTER(修改)表以添加或禁用约束和列。ALTER INDEX 是不同 的—该命令与维护有关,而与结构完全不相干。如果需要修改索引的组成,那么仍然需要 DROP(删除)然后 CREATE(创建)索引,或者用 DROP_EXISTING=ON 选项 CREATE(创建)并使 用索引。 正如在本章前面所看到的那样,SQL Server 提供了一个用于控制叶级页可填充到多满的选 项,并且如果选择使用,还有另一个选项用来处理非叶级页。但是,这些选项是主动型的选项。 它们只应用一次,然后在需要时,必须通过重建索引并重新应用这些选项来再应用它们。 在 9.5 节关于索引维护的内容中,您将了解到更多关于在何处以及为什么要使用这一命令的 内容,但现在只需要知道将使用像 ALTER INDEX 这样的维护命令作为常规维护程序的一部分。 ALTER INDEX 的语法类似于下面这样: 283 SQL Server 2012 编程入门经典(第 4 版) ALTER INDEX { | ALL } ON
{ REBUILD [ [ WITH ( [ PAD_INDEX = { ON | OFF } ] | [[,] FILLFACTOR = | [[,] SORT_IN_TEMPDB = { ON | OFF } ] | [[,] IGNORE_DUP_KEY = { ON | OFF } ] | [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ] | [[,] ONLINE = { ON | OFF } ] | [[,] ALLOW_ROW_LOCKS = { ON | OFF } ] | [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ] | [[,] MAXDOP = ) ] | [ PARTITION = [ WITH ( [ ,...n ] ) ] ] ] | DISABLE | REORGANIZE [ PARTITION = ] [ WITH ( LOB_COMPACTION = { ON | OFF } ) ] | SET ([ ALLOW_ROW_LOCKS= { ON | OFF } ] | [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ] | [[,] IGNORE_DUP_KEY = { ON | OFF } ] | [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ] ) } [ ; ] 其中一些选项与 CREATE INDEX 命令相同,因此这里将略过对这些选项的重新定义。除 此之外,相当多的 ALTER 特定选项都是细节性的,且与处理碎片之类的事情有关(稍后将介绍 碎片和维护),或是更为面向数据库管理员的,且通常用于处理非常特殊的问题。不过,这里的 核心元素都是常规维护计划的一部分。 下面将从几个顶级参数开始讲述,然后介绍更大的维护计划所需要的选项。 1. 索引名 如果想维护一个特定的索引,可以指定该索引,或者使用 ALL 表明想要维护与指定的表相关 联的所有索引。 2. 表名或视图名 从字面可知,这是想要在其上执行维护的特定对象(表或视图)的名称。注意,必须是一个 特定的表(可以给它提供一个列表,然后说“请处理所有这些!”)。 3. REBUILD 这是用来修复索引的“工业级强度”的方法。如果使用该选项运行 ALTER INDEX,那么 将完全丢弃旧的索引并重新生成新的索引。结果是真正优化的索引,其中所有叶级和非叶级的 284 第 9 章 SQL Server 存储和索引结构 页都按照定义进行了重新构建(使用默认值或者使用开关修改填充因子等)。如果讨论中的索引 是群集索引,那么也会重新组织物理数据。 默认情况下,页将被重新组织为差两行记录才满。和 CREATE TABLE 语法一样,可以将 FILLFACTOR 设置为 0~100 之间的任何值。该值是在数据库完成重新组织后页被填满的程度 (以百分比表示)。但要记住,在进行页拆分时,数据将对半分布在两个页上——除了定期重建 索引外,不能不断地控制填充的百分比。 注意:要小心使用该选项。一旦开始 REBUILD,在完成索引重建之前,正在使用 的索引实际就没有了。依赖该索引的所有查询可能会变得异常缓慢(可能会降几个 数量级)。对于这类事情,首先需要在离线系统上测试,以了解整个过程将花多少 时间。然后,计划在非高峰时段运行(最好有人监控,以确保它在高峰时段来临时 已恢复联机状态)。 重建过程可能有许多副作用,因此,重建索引应当由数据库管理员来处理。 4. DISABLE 该选项名副其实,只是方式有点过激。如果该命令的全部作用只是为了让索引离线,直至 您决定了进一步要做什么,则它是不错的选择,但它实际上会把索引标记为不可用。一旦禁 用了某个索引,在重新激活之前,必须重建索引(不是重新组织,而是重建)。ALTER INDEX… DISABLE 的相反操作并不是 ENABLE,这样的操作并不存在。您必须执行 ALTER INDEX… REBUILD。 您自己极少会使用该选项(更可能做的是删除索引)——它很可能在 SQL Server 升级过程中 或者其他一些古怪的情况下用到。此外,您可能记得在本章前面使用过该选项。该选项的用途 是,在测试期间,DISABLE 和 REBUILD 会保留索引定义,因此您不需要再次使用完整的原始 创建脚本。 注意:“如果对表禁用了群集索引,那么也会禁用表。数据仍会保留,但在重建群 集索引之前,不能被所有索引(因为它们都依赖于群集索引)访问。 5. REORGANIZE 从开发人员的角度来看,这一选项特别好。如果重新组织索引,就得到了比完全重建索引 稍逊一点的完全优化,但这种方法可以联机进行(用户仍能使用索引)。 如果仔细琢磨,那么上面的描述可能会让您想到一个问题——“稍逊一点”到底是指什么。 它其实是指,REORGANIZE 只在索引的叶级起作用,而不触及非叶级。这意味着未获得完全 优化。但是,对于大部分的索引而言,那不是真正产生碎片的地方(尽管可能会发生这种情况, 并且遇到的情况也不尽相同)。 由于该选项对用户的影响非常小,通常您会希望把该工具作为常规维护计划的一部分来使 用。后面讨论碎片时将更进一步讨论它。 285 SQL Server 2012 编程入门经典(第 4 版) 9.4.8 删除索引 如果正在不断地重新分析情况和添加索引,那么也不要忘记删除索引。记住插入索引需要 系统开销。由于在考虑需要的索引时并没有对插入的开销太在意,因此也没有考虑过哪些索引 是不需要的。总是要自问一下:“可以从中去掉哪些索引?” 删除索引的语法非常类似于删除表的语法。唯一的不同在于需要使用索引所附着的表或视 图来限定索引名称: DROP INDEX
. 或者: DROP INDEX ON
这样就可以删除索引。 9.4.9 从查询计划中获取提示 如果您按顺序阅读本章,那么会看到 SQL Server 2008 中提供的一个新功能:查询计划信 息中的索引提示,它们将给出查询优化器所认为有用的、但不存在的索引(一次给出一个索引)。 现实情况是,在创建建议的索引之后,您不需要严格地检查查询是否使用该索引;即使该索引 不会被任何其他的查询再次使用,它也会用于该查询。如果您在执行某个重要的查询时获得该 提示,则在大多数情况下需要采取该提示的建议。 注意:SQL Server 不会建议采用过滤索引,即使是过滤索引可以提供最佳的解决 方案时也是如此。然而,SQL Server 将建议采用覆盖索引。此处存在一个危险: 如果您在将要覆盖的相同键上有一个索引,但是该索引只针对一两个列,则该提 示会在可以进行替换时有效地建议采用新的索引。与往常一样,注意删除索引以 及创建索引。 9.4.10 索引未被使用的原因 这是很少被提出问题的领域(但是应该有很多问题可以提出来)。我经常看到开发人员编写 导致表扫描的查询,从而导致产生很差的性能,但是开发人员只是忽略该问题,因为他们认为 “该列上已经有索引。”因为该列上已经有索引,所以开发人员认为他们无法做其他工作,从 而无法避免这种很差的性能。 我最后一次重复声明:反复地测试!检查您的索引是否被使用。如果它们未被使用,则开 始查找原因——无序的 WHERE 子句、缺乏选择性、建议的索引或不可索引条件——并且修复 这些问题。 您在本章中已经学习了大多数相关内容,因此现在花费一点时间来介绍不可索引条件的概 念以及如何处理它们。 假设您需要定位 AdventureWorks 数据库中仍然在销售的产品——即存在值为 NULL 或今 286 第 9 章 SQL Server 存储和索引结构 天往后日期的 SellEndDate。对于“今天往后的日期”,尽管可能在后面涉及 Y10K 问题,但是 我将使用很遥远的将来日期(12/31/9999)。 SELECT ProductID, Name FROM Production.Product WHERE ISNULL(SellEndDate, '99991231') >= GETDATE() 代码段 Chap09.sql 为了使这个查询更好地执行,您可能认为需要索引 SellEndDate。但是,通过检查可以发现, 无论如何进行选择,也无论从查询实际返回多少行,该索引都不会被使用。为了了解原因,查 看该查询并注释使用的列以限制输出。 此处使用的不是 SellEndDate,而是 ISNULL(SellEndDate, '99991231'),其中完全没有用到 索引。当您过滤某个函数而不是列时,就不会用到索引。 遇到这种问题时,实际上只有很少的解决方法。您可以将该函数创建为持久的计算列并在 其上设置索引,但是这必须重写整个群集以容纳新的列;或者,您可以尝试查找某种方法来重 写查询,从而在不使用函数包装的情况下比较列。 9.4.11 使用数据库引擎调整向导 我希望您已经对索引有了足够的了解,而不需要再使用数据库引擎调整向导。不过,它仍 是非常方便的工具。它的工作方式是获取由 SQL Server Profiler(这并不是初学者应该学习的主 题,因此没有包括在本书中)生成的工作负载文件并对其信息进行考查,确定对系统最有利的索 引和/或分区。 数据库引擎调整向导位于 SQL Server Management Studio 的 Tools 菜单中。也可通过 Windows 的“开始”菜单上一个单独的程序项来访问它。和大多数其他调整工具一样,不推荐 将这个工具作为决定构建何种索引的唯一方法,但在提出一些您可能未考虑到的建议方面,它 还是很有用的。 9.5 维护索引 作为开发人员,在产品交付以后,经常容易忘记它。对于许多类型的软件来说,一般都可 以很好地逃脱责任——售出产品,然后进行下一个产品或者下一个版本的开发。然而,对于数 据库驱动的项目,实际上不可能逃避这种责任,而需要在交付日期后担负起让产品良好工作的 职责。 请不要以为这里的意思是您必须为技术支持部门分担工作——本节实际上在讨论更加重 要的事情:维护计划。 实际上,就索引的维护而言有以下两个问题需要处理: ● 页拆分 ● 碎片 287 SQL Server 2012 编程入门经典(第 4 版) 这两个问题都和页密度相关,虽然两者的表现形式在本质上有区别,但是故障排除工具是 一样的,因为处理也是相同的。 9.5.1 碎片 前面已经讨论了很多关于页拆分的内容,但是实际上没有涉及碎片。此处所谈论的不是您 可能听说过的操作系统文件的碎片以及所使用过的碎片整理工具,因为它们对数据库碎片没有 什么帮助。 当数据库增长而执行页拆分,然后最终删除数据时,就会产生碎片。虽然从增长的观点看, 平衡树机制在保持平衡方面做得还不错,但在删除数据时,它并没有太多作用。最终,可能出 现这样一种情况:在这一页上有一个记录,而在那一页上有几个记录——在这种情况中,许多 数据页上的数据量只是它们可以保存的总数据量的一小部分。 关于碎片首先会想到的第一个问题是——浪费的空间。前面提到过,SQL Server 每次分配 一个区段的空间。如果一个页上只有一条记录,则仍然会分配整个区段。 第二个问题是,散布在各处的记录会造成数据检索时的额外系统开销。为了获取需要的 10 行记录,SQL Server 不是只加载一页,而是可能必须加载 10 个不同的页来获取相同的信息。 并不只是读取行导致了这一结果——SQL Server 必须首先读取该页。更多的页意味着更多的读 取工作量。 虽说如此,数据库碎片也有它好的一面——OLTP 系统就喜欢碎片。原因是什么呢?页拆分。 没有许多数据的页在插入数据时几乎或完全不用担心页拆分。 所以,大量的碎片意味着较差的读取性能,但是它也意味着极好的插入性能。正如您所预 料的,这意味着 OLAP 系统实际不喜欢碎片。 9.5.2 确定碎片和页拆分的可能性 SQL Server 提供了一个特殊的元数据函数 sys.dm_db_index_physical_stats,它有助于确定数 据库中的页和区段有多满。然后可以使用该信息作出一些关于如何维护数据库的决策。 sys.dm_db_index_physical_stats 是表值函数(table valued function,第 13 章将详细介绍)。简 而言之,这意味着尽管它是函数,但也可像表一样使用它,这表示可在其上附加 WHERE 条件 和其他类似的构造。该函数的语法相当简单: sys.dm_db_index_physical_stats ( { | NULL | 0 | DEFAULT } , { | NULL | 0 | DEFAULT } , { | NULL | 0 | -1 | DEFAULT } , { | NULL | 0 | DEFAULT } , { | NULL | DEFAULT } ) 注意,它要求用相对非直观的 id 作为输入值,而不是过去常用的逻辑名。幸运的是,SQL Server 提供了大量可返回服务器上或数据库中特定对象的正确 id 的函数。 举例说明如何使用该函数,假定要从 Sales.SalesOrderDetail 表中获取所有索引信息。由于 288 第 9 章 SQL Server 存储和索引结构 要求获取表中的所有索引,因此只需要该表的 id——而不是任何单独的索引。由于这是表值函 数,需要编写一个查询从该函数返回结果。例如: DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks'); SET @object_id = OBJECT_ID(N'AdventureWorks.Sales.SalesOrderDetail'); SELECT database_id, object_id, index_id, index_depth, avg_fragmentation_in_percent,page_count FROM sys.dm_db_index_physical_stats(@db_id,@object_id,NULL,NULL,NULL); 代码段 Chap09.sql 相比于以前的工具,其输出是更加不用加以说明的: database_id object_id index_id index_depth avg_fragmentation_in_percent page_count --------- ---------- --------- ------- -- -------- -- -------------- 8 610101214 1 3 0.565885206143896 1237 8 610101214 2 3 1.70731707317073 410 8 610101214 3 2 6.14035087719298 228 (3 row(s) affected) 该函数还提供了大量其他信息,因此可根据兴趣进一步研究,但这里还是看一下与碎片有 关的列,如表 9-2 所示。 注意:有大量的字段说明了有关索引结构的类型(群集索引和非群集索引)、碎片页 的数量,以及其他感兴趣的信息。还有一些列的信息是有关高级 SQL Server 内容 的,因此如果不理解该函数提供的所有列,也不必有压力。 表 9-2 dm_db_index_physical_stats 输出 统 计 信 息 说 明 database id 这是对其运行函数的数据库的 SQL Server 内部标识符。NULL 值表示 会返回所有数据库的信息。记住,如果选择 NULL 选项,则所有后续 参数也必须设置为 NULL。对此参数而言,0 和 DEFAULT 在功能上等 同于 NULL object id 这是对其运行函数的表的 SQL Server 内部标识符。NULL 值表示会返 回所有表的信息。记住,如果选择 NULL 选项,则所有后续参数也必 须设置为 NULL。对此参数而言,0 和 DEFAULT 在功能上等同于 NULL index id 这是对其运行函数的索引的 SQL Server 内部标识符。NULL 值表示返 回所有索引的信息。注意,如果表是群集表,那么群集索引总是为 1; 如果表是堆,那么堆索引将为 0。非群集索引将为 2 或更大的数字。记 住,如果选择 NULL 选项,则 partition_no 也必须设置为 NULL。对此 参数而言,–1 和 DEFAULT 在功能上等同于 NULL 289 SQL Server 2012 编程入门经典(第 4 版) (续表) 统 计 信 息 说 明 partition no 这个信息有点超出本书的讨论范围,但表和索引可分区到单独的物理 存储单元。使用这一参数可标识想要获取特定分区的碎片信息。NULL 值表示将返回所有分区的信息。对此参数而言,1 和 DEFAULT 在功 能上等同于 NULL Mode 这定义了用于生成返回的统计信息的扫描级别。选项包括 DEFAULT、 NULL、LIMITED、SAMPLED 或 DETAILED。DEFAULT 和 NULL 在功能上等同于 LIMITED Logical Scan Fragmentation 通过扫描索引的叶级页检查到的无序页的百分比。它只与群集表上的 扫描有关。无序页是指在索引分配映射(index allocation map,IAM)中 指示的下一个页,该页不同于叶级页中的下一个页指针指向的页 Extent Scan Fragmentation 该信息表明一个区段在物理上的下一个区段是否为它在逻辑上的下一 个区段。这只意味着索引的叶级页在物理上不是有序的(虽然它们在逻 辑上仍然是有序的),并且表明了这一问题占区段的百分比 现在,问题是一旦得到该信息,该如何使用它呢?答案当然是看情况而定。 使用来自碎片查询的输出,就可正确认识到数据库是否已满、是否有碎片或者介于中间的某 个状态(后者更可能是希望看到的情况)。如果运行的是 OLAP 系统,那么会希望看到页已满—— 而碎片会令人沮丧。对于 OLTP 系统,仅就这一点而言,将希望有相反的结果。 所以,如何处理这个问题呢?要回答它,需要深入了解索引重建和填充因子 FILLFACTOR 的概念。 1. 使用 ALTER INDEX 重建索引 正如在本章前面所看到的那样,SQL Server 提供了一个控制叶级页有多满的选项,当然也 提供了另一个处理非叶级页的选项。但是,这些都是主动型的选择——它们只应用一次,然后 在需要时,必须通过重建或重新组织索引并重新应用这些选项来再应用它们。 为了重建索引,可以删除索引并再次创建它们(如果要这样做,使用 DROP_EXISTING 选 项通常是一个好主意),或是运用 ALTER INDEX 命令和 REBUILD 选项。不过要记住前面讨论 重建索引时所介绍的内容——除非可使用 ONLINE 选项,否则在重建完成前,重建索引会使该 索引(可能是整个表)完全离线。通常,重新组织是更好的选择。 重新组织只会影响索引的叶级(大部分问题很可能都在其中),并且在此过程中使索引保持 联机状态。重新组织索引可重新构造这些索引中的所有叶级信息,而且重建一个页填充度的基 本百分比。如果讨论的索引是群集索引,那么也会重新组织物理数据。但是,REORGANIZE 并不允许更改一些索引设置,如 FILLFACTOR。 2. 使用 FILLFACTOR 如果索引没有指定 FILLFACTOR,那么页将被重新构造为差两行填满。和 CREATE TABLE 290 第 9 章 SQL Server 存储和索引结构 语法一样,在执行完全重建时可以将 FILLFACTOR 设置为 0~100 之间的任意值(与重新组织相 对)。一旦数据库重新组织完成,这个数字将是页填充的百分比。但要记住,当进行页拆分时, 数据将仍然对半分布在两个页之间——除了定期重建索引外,不能不断地控制填充百分比。 当需要调整页密度时,使用 FILLFACTOR。正如前面所讨论过的,较低的页密度(因而具 有较低的 FILLFACTOR)对于有许多插入操作的 OLTP 系统而言是理想的——这有助于避免页 拆分。较高的页密度是 OLAP 系统所期望的(读取较少的页,但是由于很少或几乎没有插入,因 此实际上没有页拆分的风险)。 如果想重建之前的 Sales.SalesOrderDetail 表中作为主键的填充因子为 65 的索引,则可以采 用下列 ALTER INDEX 命令: ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail REBUILD WITH (FILLFACTOR = 100) 代码段 Chap09.sql 注意:现在可以偏离一下主题,讨论一下有关对象名的内容。可以注意到,这个 名称较长。字符越多,就越可能出现输入错误。对需要的名称作一下权衡是有意 义的。对于这个特定的查询,本书的命名约定不包括表名(当引用某个索引时,通 常已知道所指的表),同时省略下划线,使其最终形式为 PKSalesOrderIDSalesOrder DetailID。该名称指出了索引的性质(它支持主键)和涉及的列。不过,要慎用列名。 在极少数情况下,如果在索引中出现几个列,会导致不合规则的索引名。 然后可以重新运行 sys.dm_db_index_physical_stats 以查看其作用: database_id object_id index_id index_depth avg_fragmentation_in_percent page_count --------- ----------- --------- --------- -------------- --------- ----- 8 610101214 1 3 0.0810372771474878 1234 8 610101214 2 3 1.70731707317073 410 8 610101214 3 2 6.14035087719298 228 (3 row(s) affected) 这里要注意的最大问题是在 avg_fragmentation_in_percent 中的改变。假定我们正查看高页 密度(较强的可读性),那么希望此数字尽可能地低。该数字实际不会达到 0%,因为 SQL Server 必须处理页和行的大小调整问题,但会尽量接近该值。 关于 ALTER TABLE REINDEX/REORG 和 FILLFACTOR,需要注意下列几个要点。 ● 如果没有提供 FILLFACTOR,那么 ALTER TABLE 将使用以前建立索引时设置的填充 因子。如果从未指定过填充因子,那么 FILLFACTOR 会使得页填充至差两行满(这对大 多数情况而言都太满了)。 ● 如果提供了 FILLFACTOR,那么这会成为该索引的默认 FILLFACTOR。 291 SQL Server 2012 编程入门经典(第 4 版) ● 虽然 REORGANIZE 可以联机工作,REBUILD 也可以(如果有启用该功能所需的许可), 但本书强烈建议不要使用它——它会锁定资源并会引起许多问题。最多在非高峰时执 行它。 9.6 本章小结 在 SQL Server 或者任何其他数据库环境中,索引都是一个基本的主题,而且不可轻视。它 们有助于获得良好的性能,但也可能导致极差的性能。 关于索引要考虑的几个首要问题如下: ● 群集索引通常比非群集索引快(可以说前者总是比后者快,但是也有一些例外)。 ● 仅在将得到高级别选择性的列(也就是说,95%或者更多的行是唯一的)上放置非群集 索引。 ● 所有的数据操作语言(DML:INSERT、UPDATE、DELETE、SELECT)语句可以通过索 引获益,但是插入、删除和更新(记住,它们使用删除和插入方法)会因为索引而变慢。 索引有助于查询的查找过程,但是任何修改数据的行为将有额外的工作要做(除了实际 数据外,还要维护索引)。 ● 索引会占用空间。 ● 仅当索引中的第一列和查询相关时才使用索引。 ● 索引的负面影响和它的正面影响一样多——了解为什么建立索引,以及为什么只建立需 要的索引。 ● 索引可为非结构化 XML 数据提供结构化的数据性能,但是要记住,和其他索引一样, 这会涉及系统开销。 在考虑索引时,最好问自己一些问题,如表 9-3 所示。 表 9-3 考虑索引时需要问一些问题 问 题 回 答 会对这个表进行大量插入或者修 改吗 如果是,尽量少用索引。这种类型的表通常通过主键的 单个记录查找完成修改—这往往是该表上需要的唯 一索引。如果插入是非连续的,不考虑使用群集索引 这是一个报表吗?也就是说,这里没 有许多插入,但是会以许多不同的方 法运行报表吗 有更多索引是好的。将群集索引确定为频繁使用的、可能 会在范围内提取的信息。OLAP 系统中的索引数量通常是 OLTP 环境中所看到的许多倍 在数据上有高级别的选择性吗 如果是,而且它通常是 WHERE 子句的目标,那么添加 索引 已经删除不再需要的索引了吗 如果没有,为什么不删除 已建立了维护策略吗 如果没有,为什么不建立 292 第 9 章 SQL Server 存储和索引结构 练习题 1. 请说出至少两种确定在AdventureWorks数据库的HumanResources.Employee表中有何种 索引的方法。 2. 在 AdventureWorks 数据库的 Production.ProductModel 表的 ModifiedDate 列上创建一个 非群集索引。 3. 删除在练习 2 中创建的索引。 X 本章内容总结 主 题 概 念 数据库、文件、区段和页 SQL Server 以层次结构的方式组织它的文件。该结构的根是 数据库,数据库包含相应的文件。每个文件都分配在区段中, 而每个区段是 8 个 8KB 的页 页拆分 将新的数据写入页时,SQL Server 有时必须对半拆分页,将 一半数据写入新页中。如果新页需要分配新的空间,则必须 分配全新的区段,从而使页拆分的开销很大 索引 索引是基于索引键快速查找特定行或行范围的方式。搜索索 引的平衡树几乎总是比扫描整个表并测试每一行要快很多 群集/非群集索引 在群集索引中,在平衡树的叶级写入完整的数据行,从而按 照群集键指示的顺序有效地排序整个表。因此,每个表只可 以有一个群集索引。在非群集索引中,只将在 INCLUDE 子 句中指定的列写入行。如果查询需要额外的列,则通过存储 在索引叶子中的行标识符或群集键查找这些列 创建索引 创建和删除索引类似于创建和删除其他数据库对象。在稍有 不同的上下文中使用 ALTER,相比于改变结构,更多地将其 用于索引维护 索引策略 因为维护索引需要在插入、更新和删除方面产生系统开销, 因此在繁忙的环境中,仔细地选择索引就非常重要。通过仔 细地选择群集索引(如果有的话)以及创建一组有用的非群集 索引,可以极大地改进查询的性能。然而,过多的或选择不 当的索引会妨碍事务性活动,且不会提供重要的性能改善 索引维护 根据数据库的作用选择索引维护选项。在调整 FILLFACTOR 这样的参数之前需要仔细考虑,并且确保创建作业以定期 维护索引,从而获得最佳的性能 293
还剩109页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 18 人已下载

下载pdf

pdf贡献者

mnui98

贡献于2014-02-27

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!