sqlserver2005数据库开发详解

上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html1.2 大幅提升编程的能力_SQLServer2005 数据库开发详解1.2 大幅提升编程的能力在集成应用程序开发上，SQL Server 2005 最受人瞩目的当属集成 .NET CLR 引擎到 SQL Server 核心，让程序设计师可以通过自己所熟悉的 .NET 语言来开发 SQL Server 内的对象，扩展了程序编写的弹性。另外，支持 ANSI SQL 2003 与 W3C 的 XML 标准，让关系式和 XML 两种最常用的数据处理格式都可以集中到数据库引擎来处理。与 .NET Framework 的集成一致化数据库对象与中间层的程序编写模型。将 .NET 的 Common Language Runtime（CLR）直接集成到 SQL Server 2005 的数据库引擎中，让存储过程、用户自定义函数、触发器、用户自定义数据类型以及聚合函数可以通过 .NET 的语言，如 Visual Basic.NET 或 C# 等等来编写1，并且直接与 SQL Server 引擎执行在同一个程序中，以提升运行效率。以往要通过 C+ 编写扩展存储过程才能扩展 SQL Server 功能，但此种方式困难且容易犯错，往往造成编写的功能不如预期，反倒是增加了系统的不稳定性。 如今可以通过 .NET 语言轻易地办到扩展 SQL Server 的功能。例如通过 Regular Expression 验证数据格式正确与否，集成网络上或其他来源的数据一起提供给前端等等。应用系统的架构工程师现在有更大的想象空间，让系统分析员和程序设计员等人可以使用相通的分析设计技巧与工具，如面向对象分析设计、程序编写风格、.NET Framework 类型等来扩展 SQL Server 的功能。Visual Studio 2005 可以直接提供 SQL Server 2005 对象格式的项目程序开发实例，其内提供程序代码架构、部署设置以及集成调试的 Script 等。当你编写并编译完当作函数库（.dll 格式）的程序集（Assembly）后，可以通过 SQL 语句或是 Visual Studio 2005 提供的功能，直接将其部署到 SQL Server 中。SQL Server 会将该程序集存放在数据库内，而与外部文件系统上的程序集不再有关系。也就是说若你有新的版本，需要先将 SQL Server 内的程序集及其相关的对象删除，重新布署才可以更新。当程序集部署到 SQL Server 后，通过 Create function、Create Procedure 等 T-SQL 语句可以设置存储过程、用户自定义函数等五种对象是来自该程序集。而后就当作一般的 SQL 对象搭配 T-SQL 来使用。前端用户分不出来某个对象是由 T-SQL 还是 .NET 程序语言开发出来的。除了用户自定义数据类型和自定义聚合函数一定要以 .NET 才能编写外，其他的对象用.NET 和 T-SQL 都可以编写，则何时该用哪种语言架构呢？笔者以为面向数据的函数与程序依然应该采用 T-SQL，也就是说以集合的方式添加、修改、删除和查询数据还是 T-SQL 擅长的部分，若以一般程序语言如 C#/VB.NET 等来逐笔维护数据，反而发挥不了数据库引擎最佳化访问数据的能力。但若复杂的商业逻辑，如加/解密、网络访问、多线程、文件 I/O 等，需要一步步指引 CPU 如何做事，则适合采用 .NET 程序语言。对于复杂的运算， .NET 程序语言采用编译的方式，其编写程序逻辑与执行程序的方式都比解释式的 T-SQL 语言更好。上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html由于是数据库引擎内建 .NET 执行环境，因此若将所有的商业逻辑都搬进到 SQL Server 势必大幅增加服务器的负担，因此仍要慎选程序逻辑的执行位置。一般来说若应用与数据适合结合在一起，例如节省网络来回的负荷，数据与运算需要统一管理等，才将程序集成到服务器中。在你细心评估后，若觉得放在数据库引擎是最佳的设计，而后才把组件放入到数据库也行，应用的巧妙存乎一心。另外，在编写数据库核心程序需要格外的严谨，毕竟以往若前端程序写得不好，在意外状况发生时只会造成几个人的系统有问题，但若在数据库服务器上执行不稳定，或是有安全疑虑，将会拖累到全部数据库服务器的用户。尤其在大型数据库服务器上，可能还有其他系统同时执行，到时会连累到许多其他系统的用户，因此程序开发者和数据库管理员不可不慎。对 XML 的支持现今应用程序在交换数据或存储设置时，大多采用 XML 格式。在以往我们可能将 XML 数据以文件的方式存放在硬盘目录结构上，然后把该文件相关的管理信息放入到关系式数据库内。在操作 XML 数据时，一方面通过 SQL 查询语句在数据库内找到相关的 XML 文件，一方面用程序语言调用 DOM（Document Object Model）对象来访问解析 XML。当编写这类程序时，开发人员要同时学习 T-SQL 和 XPath 语言，ADO.NET 或其他数据库访问对象以及 DOM 对象类型。若 XML 文件数据很大，由于没有索引结构，用 DOM 解析的效率往往不佳。同时，备份数据既要备份数据库内的 metadata，又要备份文件系统上的 XML，而且安全控制也更加复杂。根据 ANSI SQL 2003 标准的要求，未来关系数据库皆可当作 XML 格式的数据源与集中储存的地方。SQL Server 2005 遵循并扩展该标准，让存放数据的模型可以扩展到 XML 数据格式。在数据定义方面新增 XML 数据类型（每条记录最长 2 GB） ，你可以用来定义数据字段或是该类型的变量。该类型除了要求符合 XML Well form 的定义外，并可通过 XML Schema 验证 XML 数据的正确性。并通过扩充 XPath 语言而形成的 XQuery 查询语言来查询和修改 XML 数据。以 XML View 将原有的关系式数据转成 XML 层次结构的查看方式，并以专门的 XML 索引格式提升查询效率。以 SQL Server 2005 当作 XML 的存储区可以让两种类型的数据有一致的管理机制，提供相同的备份、还原、安全控制等规格。可以用相同的管理工具做到上述的工作，以此简化系统的架构设计。程序设计师可以通过集成在 T-SQL 内的 XQuery 语句，混合关系式数据字段与 XML 数据一起显示与维护，在前端统一通过 ADO.NET 2.0 访问，让应用程序的开发技术单一且完整。增强 Transact-SQLT-SQL 语言一直都是使用 SQL Server 服务的基础，若你说你不熟悉 T-SQL，那我就不太相信你真的会 SQL Server。新版 SQL Server 更大幅增强了该语言的功能，由于 SQL Server 引擎新增了非常多的对象与功能，例如支持 .NET、XML、提供 Web Services、通过 Broker Services 建立信息导向的数据处理平台、利用 DDL 触发器或 Event Notification 监控、增加认证（Certificate）和加密机制等等。而要与 SQL Server 沟通时，主要的语言又是 T-SQL，自然需在 SQL Server 2005 中加入大量的 T-SQL 标记，以定义或访问上述的新增功能。同时 T-SQL 语言本身也增强了数据处理的能力，例如取得记录结果排名函数，有 上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.htmlRANK、 DENSE_RANK、ROW_NUMBER 和 NTILE 等。支持递归数据访问的 Common Table Expression（CTE） 、结构化错误处理（TRYCATCH） 、增加新的运算符如 PIVOT、UNPIVOT，让你可以编写类似 Excel 分析的汇总运算。另外，通过 APPLY 可以将数据表函数（ Table Value Function TVF）传回的多条记录与查询结果相乘展开，也就是你可以在 SELECT 语句中将每一条记录的某个或某些字段值当作参数传给 APPLY 运算符之后衔接的 TVF 函数，TVF 通过该参数取得的多条记录传回后，配上 SELECT 语句原先该条记录所选择的字段，形成多个字段乘积的结果。另外，SQL Server 也让 T-SQL 的语句更一致化，以往许多要借助系统存储过程、DBCC 等命令才能完成的工作，现在也能改以 CREATE、ALTER、DROP 等标准语句来完成。例如以往通过 sp_addlogin、sp_adduser 等系统存储过程来创建登录账号和数据库用户，现今都可以通过标准的 CREATE LOGIN 和 CREATE USER 语句来创建，而修改和删除亦是如此。以往通过 DBCC 指令来整理索引也可改成用 ALTER INDEX 语句来完成。上述的种种说明学习 SQL Server 服务各项功能以及 T-SQL 语句时更一致，而通过 T-SQL 语句可以更简单，更有效率地完成复杂的数据处理工作。不会让你在完成不同功能时，要熟悉风格完全不同的 DBCC 指令或是存储过程。新的数据类型SQL Server 2005 新增了 nvarchar（max ） 、varchar（max） 和 varbinary（max）等数据类型，以取代之前版本的 ntext、text 和 image，让你在处理大型文字或二进制数据时更有弹性和效率。另外，还可以通过 .NET 编写自定义数据类型。同时新支持的 XML 数据类型也让你将 XML 的数据可以统筹在数据库引擎一并管理和使用。ADO.NET 2.0SQL Server 2005 对前端应用程序的设计与开发提供了新的功能，但这些功能需要利用新的 ADO.NET 2.0 才能访问，而这些新功能可能会改变你以往设计应用程序的架构。例如新的数据类型、新的 snapshot 事务级别、新的提升系统稳定性的 Mirroring 功能等，都需要 ADO.NET 2.0 来访问使用。另外，有几个设计架构上的差异，如异步地访问 SQL Server、 Multiple Active Result Sets 可让你重复使用对数据库的连接；在有兴趣的数据被更新时，可以收到来自 SQL Server 2005 的主动触发等。最后两点须要搭配以下服务器端功能的描述。主动通知SQL Server 2005 在用户访问的数据被他人更改时，可以主动地通知前端应用程序。我们以往设计应用程序将数据缓存在前端时，必须要定时轮询（Polling）数据库，以同步数据库内最新的数据。但往往需要调整轮询的频率，以平衡过多的访问造成性能损耗，太久不更新又让数据严重过时。而 SQL Server 2005 在所使用数据更改时，会主动通知你，这时你考虑的方面会改成 SQL Server 通知多少次后，或更改后可以延长多少时间再去同步数据，依然是避免过多访问造成的系统负荷。但现在的设计模式会让你在系统数据未更新时，减轻浪费网络来回轮询的负担。上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html多数据结果集（Multiple Active Result Sets MARS）此功能提供在同一条连接上可以同时维护多个游标结构，以往 ADO.NET 一条连接同时只能访问一个默认结果集游标结构，也就是利用 ADO.NET 的 SqlDataReader 读取数据，若没有关闭，不可以在同一条连接中另外通过查询再开启一个结果集，因此若你要同时查询不同的语句，需要开启多条连接。现在取消了这个限制， SQL Server 2005 一条连接可以维护多个结果集，因此在设计前端应用程序时，可以更节省连接所耗的服务器资源。安全数据库安全包括设计、默认、安装和维护、通信和储存等基本方面。在设计安全性方面，此版 SQL Server 制定的登录账户支持 Windows 安全性原则（Policy） 。用户账户和对象命名的结构描述分开，让数据库对象不再绑在某个用户上，解决“用户离开公司”问题，同时让数据库管理员可以允许安装某个套装软件时，通过结构描述来分门别类各数据对象。而元数据的查看现在也搭配安全管理权限，让用户只能查看可以访问的对象。另外，所有的权限都可以通过 T-SQL 的 GRANT、REVOKE、DENY 赋予或禁止，这也称为 Granular permissions。在默认安全性部分，新版默认可以利用 SQL Server 访问外部资源（如 OpenRowset 系统函数、 xp_cmdshell 系统存储过程、启动 .NET CLR 等） ，或外部可以使用 SQL Server 的管道（如 HTTP 访问）关闭。同时新增 “SQL Server Surface 界面区配置”工具程序，让你可以通过统一的界面来打开或关闭这些功能。安全的安装和维护、通信和储存等方面提供数据加密，程序可以特定身份执行。新版 SQL Server 提供管理密码钥匙的层次架构，用来加密数据的钥匙可以安全地保存在数据库内，用数据库钥匙加密一般的钥匙，再用 SQL Server 实例的钥匙加密数据库钥匙，最后实例的钥匙通过 Windows 操作系统的 DPAPI 保护。另外，登录账号永远是被加密的。而存储过程或用户自定义函数可以特定身份执行（run as） ，以符合最小权限准则，不必普遍赋予用户账号各种权利，只有通过特定的程序或函数转换身份后，才能完成需要特殊权限的对象访问。另外，新版也可以为 SQL Agent 不同的工作定义不同的代理账号，而不像以往只有通过 SQL Agent 服务账号或一个代理账号可以设置。本机的 HTTP 支持不知道你是否曾经有过这样的疑问，为什么 OSI 网络七层结构中或互连网四层结构中，传输层与网络层可以几近统一使用 TCP 和 IP 协议，但为何在应用层需要熟悉这么多种协议，例如传输文件用 FTP 或 WebDev 协议、发送电子邮件用 SMTP、收电子邮件用 POP3、访问 SQL Server 用 TDS、调用远端应用程序用 DCOM、浏览网页用 HTTP 等等。应用层是否也可以使用统一的协议？慢慢地，答案似乎呼之欲出。是的，就是 SOAP，也就是 HTTP 加上 XML。在微软和 IBM 等大厂渐渐取得共识后，各项新的产品几乎都开始采用 SOAP 当作标准的访问协议。而 SQL Server 2005 的各项服务大都支持 “web services/SOAP” 的访问，例如 Analysis Services 和 Reporting Services 都直接通过 web services /SOAP 访问。上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.htmlSQL Server 数据库引擎也可以设置让前端应用程序直接通过 web services 的方式调用存储过程，这让 SQL Server 可以更广泛地支持各种平台。由于 SQL Server 引擎通过 Windows 2003 或 Windows XP SP2 等版本操作系统核心的 http.sys 直接处理 SOAP 协议，因此该机器上不需要安装与启动 IIS 服务，就可以让 SQL Server 数据库引擎通过 Web Services 提供服务，以 XML 的格式传回执行结果。换句话说，开发者直接在数据库层建立 web services，SQL Server 可以当作 HTTP listener，接受以 web services 模式沟通的前端应用程序访问。这让其他平台的程序开发者，如 Java、Linux 等，除了 JDBC 或其他特殊的机制外，也都可以通过标准的 web services/SOAP 访问 SQL Server。Service Broker提供消息导向或异步的程序开发平台，通过 SQL Server 所提供的队列（Queue）让服务间异步地沟通，并保证消息传递质量，也就是消息一定寄达，且按照发送的先后顺序只寄达一次。以往建立分布式运算、消息导向或异步访问架构时，我们以程序代码调用 MSMQ 服务，两个程序通过 MSMQ 提供的 COM 对象彼此协调沟通后，再各自更新自己的数据库。而今可以各自跟数据库沟通，让数据库服务器来完成消息合作。程序设计员可以减少了解 MSMQ 架构的需求。当然，若统一在 SQL Server 的平台上，你多了一种架构选择。但若是异质平台集成或应用程序分散的拓扑（topology）复杂，或整个系统中根本不需要数据库的存在，你依然会采用 MSMQ。另外，SQL Server 2005 本身在许多机制上都使用 Service Broker，让原本前台同步的运作可以改为后台异步运作。例如新增的 Database Mail 就会在用户通过系统存储过程要求寄发电子邮件时，先将需求放入到 Service Broker 中，把执行权立刻还给用户，而不必让用户空等系统和 SMTP 服务沟通，后台程序再从队列中读取命令，完成电子邮件的寄发。而新提供的 Event Notification 也是通过 Service Broker 收集各种系统事件，它提供了与 DDL/DML 触发器不同的架构。也就是若你在用户执行 DDL 或 DML 语言后，需要立刻处理的商业逻辑可以通过触发器来完成，但也由于默认触发器执行时会与上述的语句绑在同一个事务中，因此会延长系统回应用户的时间，甚至由于打开事务过久，而卡住大量的系统资源，破坏了在线同时访问的能力。因此若触发的事件可以批次后台执行，通过 SQL Server 2005 新提供的 Event Notification 机制，你可以通过队列累积需求，在系统不忙碌的时间来完成这些需求。而本文前述 SQL Server 2005 和 ADO.NET 2.0 合作的主动通知机制，其实也是建立在 Service Broker 之上的。你所编写的应用程序也可以通过 Service Broker 将同步运行改成异步，以往通过触发器立刻处理数据变换后该完成的商业逻辑，或许也可以通过 Service Broker 累积需求，选择在系统不忙碌的时间再批次完成。由于 Service Broker 还提供在需求量大时调整负荷的机制，让你更能够善用多 CPU 的服务器资源。Notification Service让大量的事件与大量的订阅可以在 SQL Server 平台上聚合，如数百万人对数百支股票各有各的兴趣，每个人可以自行订阅他有兴趣的多个事件。当大量的人订阅了各种事件，并设置了需要通知的条件和通知的机制，SQL Server 在收集大量事件的发生后，以 Join 的方式集成需求，然后通知各订阅者。这个服务在 SQL Server 2000 时就以外挂的方式提供，至今已经可以直接从微软网站下载 上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html2.0 版。但很可惜的是它没有友善的设置界面以及简易的程序编写模型，因此你需要花点功夫来学习设置与编写程序。1.3 完备的分析平台_SQLServer2005 数据库开发详解1.3 完备的分析平台在 SQL Server 7.0 时，微软首度将商业智能分析功能加入到数据库产品中。历经了 SQL Server 2000 版本，而新版将商业分析的功能发扬光大。本节先列举重要的新增功能。SQL Server 2005 Integration Services全新设计架构，而不再延续前版的 DTS 功能，提供工作流（Work Flow）与数据流（Data Flow）分开的运行模式。支持循环容器，让你可以轻易地在工作流程中设计 For Loop 或 For Each Loop 等商业逻辑。以管线与临时数据结构重新设计数据转换的流程，这让数据处理的工作负担可以大幅落在 SSIS 所执行的机器上，以减轻数据库服务器在数据转换工作上的负荷。不管是在集成开发环境，还是包内的设计与编写扩展功能上，都改以 .NET 为主。包内通过 VB.NET 来编写工作流的“执行 Script 任务”或数据转换流内的“Script 原件”，抑或是通过在包外部以 .NET 语言参照 SSIS 提供的 .NET 类型，开发集成 SSIS 的应用程序。Analysis Services提出 UDM 的概念也就是具备以下四点：集成异质数据源：以数据源视图（Data Source View）统一地描述其后方各种异质型的数据源的架构（schema） 。l 丰富的数据显示与浏览模型，以专业领域的用词显示符合商业逻辑的数据：提供动态与弹性查看数据的方式，因此 Analysis Services 2005 除了以往的度量值、维度外，还提升了属性（Attribute）的分析能力、加入 KPI、透视（Perspective）、多国语言翻译（Translation）等功能。深入分析：新加入 MDX Script 语句，并可以调用 .NET 语言编写运算函数。高级的高速缓存：不同等级的高速缓存，区分不同数据浏览的需求，在大量数据与效率间取得平衡，让及时分析变得可行。另外，它提供更完备的向导，让你建立分析 Cube 时更容易。在建立 Cube 时，Cube 向导自动检测与分析数据源视图内的数据关联性，并完成对 fact tables、dimension tables 和 measures 的建议。支持多实例（Multi-instance）及聚类分析（Clustering）技术，提供更好的稳定性。这一版的 Profiler 工具程序也可以跟踪 Analysis Services 所触发的事件。让你可以通过 Profiler 上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html来跟踪、调试与监控 Analysis Services。Data Mining提供 9 种数据挖掘模型，在“SQL Server Business Intelligence Development Studio”集成了丰富的查看与测试数据挖掘模型的工具，并支持预测查询语句的编写环境。在“SQL Server Management Studio”中也支持 DMX（Data Mining Extension）语句的编写、执行与管理。并提供 Web 界面查看挖掘模型的原始实例应用程序代码。Reporting Services新增更为友善的用户互动界面，例如通过日历控制项来选择日期，报表参数可复选、可动态依照不同数据字段排序、提供 MDX 语句产生器，以方便建立 Analysis Services 的报表、提供 Report Viewer 控制项方便开发者内嵌报表到应用程序中。另外，新提供 Report Data Model 设计环境和 Report Builder 工具程序，让普通人无须借助专业的报表设计人员就可以自行产生所需的报表。1.4 升级到 SQLServer2005 的好处_SQLServer2005 数据库开发详解1.4 升级到 SQL Server 2005 的好处如上所述，SQL Server 2005 增加了许多新的功能，但若仅是将旧系统升级而不做任何改变，大概用不到上述这些好处，困为你必须重新学习这些功能的定义。然而将旧有版本升级成 SQL Server 2005 仍有许多立即的好处，除了提供学习研发的机会外，SQL Server 2005 在针对旧有功能的性能提升、增加系统稳定性上，也下了极大的功夫。本节讨论的是你仅需完成升级，就可以立刻享有到的好处。全文检索首先是针对全文检索，新版建立 Catalog 内容时，扩展（Index population）的速度较旧版快一倍以上，而查询效率也提升 30%-50%。另外，全文检索服务支持多实例（multi-instanced） ，各 SQL Server 实例使用各自的全文检索服务实例，不会互相干扰。全文检索也支持聚集架构以提高可获得性。SQL Server 2005 可以备份/还原、分离/附加（Detach/attach ）全文检索的 catalog，让你在还原数据库或迁移数据库之后，无须耗时重新扩展以建立 catalog。查询引擎最佳化当我们输入 SQL 语句到 SQL Server 服务器端时，若查询引擎越聪明，就能够越快速且正确地判断用户的意图，找到最佳的执行计划，才能越有效率地完成用户的需求。在此看一下 SQL Server 2005 的查询引擎较 2000 更为聪明的地方：若执行的 update 语句实际并未改变数据内容，则 SQL Server 不会浪费资源去重新维护索上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html引。有效地加强文字符号（Symbolic）查询的解读。当 T-SQL 语句格式是exp1 Op exp2而 exp 操作数分别是字段名称和常数；op 为 5 and a3 and a=0 直接会传回 false，因为 a 不可能既等于 0 又大于 5。?（a between 1 and 100） and （a between 100 and 200） 直接将条件改成 a=100。l 若条件是 a3 and a5 ，则实际条件是 a5，在通过索引寻找时，自动使用条件比较严苛的 a5。l 增强 Indexed view 可用性，提供更多使用情境的对比。l 对等联结（Equi-join）的条件使用如下语句时：r.x=s.x and s.x 10其实隐含着以下的意义：r.x=s.x and s.x 10 and r.x 10SQL Server 2005 除了在 s.x 字段测试是否有可用的索引，也会尝试在 r.x 字段上看看有没有可用的索引。上述正规与减化符号查询的机制，将更能有效地建立最佳的执行计划。统计信息若要正确判断执行计划，SQL Server 先要对所存储的数据内容分布建立详细而正确的统计。2005 关于存储数据统计的部分提供以下的强化：l 以往自动更新统计在数据量很大时，可能会阻止 OLTP 应用程序的执行。现在维护统计数据时，可以异步更新统计，因而不会卡住正在执行的查询。若建立新的统计正做到一半，目前想要执行的查询就用旧的统计。通过启动如下的数据库设置，可以启动异步更新统计。ALTER DATABASE db 名称 SET AUTO_UPDATE_STATISTICS_ASYNC ONl 统计的数据更为精确，提供 string summary statistics，可以支持如 like %字符串% 形式的过滤条件。以更精确的统计减少建立执行计划时的猜测。l 针对计算字段手动或自动建立统计。例如：因为能自动对计算字段建立统计，所以可准确预测如下的 where 条件。而如同 Indexed View，在下述实例中 Where 条件并未引用 b 字段，但因为定义相同，SQL Server 就会自动引用这个建立在计算字段 b 上的统计：create table t1（a int,b as a+1）上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.htmlcreate table t2（a int, c int）SELECT * FROM t1 JOIN t2 ON t1.a=t2.aWHERE t1.a+1 10l 以平行运算的方式更有效地建立 Full scan 的统计。l sp_updatestats 系统存储过程只更新需要更新的统计。该存储过程可以通过 UPDATE STATISTICS 语句更新目前数据库内用户自定义以及系统数据表相关的统计。在 SQL Server 2005 版的该存储过程只更新需要更新的统计，没有数据更改的就自动跳过，因此整体效率更好。执行计划的共享与重用若多条连接或同一条连接重复执行相同 SQL 语句时，执行计划能够共享与重用，将可减少 CPU 建立执行计划的负荷。为此需求，SQL Server 2005 提供了以下的功能。l 针对在批次内或存储过程的多句语句，可使用语句层次的重新编译，不必完整重新编译全部批次或存储过程。例如几千行的存储过程中，用到了临时数据表（temp table） ，在重新调用该存储过程时，只需要重新编译使用到缓存数据表的那几行语句。l 强迫使用参数化查询，自动将条件中使用的常数当作参数，避免当相同查询语句以不同参数输入时，重新编译执行计划。例如你前端程序两次输入的 Where 条件是：Where col=1 和 Where col=2，虽然主体的查询语句相同，但因为整句话不同，这时 SQL 可能会重新编译执行计划。因此最好是 Where col=input，让编译执行计划时，不管参数值为何，都直接使用相同的查询语句。在 SQL Server 2005 中你可使用数据库的设置，强制启动自动将常数当作参数来编译执行计划，设置语句如下：ALTER DATABASE db 名称 SET PARAMETERIZATION FORCED以往 SQL Server 2000 其实就有部分实现上述的功能，不过 2005 版可以让你强制启动，以尽量将语句中所有的常数都当作变量。另外，在执行查询时，语句包含动态对象，如数据表类型变量、触发器等的执行计划仍可以被缓存。执行查询精确地建立统计，参照该统计分析出有效率地执行计划，聪明的高速缓存避免重复建立执行计划后，接着还需要执行查询并返回结果。SQL Server 2005 针对在不同服务器上执行的分布式查询可以异步执行，也就是当批次内分别在不同的链接服务器（Iinked Server）下查询语句时，SQL Server 2005 会以多线程的方式同时对不同的服务器下查询语句，再将结果综合返回到前端。以往采用顺序的方式，需要等到第一句语句在某台服务器执行完毕后，才将下一句话放到另一台服务器上执行，无法发挥多台服务器平行运作的优势。上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html另外，当通过 SQL Server Profiler 工具程序录制 SQL Server 执行的状况时，提供更为丰富的信息，如分布式查询、以 XML 及图形化描述死锁信息。据此，你可以完成监控、调试、及性能调教等操作。最后，你还可以在重建索引的同时执行查询语句。存储引擎针对存储引擎，SQL Server 2005 也进行了许多的强化：l 动态字符串的数据列表可以跨页，通过 MAX 数据类型的声明，数据大小可以超过 8060 字节的限制。l 更有效地使用 tempdb1。l 自动快速缓存数据表和数据表变量的第一页。l 更有效地使用资源，如取得数据页（page） 、减少使用事务记录。l 备份? 数据备份和 Log 备份可以同时进行，批次执行的 Log shipping 在备份 log 时不会被数据备份卡住。? Restore Verifyonly 语句现在会读取每一数据页，以确认数据，所以比以往的版本耗时。l 文件 I/O? 默认启动新提供的 Page Checksum 功能，提升系统的可信赖度（reliability） 。? 可以单独还原损毁的数据页。l Insert：最佳化写入大量数据到 b-tree 数据区块，效率与 bulk load 相似，但不需要使用 bulk load 语句。l 数据库恢复（recovery ）：更有效率，数据库在 undo 阶段就可以使用。但这需要企业版才支持。l 事务管理提供单条记录的版本（row version） ，可在数据库级别设置 read committed snapshot，不必在连接（session）级别设置，就可以提供 snapshot isolation 的功能。数据库的相关设置如下：? ALLOW_SNAPSHOT_ISOLATION? READ_COMMITTED_SNAPSHOT连接维持在原 Read Committed 事务级别? 在 T-SQL 语句中可以通过 ReadCommittedLock 这个 Query hint；在启动 row version 后，仍使用原 Read Committed 事务级别的行为。l 触发器会利用 Row-level versioning 的技术，在读取新旧记录时，会自动读取 row-level version，不管数据库是否启动 snapshot isolation。? 不像以往需要从事务记录找寻新旧改变，尤其在多人同时连接时，避免同时抢 log 读取的操作。l 以相同的字段重建聚集索引（Clustered Index）时，如下列的语句所示，不会导致非聚集索引一起重建2。上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html? DBCC DBREINDEX（tbl,idx）? CREATE CLUSTERED INDEX idx ON Tbl（col） WITH DROP_EXISTING? ALTER INDEX idx ON tbl REBUILD 新语句SQL Server 内部的 OS（SQLOS）3更稳定更有效率l 启动 AWE 设置后，仍可动态使用存储器。l 更准确地在高速缓存中保留有用的数据页（page） 。l 加上对存储器数据页的 check sum 检查，以早期发现存储器硬件的错误。l 保留一条 Dedicated Admin Connection（DAC） 资源，在 SQL Server 资源用尽时，可以通过该连接进入管理。l 以 XML 格式输出死锁的相关数据，SQL Profiler 可用图 2-24 显示死锁信息。l 以往线程（thread）是绑在 session 上，现在是绑在批次（batch）上，因此，当一个 session 传递多个批次，而每个批次都很耗 CPU 资源，现在可以分散到多颗 cpu 同时执行。除了上述条列的各项功能外，另外还有在单一机器可以同时安装多个 SQL Server 数据库引擎、多个 Analysis Services、多个 Reporting Services 实例。通过 SQL Server 界面区配置提升安全管理的方便性。SQL Server Profiler 现今也可以记录 Analysis Services 的执行内容等等。只要升级后，立刻可感受到上述功能的优点，而不必另行改变数据库架构与设计应用程序。总的来说，因为 SQL Server 引擎更为聪明，更能有效地使用硬件资源，因此直接升级后，不改任何程序代码与设置的状态下，便可以提升性能、稳定性与管理方便性，尤其对于大型数据库更能发挥所长。当然，升级后，若你能稍作改变，使用 SQL Server 新提供的功能是更佳的做法。1 tempdb 的使用建议：建立与 CPU 数目相同的文件数（参考该 SQL Server 实例的 affinity mask 设置），且多个文件的大小要相同。2若聚集索引不是唯一的，因为维持唯一的值在以往可能会随着 rebuild 而重建，则非聚集索引凭着聚集索引指向数据的唯一指针也需要重建，现在 SQL 2005 会在重建聚集索引时，维护这个原有的唯一值。3因为 SQL Server 的核心某部分需要做 Memory allocation、建立、分配与替 thread 排各 cpu 的调度等等，因此该部分也称为 SQLOS。上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.html1.5 结语_SQLServer2005 数据开发详解1.5 结语 很多朋友在各种场合询问笔者：透过通过 SQL Server 2005 的各种功能设计，应用程式程序的最佳架构是什么？大哉斯问?！。SQL Server 2005 提供了很多新的功能，但都还需要市场的验证，需要经验的累积来告诉我们运用整体功能的最佳实作实现方式为何。相信在各位的努力下，我们审慎地重新检视审查原有应用程式程序需求，在新的 SQL Server 2005架构与功能中，才能探究出发挥最佳的搭配组合。 SQL Server 2005 所提供的功能彼此绵密紧密交错，不能简单地以管理、开发、分析单纯地划分。例如安全架构一定同时影响这三方面，异步地运作、主动通知等也让管理与分析系统有新的面向方面。若仅仅将 SQL Server 2005 资料库数据库引擎当作 SAL Server 2000来用，单纯地升级，你也应立刻多少感受到效能性能提升的好处。但此时此刻，实应坐下来好好赏玩一下 SQL Server 2005，而后重新思考整体架构，毕竟做对的事情比把事情做对优先。 最后附带一点，因为书中引用了 SQL Server 2005 所提供的范例实例资料库数据库AdventureWorks 当作资料数据源，所以若要测试本书的范例实例，请在安装SQL Server 2005 时，须额外勾选一并安装范例实例资料库数据库。另外，笔者也在随书光碟光盘中附上建立以往版本的范例实例资料库数据库 Northwind 和 Pubs 之 T-SQL Script 文件，若你需要资料数据来测试，又较熟悉以往范例实例的架构，也可以直接透过通过这些Script 来建立这两个范例实例资料库数据库。 11.1 开发给 SQLServer2005 用的 AssemblySQL Server 2005 在核心引擎中加入了 .NET 的公共语言执行环境 CLR (Common Language Runtime)。一直以来 SQL Server 核心引擎只有两大引擎：关系引擎(Relational Engine)和存储引擎(Storage Engine)，现在变成三个，其改变不可谓不大。本章将介绍 SQL Server 2005 引入 .NET 引擎后的开发方式与用途。由于 .NET Framework 自身非常巨大，而且又不是本书的探讨主题，若你有兴趣深入研究 .NET Framework 可以参照 .NET Magazine 网站( .tw/)。11.1 开发给 SQL Server 2005 用的 AssemblySQL Server 2005 让存储过程、触发器、用户自定义函数、用户自定义类型以及用户自定义聚合函数（Aggregate）等五种 SQL Server 对象皆可以通过 .NET 的程序集(assembly)来定义。以往必须要通过 C/C+ 才能办到的扩展存储过程(Extended Stored Procedures)而今你只要通过自己所熟悉的 .NET 语言，如 C# 或 VB.NET 来完成即可(但要放入 SQL Server 2005 的只能是 dll 函数库类型，不可以是 exe 执行文件 )。而 .NET 自身强大的功能皆可以融入到 SQL Server 2005 中。将.NET 开发的程序集成到 SQL Server 中有以下的好处：上学吧：上学吧为您提供微软认证考试资料下载：/share/e49.htmll 更强的程序设计逻辑：.NET Framework 中包罗万象的各种类型功能，你大都能够引用，但不是全部的类型都被允许调用使用，你可以在 Visual Studio 2005 打开数据库项目后，通过“添加引用”查看能使用的类。l 更安全：由于 .NET 对于程序编写与执行环境的安全下了很大的功夫，不管是执行时潜在的类型安全检查(type safety check)还是管理员所设置的程序代码访问安全(Code Access Security)与角色基础安全(Role Base Security)等等诸多机制，都加强了程序执行环境的安全。SQL Server 使用根植于 CLR 的组件执行起来也就更安全与稳定。l 统一的开发与调试环境：Visual Studio 2005 开发环境提供编写 SQL Server 2005 对象的模板，同时支持部署到 SQL Server 2005 的功能，由于 Visual Studio 2005 自身的便利性，将可以提升开发 SQL Server 2005 对象的品质与性能。l 性能和扩充性：通过 .NET 所开发出来的程序集(Assembly)是已经编译过的程序，若是以 T-SQL 编写商业应用，则是以解释的方式执行。所以较为复杂的商业应用当然用 .NET 的语言编写更好。l 多语言选择：由于 .NET 将程序语言与功能分开，因此挑选语言并没有很大的意义，不管是 Visual Basic、C#等语言，还是其他的语言，只要你熟悉该种语法，即可开发 SQL Server 可用的对象，而不像以往一定要使用 C/C+ 才行。虽然用 .NET 程序语言开发 SQL Server 的对象有很多好处，但它仍无法取代 SQL 语言，以及纯用 T-SQL 编写的的 SQL Server 对象。复杂的商业逻辑，需要面向对象分析设计，结构化处理，访问文件、网络等周边设备，对数据加解密等诸多程序功能，自然用 .NET 语言更好。但若纯粹访问数据，都是集合（set ）方便进行添加、修改、删除和查询数据的工作，此种仍用 T-SQL 较为适合，这样才能发挥关系引擎最优化执行 SQL 语法的功能。若将所有的商业逻辑都搬进到 SQL Server 势必大幅增加服务