简介
Microsoft 通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CLR),SQL Server 2005显著地增强了数据库编程模型的功能。 它支持用任何 CLR 语言(特别是 Microsoft Visual C# .NET、Microsoft Visual Basic .NET 和 Microsoft Visual C++)编写过程、触发器和函数。同时,它还提供了扩展数据库系统中的类型系统和聚合函数的功能,方法是允许应用程序创建它们自己的数据类型和聚合函数。
本白皮书从数据库应用程序开发人员的角度描述了如何最有效地利用这项技术。它还将 CLR 与 SQL Server 中支持的现有编程语言(即 TransacT-SQL (T-SQL) 和扩展存储过程 (XP))进行了比较。本白皮书不打算提供这些功能的基本参考资料,SQL Server 2005 Beta 2 Books Online 对其进行了详细介绍。
本白皮书的目标读者包括数据库应用程序开发人员、架构师和数据库管理员。本白皮书假设读者熟悉基于 .NET Framework 的编程和数据库编程。
CLR 集成概述
下面简要概述了 CLR 集成执行的 SQL Server 功能,以及 Visual Studio 2005 最终发布版本如何支持这些功能。
注册和执行数据库中的托管代码包括以下步骤:
? |
开发人员将托管程序编写为一组类定义。将 SQL Server 内旨在用作存储过程、函数或触发器(下面统称为例程)的代码编写为类的 static (或 Microsoft Visual Basic .NET 中的 shared )方法。将旨在用作用户定义的类型和聚合的代码编写为一个整类。开发人员编译该程序并创建一个 程序集 。
---|---
? |
然后,将此程序集上载到 SQL Server 数据库,在其中使用 CREATE ASSEMBLY 数据定义语言 (DDL) 将它存储到系统目录。
? |
接着,创建 TransacT-SQL 对象,例如,例程(函数、过程和触发器)、类型和聚合,并将其绑定到已经上载的程序集中的入口点(对例程来说是方法,对类型和聚合来说是类)。使用 CREATE PROCEDURE/FUNCTION/ TRIGGER/TYPE/AGGREGATE 语句来完成这一步。
? |
在创建了这些例程之后,应用程序就可以像使用 T-SQL 例程一样使用它们。例如,可以从 T-SQL 查询中调用 CLR 函数,从客户端应用程序或从 T-SQL 批处理中调用 CLR 过程,就好像它们是 T-SQL 过程一样。
Visual Studio 2005 Beta 1 支持在 SQL Server 2005 中开发、部署和调试托管代码。有一种新的项目类型(称为 SQL Server 项目),它允许开发人员在 SQL Server 中开发、部署和调试例程(函数、过程和触发器)、类型和聚合。
构建和部署
SQL Server 项目提供了代码模板,这使得开发人员能够轻松地开始为基于 CLR 的数据库例程、类型和聚合编写代码。该项目还允许添加对数据库中其他的程序集的引用。在构建项目时,可以将其编译成一个程序集。部署此程序集可以将程序集的二进制文件上载到与该项目相关联的 SQL Server 数据库中。部署操作还自动创建在数据库的程序集中定义的例程、类型和聚合,方法是使用在代码中定义的自定义属性(SqlProcedure、SqlFunction 和 SqlTrigger 等等)。它还上载与该程序集相关联的源代码和 .pdb 文件(调试符号)。
调试
对于任何平台来说,调试都是开发人员体验的基本部分。SQL Server 2005 和 Visual Studio 2005 为数据库编程人员提供了这些功能。调试 SQL Server 2005 对象的关键部分在于其易于安装和使用。调试到运行 SQL Server 的计算机的连接在很大程度上同调试传统操作系统中运行的进程的方式一样。调试器的功能不会受到客户端到服务器的连接类型的影响。这样就可以调试表格数据流 (TDS) 和 HTTP 连接。而且,还可以跨语言进行无缝调试。因此,如果有一个调用 CLR 存储过程的 T-SQL 存储过程,调试会允许您从 T-SQL 过程进入到 CLR 过程。
CLR 与 TransacT-SQL
现在我们进入本文的关键部分:对 CLR 集成和 SQL Server 中支持的现有编程语言进行比较。
TransacT-SQL (T-SQL) 是 SQL Server 支持的本机编程语言。和大多数 SQL Server 版本一样,它包含数据处理功能和数据定义功能。数据处理功能一般可以分为两类:查询语言(由 SELECT/INSERT/UPDATE/ DELETE 语句组成)和过程语言(WHILE、赋值、触发器、光标等)。一般来说,SQL Server 中的 CLR 支持为过程语言提供了 T-SQL 的替代方法。
即使在 SQL Server 中引入 CLR 支持以前,数据库应用程序应该尽可能多地使用查询语言,这始终被认为是很重要的。数据库应用程序应该利用面向集的查询处理器,并且只在查询语言无法表示逻辑时才转向过程编程。对于 SQL Server 中的 CLR 支持,这仍然是正确的。不应该使用 CLR 来编写可以用简单的 SELECT 语句表示的过程代码。在 SQL Server 2005 中增加了许多重要的功能来提高T-SQL 查询语言的表达能力。
? |
递归查询:遍历表中的递归层次的能力
---|---
? |
分析函数:RANK 和 ROW_NUMBER 允许排列结果集中的行
? |
新的关联操作:APPLY、PIVOT 和 UNPIVOT
试图使用 CLR 功能的开发人员应该确保他们充分地利用查询语言,包括 SQL Server 2005 中的扩展在内。对于在查询语言中无法以声明方式表示的逻辑,他们应该考虑将 CLR 作为有效的替代办法。
现在让我们看一些方案,其中基于 CLR 的编程能够补充 T-SQL 查询语言的表达能力。通常,需要在查询(可称为函数)内嵌入过程逻辑。这允许许多方案,例如:
? |
根据数据库表中存储的值,对每行进行复杂的计算(必须用过程逻辑来表示)。这可以包括将这些计算的结果发送给客户端,或者使用计算来过滤发送给客户端的行集,如以下示例中所示:
SELECT
1<complex-calculation>(<column-name>,...)
2 FROM <table>
3 WHERE <complex-calculation>(<column-name>,...) = ...
4
5
6---|---
7? |
8
9? 使用过程逻辑来评估表格结果,然后在 SELECT 或 DML语句的 FROM 子句中进行查询。
10
11SQL Server 2000 引入了支持这些方案的 T-SQL 函数(标量和表值)。有了 SQL Server 2005,就可以用 CLR 语言更容易地编写这样的函数,并且会极大地提高它们的性能。之所以编写这些函数非常容易,是因为事实上编写 CLR 代码的开发人员可以利用 .NET Framework API中存在的大量有用函数和类。这个类/函数库比 TransacT-SQL 中支持的内置函数要丰富得多。此外,CLR 编程语言提供了 T-SQL 中所没有的丰富构造(例如数组和列表等)。与 T-SQL(它是一种解释语言)相比,CLR 编程语言之所以具有更好的性能,是因为托管代码是已编译的。对于涉及算术计算、字符串处理、条件逻辑等的操作,托管代码的性能可能要优于 T-SQL 一个数量级。
12
13**注** **** 对于函数,几乎没有必要从函数中访问数据库。外部查询已经从数据库中检索到数据值,并且将其作为参数传递给函数。这是 CLR 的优势,在计算性任务上比 T-SQL 更胜一筹。
14
15从 CLR 中访问数据
16
17现在让我们从编程模型和性能这两个方面,看看如何用 CLR 来编写访问数据库的业务逻辑。
18
19编程模型
20
21使用 T-SQL,只是在过程代码内部嵌入查询语言语句 (SELECT/INSERT/UPDATE/ DELETE)。通过托管代码,可以使用 SQL Server 托管提供程序来实现 Microsoft ADO.NET 数据访问 API(也称为 _in-proc ADO.NET_ )。使用这种方法,可以将查询语言语句(SELECT 和 DML 语句)作为动态字符串嵌入,并且将其作为参数传递给 ADO.NET API。与 T-SQL 的静态方法相比,过程代码内嵌入的 SQL 语句的基于动态 API 的特性是它们在编程模型上的主要区别。不利的是,in-proc ADO.NET 模型会产生比 T-SQL 更冗长的代码。此外,因为 SQL 语句是动态字符串,所以在执行之前不在语法或语义上进行编译和验证。有利的是,带有 ADO.NET 的数据库编程模型与客户端或中间层中使用的模型相似,因而更容易在各层之间移动代码和利用现有的技术。
22
23此外,在基于 T-SQL 和基于 CLR 的编程模型中使用的都是同一 SQL 查询语言,不同之处在于过程部分,注意到这一点是非常重要的。
24
25性能
26
27正如已经提到的,在谈及过程代码、计算等方面时,与 T-SQL 相比,托管代码在性能方面具有决定性的优势。然而,对于数据访问方面,T-SQL 在性能方面通常会更好。因此,通用规则是用 CLR 编写计算和逻辑密集的代码要比数据访问密集的代码好。不过这值得更详细地考虑。
28
29让我们看看数据访问编程中的一些典型的基元和模式,以及在这些情况下如何使用 ADO.NET 进行 T-SQL 和托管编程。
30
31将结果发送到客户端
32
33这包括将一组行发送到客户端,而没有在服务器中“消费”它们(即没有在例程内导航行)。使用 T-SQL,只需在 T-SQL proc 中嵌入一个 SELECT 语句就可以将 SELECT 产生的行发送到客户端。通过托管代码,可以使用 SqlPipe 对象将结果发送到客户端。T-SQL 和 in-proc ADO.NET 平台在这种情况下的作用是一样的。
34
35提交 SQL 语句
36
37这包括来自过程代码的 SQL 语句的执行往返。在这种情况下,T-SQL 具有很大的优势(比 in-proc ADO.NET 快两倍多)。
38
39此处需要重点注意的是,之所以在 CLR 中出现性能降低,是因为增加了额外的代码层,包括将来自托管代码的 T-SQL 语句提交给原生 SQL 代码。在将该语句提交给查询处理器之后,基于语句源(T-SQL 或 in-proc ADO.NET)的性能方面就没有什么不同了。
40
41**注** **** 典型的数据访问密集的存储过程可能涉及提交一系列的 SQL 语句。如果 SQL 语句简单,并且不需要花费大量的时间执行,则来自托管代码的调用开销可能占用大部分执行时间,这样的过程用 T-SQL 编写将执行得更好。
42
43只进、只读行导航
44
45这包括以只进、只读方式一次导航一个由 SQL 语句产生的行。在 T-SQL 中,这是通过只进、只读光标实现的。在 CLR 中,这是通过 SqlDataReader 实现的。通常,每一条语句都涉及一些处理。如果忽略了与每行相关联的处理,则导航行在 CLR 中就比在 T-SQL 光标中稍慢。然而,如果您关心为每行执行的处理,则 CLR 会更有优势,因为 CLR 在这种处理上比 T-SQL 做得好。
46
47带有更新的行导航
48
49如果需要根据光标中的当前位置更新行,则没有相关的性能比较,因为 in-proc ADO.NET 不支持此功能,而应该通过 T-SQL 可更新光标来进行此操作。
50
51注 在任何可能的情况下,最好使用 UPDATE 语句来批量更新行,只有在这样的修改无法用单一的 UPDATE 语句进行表示时,才应使用基于光标导航的 UPDATE。
52
53以下示例说明在特定情况下如何确定 T-SQL 和 CLR 将执行:
54
551\.
56
57|
58
59考虑这样一个过程,它执行一系列(或在最简单的情况下仅一个)INSERT/UPDATE/DELETE/SELECT 语句,带有几个或者不带返回到客户端的行,并且不导航 SELECT 产生的行。如果将这样的过程编写成 T-SQL 过程可能执行得更好。
60
61---|---
62
632\.
64
65|
66
67考虑这样一个过程,它执行单一的 SELECT 语句,并且使用存储过程内的行,方法是,一次导航一行并进行一些不涉及对每行进行更多的数据访问的处理。这个过程可能在带有 in-proc ADO.NET 的 CLR 中执行得更好,特别是如果每行都有一些大量处理的开销时(因为这样的操作在 CLR 中比在 T-SQL 中更加高效)。
68
69下面是一些简单的指导原则,可以用来在 CLR 和 T-SQL 之间进行选择:
70
71? |
72
73尽可能使用带有 T-SQL SELECT、INSERT、UPDATE 和 DELETE 语句的基于集的处理。只有在无法使用基于集的 DML 语句之一表示逻辑时,才应该使用过程和基于行的处理。
74
75---|---
76? |
77
78如果过程仅仅是一个通过封装基本 INSERT/UPDATE/DELETE/SELECT 操作访问基表的包装,则应该用 T-SQL 进行编写。
79
80? |
81
82如果过程主要包括结果集中的只进、只读行导航,以及一些涉及每行的处理,则用 CLR 编写可能更有效。
83
84? |
85
86如果过程包括大量的数据访问以及计算和逻辑,则可以考虑将过程代码分隔为 CLR 来调用 T-SQL 过程,以进行大部分的数据访问(反之亦然)。另一个替代方法是,使用单一的 T-SQL 批处理,它包括从托管代码执行一次的一组查询,以减少从托管代码提交 T-SQL 语句的往返次数。
87
88后面的部分将更深入地讨论在处理结果集时何时及如何适当地使用 T-SQL 和 CLR。
89
90##
91
92CLR 与 XP
93
94在 SQL Server 以前的版本中,扩展存储过程 (XP) 为数据库程序开发人员提供了唯一可用的机制来编写服务器端逻辑,这要么难于表示,要么不可能用 T-SQL 编写。CLR 集成提供了一种更健壮的替代方法来编写这种存储过程。此外,使用 CLR 集成,过去以存储过程形式编写的逻辑通常可以更好地表示为表值函数,因为它们允许它们允许将该函数构造的结果放在 SELECT 语句中进行查询(通过将这些结果嵌入到 FROM 子句中)。
95
96以下是使用 CLR 过程或函数与 XP 相比的优势:
97
98? |
99
100**粒度控制** :很少可以控制 XP 能做什么或者不能做什么。使用代码访问安全模型,SQL Server 管理员可以分配三种权限之一:SAFE、EXTERNAL_ACCESS 或 UNSAFE,从而对托管代码允许进行的操作集进行不同程序的控制。
101
102---|---
103? |
104
105**可靠性** :托管代码(特别是在 SAFE 和 EXTERNAL_ACCESS 权限集中)提供了比 XP 更安全、更可靠的编程模型。可验证的托管代码确保了所有对对象的访问都是通过强类型化的接口实现的,从而降低了程序访问或破坏属于 SQL Server 的内存缓冲的可能性。
106
107? |
108
109**数据访问** :使用 XP£?编程人员必须向后显式连接到数据库(称为回环),以访问本地 SQL Server 数据库。而且,必须将此回环连接显式绑定到原来的会话事务上下文,以确保 XP 可以参与到调用它的同一个事务中。通过托管代码,可以使用更自然和更有效的编程模型来访问本地数据,这些模型利用当前的连接和事务上下文。
110
111? |
112
113**性能** :System.Data.SqlServer API 允许托管过程将结果集发送回客户端,其性能比 XP 使用的开放式数据服务 (ODS) API 更好。此外,System.Data.SqlServer API 支持新的数据类型(如 SQL Server 2005 中引入的 XML、(n)varchar(max)、varbinary(max)),而没有扩展 ODS API 来支持这些新的数据类型。
114
115? |
116
117**可伸缩性** :通过托管代码,SQL Server 可以管理资源(如内存、线程和同步)的使用,因为公开这些资源的托管 API 是在 SQL Server 资源管理器上实现的。相反,SQL Server 不能查看或控制 XP 的资源使用情况。举例来说,如果 XP 消耗了太多的 CPU 或内存资源,就没有办法使用 SQL Server 来检测或控制。然而,通过托管代码,SQL Server 可以检测到特定线程在一段很长的时间内一直没有退出,然后就强制该任务退出,这样其他工作可以按计划进行。因此,使用托管代码提供了更好的可伸缩性和健壮性。
118
119正如上面所提到的,在数据访问和发送结果集给客户端方面,CLR 过程比 XP 做得更好。对于不包括数据访问和发送结果的代码,比较 XP 和托管代码的性能就是比较托管代码和原生代码的性能。一般来说,在这些情况下托管代码比不上原生代码的性能。而且,当在 SQL Server 内运行时,从托管代码到原生代码的事务处理过程有额外的开销,因为在移动到原生代码和从原生代码移回时,SQL Server 需要对特定于线程的设置进行额外的登记-保留。因此,对于在托管代码和原生代码之间有频繁的事务处理的情况,XP 大大地胜过在 SQL Server 内部运行的托管代码。
120
121对于大多数扩展过程,如果考虑数据访问和结果发送的可伸缩性、可靠性和性能优势,CLR 过程提供了更好的替代方法。对于性能主要是由处理(数据访问和结果发送之外的)和频繁的托管-原生转换决定的情况,应该权衡 CLR 的可伸缩性和可靠性的优势与 XP 的原始性能优势。
122
123## 代码位置:数据库与中间层
124
125通过在数据库中提供丰富的编程模型,CLR 集成提供了将逻辑从其他层移动到数据库层的选择。然而,这显然并不意味着所有或大部分逻辑应该移到数据库中。
126
127将逻辑移到数据库层可以减少网络中的数据流量,但是增加了服务器上宝贵的 CPU 资源的负荷。因此,在应用程序中做出代码放置的决定之前,要慎重权衡。以下注意事项适用于将数据库层作为首选的代码位置:
128
129? |
130
131**数据验证** :在数据层进行数据验证的逻辑可以更好地将数据和逻辑封装在一起。这样避免了在不同数据接触点(如:后端处理、批量上载和来自中间层的数据更新等)中重复验证逻辑。
132
133---|---
134? |
135
136**减少网络流量** :对于需要处理大量的数据而产生很少的数据的数据处理任务(如数据分析应用程序中的需求预测、基于需求预测的生产安排等)来说,将逻辑放在数据库层中是合适的。
137
138**注** **** 即使在引入 CLR 支持之前,上面的注意事项也是有效的。数据库层中的 CLR 支持意味着编程语言的选择没有妨碍代码位置的正确选择。
139
140示例:生产安排
141
142生产安排是制造企业的常见任务。在高层次上,它包括制订何时生产多少单位数量的产品的计划,以便能够满足需求、最大程度的降低库存成本,同时将生产成本降到最低。有几个算法将需求预测、库存成本和生产线安装成本作为输入,而将制造策略作为输出。
143
144假定将来的需求预测存储在 SQL Server 表中,则此类算法的实现有以下特征:
145
1461\.
147
148|
149
150使用大量的数据作为输入(如需求预测)。
151
152---|---
153
1542\.
155
156|
157
158产生小结果(如在特定的日期内生产的单位数量)。
159
1603\.
161
162|
163
164需要相当多的计算以便从输入中派生输出。
165
166在中间层实现这样的算法是可行的,但是在数据库外移动输入集方面有性能损失。在 T-SQL 中将其实现为存储过程也是可行的,但是因为需要复杂的计算,性能损失就显现出来了。性能特征将随着实际的数据量和算法的复杂性的不同而不同。
167
168为了验证 CLR 集成是否适合于这样的情况,我们举一个特定的生产安排算法的示例 - Wagner-Whitin 算法的动态编程实现。正如所预料的,CLR 集成优于 T-SQL。对于这种情况,使用托管代码还有其他好处。这种算法的实现需要使用大量的一维和多维数组、数据结构,而这些在 T-SQL 中是不可用的。总之,CLR 集成的性能要优于 T-SQL 实现几个数量级。
169
170假定以下简单的数据库架构跟踪可以生产的产品列表。
171
172表 1: t_products
173---
174列名 | 类型 | 是否为空 | 说明
175
176**PID**
177
178|
179
180**int**
181
182|
183
184**非空**
185
186|
187
188**产品主键 ID**
189
190Pname
191
192|
193
194nvarchar(256)
195
196|
197
198空
199
200|
201
202产品名称
203
204InventoryCost
205
206|
207
208int
209
210|
211
212非空
213
214|
215
216存储该产品的每时段成本
217
218StartupCost
219
220|
221
222int
223
224|
225
226非空
227
228|
229
230建立生产线来生产该产品的成本
231
232下表存储了每周每个产品的需求预测信息。
233
234表 2: t_SalesForecast
235---
236列名 | 类型 | 是否为空 | 说明
237
238PID
239
240|
241
242Int
243
244|
245
246非空
247
248|
249
250产品 ID
251
252WeekDate
253
254|
255
256smalldatetime
257
258|
259
260非空
261
262|
263
264需求预测周
265
266DemandQty
267
268|
269
270int
271
272|
273
274非空
275
276|
277
278特定产品和特定周的需求预测
279
280给定一组产品,它们的库存和启动成本以及未来需求预测,我们创建了接受如下输入参数的存储过程:1)制订生产进度表的日期,2)按进度表生产所需要的周数。
281
282存储过程返回带有下表中的架构的行集。
283
284表 3:存储过程架构
285---
286列名 | 类型 | 说明
287
288Product
289
290|
291
292nvarchar(256)
293
294|
295
296产品名称
297
298Period
299
300|
301
302datetime
303
304|
305
306进度周
307
308Quantity
309
310|
311
312int
313
314|
315
316在指定周内制造的产品的数量
317
318将 C# 版本的代码复制到下面的代码中,以说明这种可以从 CLR 集成中大大获益的情况:
319
320
321 using System;
322 using System.Data;
323 using System.Data.Sql;
324 using System.Data.SqlServer;
325 using System.Data.SqlTypes;
326 public class ProductionSchedule
327 {
328 //4-year limit on scheduling
329 public const int MAXPRODUCTS = 101;
330 public const int MAXWEEKS = 210;
331 public const int MAXNAME = 256;
332 public ProductionSchedule()
333 {
334 }
335 public static int Schedule(SqlDateTime startDate, int numWeeks)
336 {
337 SqlDateTime[] week = new SqlDateTime[MAXWEEKS];
338 int[] quantity;
339 int[][] Cij;
340 int[] Fk;
341 int[] minK = new int[MAXWEEKS];
342 int product_id, current_product, product_count = 0;
343 int startPeriod;
344 // We'll use arrays to keep state about products and forecasts
345 in memory. This is only viable given that we know we have a small number
346 of products and weeks.
347 // For larger data sets, we would have to consider cursors or
348 temporary tables.
349 // stored as CLR types since we know they can't be null
350 int[] h = new int[MAXPRODUCTS];
351 int[] K = new int[MAXPRODUCTS];
352 // stored as nullable SqlChars since the table schema allows for null names
353 SqlChars[] productNames = new SqlChars[MAXPRODUCTS];
354 bool moreProducts = true;
355 int optimal_j;
356 int period;
357 int sum;
358 SqlPipe pipe = SqlContext.GetPipe();
359 SqlDataRecord record;
360 object[] values = new object[3];
361 SqlMetaData[] metadata = new SqlMetaData[3];
362 //Initialize algorithm arrays
363 Cij = new int[MAXWEEKS][];
364 for( int l=0;l<maxweeks;l++) (product_count="" all="" an="" and="" bail="" by="" cij[l]="new" cmd="SqlContext.GetCommand();" cmd.commandtext='@"SELECT' cost="" dbo.t_products="" exceeded="" exception="" expected="" fk="new" for="" from="" h="" h[product_count]="reader.GetInt32(1);" holding="" if="" int[maxweeks];="" inventorycost,="" k="" k[product_count]="reader.GetInt32(2);" look="" name="" number="" of="" order="" out="" pid";="" pname,="" product="" product_count++;="" productnames[product_count]="reader.GetSqlChars(0);" products="" reader="cmd.ExecuteReader();" sqlcommand="" sqldatareader="" startup="" startupcost="" then="" up="" we="" while(reader.read())="" with="" {="">= MAXPRODUCTS)
365 {
366 throw new Exception("Too many products");
367 }
368 }
369 reader.Close();
370 product_count = 0;
371 //Get the list of product ids;
372 cmd = SqlContext.GetCommand();
373 cmd.CommandText = @"select PID, weekdate, DemandQty from dbo.t_SalesForecast ORDER BY PID, WeekDate";
374 reader = cmd.ExecuteReader();
375 moreProducts=reader.Read();
376 //Set up the record for returning results
377 metadata[0] = new SqlMetaData( "Product",
378 SqlDbType.NVarChar,MAXNAME );
379 metadata[1] = new SqlMetaData( "Period", SqlDbType.DateTime );
380 metadata[2] = new SqlMetaData( "Quantity", SqlDbType.Int );
381 record = new SqlDataRecord( metadata );
382 while( moreProducts )
383 {
384 product_id = current_product = reader.GetInt32(0);
385 int index = 1;
386 quantity = new int[MAXWEEKS];
387 while( current_product == product_id )
388 {
389 week[index] = reader.GetSqlDateTime(1);
390 quantity[index] = reader.GetInt32(2);
391 index++;
392 moreProducts = reader.Read();
393 if( !moreProducts )
394 break;
395 current_product = reader.GetInt32(0);
396 }
397 //Determine the ordinal start week
398 startPeriod = 1;
399 //For each product ID calculate Cij
400 for( int i = startPeriod; i < (startPeriod + numWeeks); i++ )
401 {
402 for( int j = i+1; j <= (startPeriod + numWeeks+1); j++ )
403 {
404 Cij[i][j] = GetCij(quantity,i,j,K [product_count],h[product_count]);
405 }
406 }
407 //Calculate Fk
408 for( int k = startPeriod + numWeeks + 1; k >= startPeriod; k--)
409 {
410 minK[k] = GetFk_SO(k,startPeriod + numWeeks,Cij,Fk);
411 }
412 //Send the results
413 record.SetSqlChars(0,productNames[product_count]);
414 pipe.SendResultsStart(record,false);
415 for( int k = startPeriod; k < startPeriod + numWeeks; )
416 {
417 period = k;
418 optimal_j = minK[k];
419 sum = 0;
420 while( k < optimal_j )
421 {
422 sum = sum + quantity[k++];
423 }
424 values[1] = week[period];
425 record.SetValue(1,values[1]);
426 values[2] = sum;
427 record.SetValue(2,values[2]);
428 pipe.SendResultsRow(record);
429 }
430 pipe.SendResultsEnd();
431 product_count++;
432 }
433 reader.Close();
434 return 0;
435 }
436 private static int GetCij(int[] quantities, int i, int j, int K, int h)
437 {
438 if( j == i+1 )
439 return K;
440 else
441 return (j-1-i) * h * quantities[j-1] + GetCij(quantities, i, j-1,K,h);
442 }
443 private static int GetFk_SO(int k,int n,int[][] Cij, int[] Fk)
444 {
445 int j,min;
446 j = k+1;
447 min = j;
448 if ( k == n+1 )
449 {
450 Fk[k] = 0;
451 return j;
452 }
453 Fk[k] = Cij[k][j] + Fk[j];
454 for(; k <= n ;k++)
455 {
456 j = k + 1;
457 while( j <= n+1 )
458 {
459 if( Cij[k][j] + Fk[j] < Fk[k] )
460 {
461 min = j;
462 Fk[k] = Cij[k][j] + Fk[j];
463 }
464 j++;
465 }
466 }
467 return min;
468 }
469 }
470
471
472## 处理常见数据库编程任务和问题
473
474前一节在高层次上对基于 CLR 的编程与 T-SQL、中间层和扩展存储过程 (XP) 进行了比较。在这一节中,我们将考虑数据库应用程序开发人员经常遇到的一些编程任务和模型,并且讨论如何使用 CLR(以及在一些情况下如何不使用)进行处理。
475
476使用 Framework 库进行数据验证
477
478SQL Server 2005 中的 CLR 集成允许用户利用 .NET Framework 类库提供的丰富功能来解决其数据库编程问题。
479
480常规表达式的使用可以很好地说明 CLR 集成如何增强了验证和过滤功能。在处理数据库中存储的文本数据方面,常规表达式提供的模式匹配功能比通过 T-SQL 查询语言中的 LIKE 运算符可用的模式匹配功能多。考虑以下 C# 代码,它只是 System.Text.RegularExpressions 命名空间中的 RegEx 类的一个简单包装:
481
482
483 using System;
484 using System.Data.Sql;
485 using System.Data.SqlTypes;
486 using System.Text.RegularExpressions;
487 public partial class StringFunctions
488 {
489 [SqlFunction(IsDeterministic = true, IsPrecise = true)]
490 public static bool RegExMatch(string pattern, string matchString)
491 {
492 Regex r1 = new Regex(pattern.TrimEnd(null));
493 return r1.Match(matchString.TrimEnd(null)).Success;
494 }
495 [SqlFunction(IsDeterministic = true, IsPrecise = true)]
496 public static SqlString ExtractAreaCode(string matchString)
497 {
498 Regex r1 = new Regex("\((?<ac>[1-9][0-9][0-9])\)");
499 Match m = r1.Match(matchString);
500 if (m.Success)
501 return m.Value.Substring(1, 3);
502 else return SqlString.Null;
503 }
504 };
505
506
507假设 StringFunctions.RegExMatch 和 StringFunctions.ExtractAreaCode 方法已经被注册为带有 RETURNS NULL ON NULL INPUT 选项的数据库中的用户定义函数(这允许该函数在任何输入都为 NULL 时返回 NULL,这样在该函数内就没有特殊的 NULL 处理代码):
508
509现在,可以在使用上述代码的表的列中定义约束,以验证电子邮件地址和电话号码,如下所示:
510
511
512 create table Contacts
513 (
514 FirstName nvarchar(30),
515 LastName nvarchar(30),
516 EmailAddress nvarchar(30) CHECK
517 (dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu)',
518 EmailAddress) = 1),
519 USPhoneNo nvarchar(30) CHECK
520 (dbo.RegExMatch('\([1-9][0-9][0-9]\) [0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]',
521 UsPhoneNo)=1),
522 AreaCode AS dbo.ExtractAreaCode(UsPhoneNo) PERSISTED
523 )
524
525
526另外,请注意 AreaCode 列是使用 dbo.ExtractAreaCode 函数从 USPhoneNo 列中取出地区代码而得到的列。然后,可以对 AreaCode 列建立索引,这样便于在表格中根据特定地区代码查找联系人的查询。
527
528更一般地讲,此示例演示了如何利用 .NET Framework 库来增强带有有用函数的 T-SQL 内置函数库,这些有用函数很难用 T-SQL 表达。
529
530产生结果集
531
532需要从运行在服务器内的数据库对象(如存储过程或视图)中产生结果集可能是最常见的数据库编程任务之一。如果可以使用单个查询(SELECT 语句)来构建结果集,则这只需使用视图或在线表值函数即可实现。然而,如果需要多个语句(过程逻辑)来构建结果集,则有两个选择:存储过程和表值函数。虽然 SQL Server 2005 有表值函数,但是它们只能用 T-SQL 进行编写。在 SQL Server 2005 中,通过 CLR 集成,还可以使用托管语言来编写这样的函数。在这一节中,我们将讨论如何决定使用存储过程还是使用表值函数,以及使用 T-SQL 还是使用 CLR。
533
534从 T -SQL 过程可以将相关的结果作为表值函数的返回值返回,或者通过存储过程内曾经隐式存在的“调用者管道”返回。从存储过程的任何位置(不管执行的嵌套程度如何)执行 SELECT 语句都会把结果返回给调用者。更严格地讲,实际上 SELECT 语句并没有进行变量赋值。而且,FETCH、READTEXT、PRINT 和 RAISERROR 语句也隐式地将结果返回给调用者。
535
536请注意,“调用者”一直没有正确地定义,它实际上取决于存储过程的调用上下文。
537
538如果从任何客户端数据访问 API(如 ODBC、OLEDB 和 SQLClient)中调用存储过程,则调用者是实际的 API,并且它提供的任何一种抽象都可以表示结果(如 hstmt、IRowset 或 SqlDataReaderand)。这意味着,通常,从存储过程中产生的结果将始终返回到调用 API 中,而跳过堆栈中所有的 T-SQL 框架,如以下示例中所示:
539
540
541 create proc proc1 as
542 select col1 from dbo.table1;
543 create proc proc2 as
544 exec proc1;
545
546
547在执行过程 _proc2_ 时, _proc1_ 产生的结果将转到 _proc2_ 的调用者。 _proc2_ 中只有一种方法可以捕获产生的结果,即通过使用 INSERT/EXEC 将其存储到永久表、临时表或表变量中,从而将结果流式处理到磁盘。
548
549
550 create proc proc2 as
551 declare @t table(col1 int);
552 insert @t (col1) exec proc1;
553 -- do something with results
554
555
556在使用 INSERT/EXEC的情况下,“调用者”是 INSERT 语句的目标表/视图。
557
558SQL Server 2005 CLR 存储过程引入了新的“调用者”类型。当通过托管框架中的 in-proc 提供程序执行查询时,就可以通过 SqlDataReader 对象使结果可用,并且可以在存储过程中使用结果。
559
560
561 ...
562 SqlCommand cmd=SqlContext.GetCommand();
563 cmd.CommandText= "select col1 from dbo.table1";
564 SqlDataReader sdr=cmd.ExecuteReader();
565 while (sdr.Read())
566 {
567 // do something with current row
568 }
569 ...
570
571
572下面的问题是托管存储过程如何将结果返回给它的调用者而不是通过 SqlDataReader 来使用它。这可以通过称为 **SqlPipe** 的新类来实现。通过 **SqlContext** 类的静态方法可以使此类的实例对托管存储过程可用。 **SqlPipe** 有几种方法可以将结果返回给存储过程的调用者。这两个类都是在 Sqlaccess.dll 中定义的。
573
574SqlPipe
575
576在 SqlPipe 类中可以使用的方法中,最容易理解的就是 Execute 方法,它将命令对象作为参数接受。这个方法主要执行命令,并且没有使执行的结果可用于托管框架,而是将结果发送给存储过程的调用者。发送结果的这种形式在语义上与将语句嵌入 T-SQL 存储过程内是一样的。在本文前面描述的性能方面,SqlPipe.Execute 与 T-SQL 是等价的。
577
578
579 create proc proc1 as
580 select col1 from dbo.table1;
581 The equivalent in C# would be:
582 public static void proc1()
583 {
584 System.Data.SqlServer.SqlCommand cmd=SqlContext.GetCommand();
585 cmd.CommandText= "select col1 from dbo.table1";
586 SqlContext.GetPipe().Execute(cmd);
587 }
588
589
590对于返回的数据是由执行的查询直接产生的情况,SqlPipe.Execute 可以很好地工作。然而,在某些情况下可能希望1)从数据库中获得结果,进行操作或者转换,然后发送它们,或者 2)将结果发送回原地而不是本地 SQL Server 实例。
591
592SqlPipe 提供了一组可以协同工作以使应用程序可以将任何结果返回给调用者的方法:SendResultsStart、SendResultsRow 和 SendResultsEnd。在很大程度上,这些 API 类似于对扩展存储过程的开发人员可用的 srv_describe 和 srv_sendrow API。
593
594SendResultsStart 将 SqlDataRecord 作为参数接受,并且指示返回的新结果集的开头。该 API 从记录对象读取元数据信息,并且将其发送给调用者。该方法有重载,以允许发送元数据以及记录中的实际值。
595
596随后可以返回行,方法是对要发送的每行调用一次 SendResultsRowows。在发送完全部所需的行之后,需要调用 SendResultsEnd 来指示结果集的结尾。
597
598例如,下面的 C# 代码片段表示一个存储过程,它读取 XML 文档(来自 MSDN 的 Really Simple Syndication [RSS] 供给),使用 System.Xml 类进行解析,并且以相关的形式返回信息。请注意,这些代码应该创建为 EXTERNAL_ACCESS(或 UNSAFE)程序集,因为访问 Internet 所需的代码访问安全 (CAS) 权限只有在这些权限集中才是可用的。
599
600
601 // Retrieve the RSS feed
602 XPathDocument doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
603 XPathNavigator nav = doc.CreateNavigator();
604 XPathNodeIterator i = nav.Select("//item");
605 // create metadata for four columns
606 // three of them are string types and one of the is a datetime
607 SqlMetaData[] rss_results = new SqlMetaData[4];
608 rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
609 rss_results[1] = new SqlMetaData("Publication Date", SqlDbType.DateTime);
610 rss_results[2] = new SqlMetaData("Description", SqlDbType.NVarChar, 2000);
611 rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
612 // construct the record which holds metadata and data buffers
613 SqlDataRecord record = new SqlDataRecord(rss_results);
614 // cache a SqlPipe instance to avoid repeated calls to SqlContext.GetPipe()
615 SqlPipe sqlpipe = SqlContext.GetPipe();
616 // send the metadata, do not send the values in the data record
617 sqlpipe.SendResultsStart(record, false);
618 // for each xml node returned, extract four pieces
619 // of information and send back each item as a row
620 while (i.MoveNext())
621 {
622 record.SetString(0, (string)
623 i.Current.Evaluate("string(title[1]/text())"));
624 record.SetDateTime(1, DateTime.Parse((string)
625 i.Current.Evaluate("string(pubDate[1]/text())")));
626 record.SetString(2, (string)
627 i.Current.Evaluate("string(description[1]/text())"));
628 record.SetString(3, (string)
629 i.Current.Evaluate("string(link[1]/text())"));
630 sqlpipe.SendResultsRow(record);
631 }
632 // signal end of results
633 sqlpipe.SendResultsEnd();
634
635
636**注** **** 在 SendResultsStart 和 SendResultsEnd 调用之间,SqlPipe 被设置为繁忙状态,调用除 SendResultsRow 之外的任何 Send 方法都会导致错误发生。SqlPipe 处于繁忙状态中时,SendingResults 属性被设置为 TRUE。
637
638表值函数
639
640CLR 集成也启用了对用托管语言编写的表值函数 (TVF) 的支持。与 T-SQL 相似,TVF 主要用于返回表结果。最显著的不同在于,T-SQL 表值函数临时将结果存储在工作表中,而 CLR TVF 则能够对产生的结果数据进行流式处理。这意味着结果在从函数返回之前不需要物化。
641
642注 T-SQL 还具有内联 TVF 的概念,即不临时存储结果。内联 TVF 在大部分语义上便于指定子查询(可能带有参数)。
643
644托管 TVF 返回 ISqlReader 接口,这是由 SqlClient 和 SqlServer (in-proc) 托管提供程序中的 SqlDataReader 实现的一种只进光标抽象。查询处理器调用此接口上的 Read() 方法,以在每行返回 FALSE 之前获取它。
645
646将上面的示例改为返回来自 RSS 供给的信息,返回结果的代码如下所示(不包括未实现的方法):
647
648
649 [SqlFunction]
650 public static ISqlReader tvf1()
651 {
652 return (ISqlReader)new RssReader();
653 }
654 public class RssReader : ISqlReader
655 {
656 SqlMetaData[] rss_results = null;
657 XPathDocument doc;
658 XPathNavigator nav;
659 XPathNodeIterator i;
660 // Construct helper class, initializing metadata for the results
661 // reading from the RSS feed, creating the iterator
662 public RssReader()
663 {
664 rss_results = new SqlMetaData[4];
665 rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
666 rss_results[1] = new SqlMetaData("Publication Date",
667 SqlDbType.DateTime);
668 rss_results[2] = new SqlMetaData("Description", SqlDbType.NVarChar, 2000);
669 rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
670 // Retrieve the RSS feed
671 doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
672 nav = doc.CreateNavigator();
673 i = nav.Select("//item");
674 }
675 // # of columns returned by the function
676 public int FieldCount { get { return rss_results.Length; } }
677 // metadata for each of the columns
678 public SqlMetaData GetSqlMetaData(int FieldNo)
679 { return rss_results[FieldNo]; }
680 // Read method positions the navigator iterator on next element
681 public bool Read() { return i.MoveNext(); }
682 // methods to return each column
683 public Object GetValue(int FieldNo)
684 {
685 switch (FieldNo)
686 {
687 case 0:
688 return new SqlString((string)
689 i.Current.Evaluate("string(title[1]/text())"));
690 case 1:
691 return new SqlDateTime(DateTime.Parse(
692 (string)i.Current.Evaluate("string(pubDate[1]/text())")));
693 case 2:
694 return new SqlString((string)
695 i.Current.Evaluate("string(description[1]/text())"));
696 case 3:
697 return new SqlString((string)
698 i.Current.Evaluate("string(link[1]/text())"));
699 }
700 return null;
701 }
702 public string GetString(int i) { return (string)GetValue(i); }
703 public DateTime GetDateTime(int i) { return (DateTime)GetValue(i); }
704 public SqlChars GetSqlCharsRef(int i) {
705 return new SqlChars((SqlString)GetValue(i)); }
706 public SqlChars GetSqlChars(int i) {
707 return new SqlChars((SqlString)GetValue(i)); }
708 public SqlDateTime GetSqlDateTime(int i) {
709 return (SqlDateTime)GetValue(i); }
710 ...
711 }
712
713
714使用来自此表值函数的结果的一种简单查询如下所示:
715
716
717 select title, pubdate, description, link from dbo.GetRssFeed()
718
719
720显然可以用此数据的 TVF 形式来表示更丰富的查询。假定函数 CanonicalURL() 会返回规范的 URL 版本。现在,可以使用规范的 URL 很容易地返回来自 RSS 供给的数据:
721
722
723 select title, pubdate, description, dbo.CanonicalURL(link)
724 from dbo.tvf1()
725 order by pubdate
726
727
728请注意,在这个示例中,我们没有利用 TVF 的流化功能,因为我们正在使用整个 RSS 供给,在此之上建立导航器,然后在调用 Read() 时循环访问各个项目。然而,可以想象使用流式 API 来使用 Web 资源的结果,并且使用 XmlReader 来循环访问产生的 XML。需要重点注意的是,给出了 CLR 表值函数和 T-SQL 函数之间的执行模型的不同时,最主要的性能差异可能有利于 CLR TVF,特别是在可能将结果数据流化的情况下。
729
730使用哪一个?
731
732决定将存储过程与 SqlPipe (不管是 T-SQL 中使用隐式管道还是在托管中使用显式托管类)结合使用,还是使用表值函数,取决于以下几个必须考虑的因素:
733
734? |
735
736可组合性要求
737
738---|---
739? |
740
741返回的数据的源
742
743? |
744
745对副作用操作的需要
746
747? |
748
749强类型化和结果集的数量
750
751可组合性
752
753有时可能需要重用或进一步处理 TVF 或存储过程中产生的结果。从可组合性的角度来说,表值函数更灵活。TVF 的返回类型是相关的行集,可以用在允许此类构建的任何地方。特别是,它可以用在 SELECT 语句的 FROM 子句中,因为这些产生的结果可以受益于子查询中的 SELECT、INSERT/SELECT、派生的表和通用表表达式等的可组合性。
754
755另一方面,从 T-SQL 语言中,存储过程可以组合成 INSERT / EXEC 组合的唯一部分,这使得可以将产生的结果存储在永久或临时表中。INSERT 操作表示数据的实际副本,它可能会影响性能。
756
757如果需要组合和重用服务器中的结果,TVF 是更好的替代方法。如果产生的结果只需要回流到客户端/中间层,任何一种方法都可以完成这项工作。
758
759数据源
760
761返回的数据源是在基于 T-SQL 和基于 CLR 的实现之间做出决定的另一个重要因素。可以通过使用 in-proc 提供程序读取本地实例中的一些数据源产生结果,也可以从 SQL Server 之外的数据源产生结果。本文前面描述的基于 Web 请求的结果构造的代码片段便是后者的示例。另一个远程数据源的示例是,使用 SqlClient 托管提供程序从远程SQL Server 实例中检索结果。对于这样的外部源,基于 CLR 的实现是更好的选择,因为使用它可以很容易地实现访问外部数据的逻辑。
762
763现在让我们考虑这种情况,使用 in-proc 提供程序基于在本地实例中执行的查询生成结果。在使用 TVF 的情况下,默认的处理可能是返回由 in-proc 提供程序产生的 SqlDataReader,或者用 ISqlReader 的自定义实现包装这样的阅读器,以便在读取结果时对其进行转换。在使用存储过程的情况下,根据本地实例产生的结果必须执行查询,循环访问读取行,对结果执行一些操作,然后通过管道将其发送回去。
764
765然而,SQL Server 2005 不允许表值函数返回时请求还处于未决状态。在函数体可以返回之前,必须全部执行任何通过 in-proc 提供程序执行的查询并且完全使用结果。如果执行了返回语句,而 in-proc 提供程序中的 SqlDataReader 操作还处于未决状态,就会引发错误。这意味着对于从本地数据库实例返回数据的大多数情况,无法通过 CLR TVF 流化结果。如果因为其他因素(例如可组合性)需要将此编写为 TVF,则使用 T-SQL 编写是唯一的选择。另外,通过 SqlPipe 使用托管存储过程是一个可能的选择。
766
767请注意,对于基于来自本地实例的数据从存储过程中产生结果的情况,SendResultsXXX API 的使用只有在需要对结果进行修改或处理时才有意义。如果在未作修改的情况下将结果发送给调用者,则 SqlPipe.Execute 是更好的执行解决方案。
768
769副作用操作
770
771一般来说,不允许用户定义的函数(特别是表值函数)执行副作用操作。其中包括改变数据库状态的操作(如 DML 语句或事务处理操作)。在产生结果前后可能需要对系统状态作一些修改。例如,业务组件可能需要设置 SAVEPOINT 事务,执行一些 UPDATE,并且通过管道返回结果;但是如果出现错误,则回滚到 SAVEPOINT。
772
773如果不允许从 TVF 执行副作用操作,则只有存储过程才能实现这样的方案,并且必须通过 SqlPipe 返回结果。
774
775请注意,当 SqlPipe 忙于发送结果时,尤其不允许通过 in-proc 提供程序执行副作用操作。只有在完成结果集之前或之后允许进行这些操作。
776
777强类型化和返回的结果集的数量
778
779从上面的代码示例和与 T-SQL 一致的角度来看,由存储过程通过 SqlPipe 产生的结果的描述不同于 TVF。TVF 是强类型化的,并且作为注册 (CREATE FUNCTION) 语句的一部分,它必须静态地定义 TVF 产生的结果的列数和类型。
780
781另一方面,存储过程声明并没有声明产生的结果 - 甚至是否返回结果。这看起来可能很方便,虽然它确实提供了更大的灵活性,但是在编写执行存储过程的应用程序时要更加细心,因为存储过程可以动态地重定义产生的结果的形式。
782
783因此,自然而然建议根据元数据来描述结果:如果结果的架构需要根据调用的不同而变化,则只有 SqlPipe 才能提供这种灵活性。
784
785同样地,通过存储过程内的 SqlPipe 产生的结果的弱类型化不能将单个结果的架构扩展为可能返回可变数量的结果集。存储过程可以自由地根据条件确定是否发送给定的行集和定义其形式。这样的灵活性增加了使用这种可变的结果流的应用程序的复杂性开销。
786
787下表总结了如何在两者之间做出决定的指导原则:
788
789? |
790
791存储过程(使用隐式 SqlPipe 或基于显式 CLR 的方法)和 TVF
792
793---|---
794? |
795
796T-SQL 和 CLR
797
798表 4:关于生成结果集的指导原则
799---
800| 是 | 否
801
802**需要可组合性?**
803
804|
805
806TVF
807
808|
809
810TVF 过程或 TVF
811
812外部数据源(与只访问本地数据)?
813
814|
815
816CLR TVF 或 CLR 过程
817
818|
819
820(只访问本地数据)T-SQL TVF 或过程
821
822需要副作用?
823
824|
825
826过程
827
828|
829
830过程或 TVF
831
832固定的结果架构?
833
834|
835
836过程或 TVF
837
838|
839
840过程
841
842多个结果集?
843
844|
845
846过程
847
848|
849
850过程或 TVF
851
852流化结果的能力?
853
854|
855
856CLR TVF
857
858|
859
860T-SQL TVF
861
862对于本节的大部分内容,通过 SqlPipe 发送结果是与过程紧密相关的。即使在 CLR 触发器主体中 SqlPipe 是可用的并且返回结果是可能的,也很不提倡这种做法,因为使用在目标对象中定义的触发器发出数据处理语言 (DML) 或数据定义语言 (DDL) 语句可能会导致意外的结果。
863
864将标量分解为行
865
866经常需要在应用程序中传送多值参数。例如,在定单处理系统中,可能需要编写存储过程来将定单插入到 Orders 表中。存储过程中的参数之一可能是定单中的行项目。在这种情况下,您会遇到 T-SQL 限制,它不支持表值参数或缺乏集合数据类型(如数组)。解决这个问题的一种方法是,将集合编码为一个标量值(如 nvarchar 或 xml),然后将其作为参数传递给存储过程。在存储过程内,可以使用表值函数来接受标量输入,并将其转换成一组行,然后将这些行插入到 LineItems 表中。
867
868虽然可以用 T-SQL 编写表值函数,但是用 CLR 实现它有两个好处:
869
870? |
871
872System.Text 命名空间中的字符串处理函数使得编写表值函数更加容易。
873
874---|---
875? |
876
877CLR TVF 提供了更有效的流实现,这避免了将结果加载到工作表中。
878
879下面的代码片段显示了如何实现一个表值函数,它接受以‘;’分隔的一组值作为输入字符串,并且以一组行(字符串中的每个值一行)的形式返回该字符串。请注意, **MySqlReader** 类的构造函数实现了大部分工作,它使用 **System.String.Split** 方法将输入字符串分解为数组。
880
881
882 // TVF that cracks a ';' separated list of strings into a result
883 // set of 1 nvarchar(60)column called Value
884 public static ISqlReader GetStrings(SqlString str)
885 {
886 return (ISqlReader)new MySqlReader(str);
887 }
888 public class MySqlReader : ISqlReader
889 {
890 private string[] m_strlist;
891 private int m_iRow = -1; // # rows read
892 //The core methods
893 //Initialize list
894 public MySqlReader(SqlString str)
895 {
896 //Split input string if not database NULL;
897 //else m_strlist remains NULL
898 if (!str.IsNull)
899 {
900 m_strlist = str.Value.Split(';');
901 }
902 }
903 // SECTION: Metadata related: Provide #, names, types of
904 // result columns
905 public int FieldCount { get { return 1; } }
906 public SqlMetaData GetSqlMetaData(int FieldNo)
907 {
908 if (FieldNo==0)
909 return new SqlMetaData("Value", SqlDbType.NVarChar, 60);
910 else throw new NotImplementedException();
911 }
912 // SECTION: Row navigation. Read is called until it returns
913 // false. After each Read call, Get<typename> for each
914 // column is called.
915 public bool Read()
916 {
917 //Return empty result set if input is DB NULL
918 //and hence m_strlist is uninitialized
919 if (m_strlist==null) return false;
920 m_iRow++;
921 if (m_iRow == m_strlist.Length)
922 return false;
923 return true;
924 }
925 //Column getters
926 //Implement Get<sqltypename> for each column produced by
927 //the TVF; in this case just one.
928 public SqlChars GetSqlChars(int i)
929 {
930 if (i == 0)
931 return new SqlChars(m_strlist[m_iRow]);
932 else
933 throw new NotImplementedException();
934 }
935 //Methods not used by SqlServer omitted;
936 //Actual implementation should provide an empty
937 //implementation.
938 ...
939 } // public class MySqlReader
940 } // class StringFunctions;
941
942
943假定 **GetStrings** 方法注册为具有相同名称的 TVF。下面是存储过程的代码片段,它使用此 TVF 从定单中提取表形式的行项目。
944
945
946 CREATE PROCEDURE Insert_Order @cust_id int, @lineitems
947 nvarchar(8000)
948 AS
949 BEGIN
950 ...
951 INSERT LineItems
952 SELECT * FROM dbo.GetStrings(@lineitems)
953 ...
954 END
955
956
957对数据进行自定义聚合
958
959在许多情况下,您可能需要对数据进行聚合。这包括执行统计计算(如 avg、stddev 等等)。如果所需的聚合函数不是作为内置聚合函数直接支持的,SQL Server 2005 中有三种方法可以进行这样的自定义聚合:
960
961? |
962
963将聚合编写为用户定义的聚合 (UDA)。
964
965---|---
966? |
967
968使用 CLR 存储过程编写聚合。
969
970? |
971
972使用服务器端光标。
973
974让我们在一个称为 PRODUCT(int) 的简单聚合函数的上下文中检查这三种替代方法,该聚合函数计算一组给定值的乘积。
975
976作为用户定义的聚合函数实现的 PRODUCT
977
978下面是此函数的主干 C# 代码示例。所有的积累逻辑都在 Accumulate 函数中(为了简单起见,其他函数显示为 {...})。
979
980
981 [SqlUserDefinedAggregate(Format.Native)]
982 public struct Product
983 {
984 public void Accumulate(SqlInt32 Value)
985 {
986 m_value *= Value;
987 }
988 public void Init() {...}
989 public void Merge(Product Group) {...}
990 public SqlInt32 Terminate() {...}
991 }
992
993
994在定义类型、创建程序集和注册到 SQL Server 之后,就可以通过以下方式使用 T-SQL中的聚合函数:
995
996
997 SELECT dbo.Product(intcol)
998 FROM tbl
999 GROUP BY col
1000
1001
1002作为使用 SqlDataReader 的托管存储过程实现的 PRODUCT
1003
1004可以创建存储过程来执行查询和循环访问结果,以执行计算。这种循环访问是通过使用 SqlDataReader 类完成的。
1005
1006
1007 [SqlProcedure]
1008 public static void Product(out SqlInt32 value)
1009 {
1010 SqlCommand cmd = SqlContext.GetCommand();
1011 cmd.CommandText = "select intcolumn from tbl";
1012 SqlDataReader r = cmd.ExecuteReader();
1013 bool first = true;
1014 using (r)
1015 {
1016 while (r.Read()) //skip to the next row
1017 {
1018 if (first)
1019 {
1020 value = r.GetSqlInt32(0);
1021 first = false;
1022 }
1023 else
1024 {
1025 value *= r.GetSqlInt32(0);
1026 }
1027 }
1028 }
1029 }
1030
1031
1032可以使用 EXEC 语句来调用这一过程
1033
1034
1035 EXEC Product @p OUTPUT
1036
1037
1038作为使用光标的 T-SQL 存储过程实现的 PRODUCT
1039
1040可以创建 T-SQL 存储过程来执行查询和通过使用 T-SQL 光标循环访问结果,以执行计算。
1041
1042
1043 create procedure TSQL_ProductProc (@product int output)
1044 as
1045 begin
1046 declare @sales int
1047 declare c insensitive cursor for select intcolumn from tbl
1048 open c
1049 fetch next from c into @sales
1050 if @@FETCH_STATUS = 0
1051 set @product = @sales
1052 while @@FETCH_STATUS = 0
1053 begin
1054 fetch next from c into @sales
1055 set @product = @product * @sales
1056 end
1057 close c
1058 deallocate c
1059 end
1060
1061
1062决定是使用 UDA 还是使用其他某种解决方案来产生结果取决于几个因素:
1063
1064? |
1065
1066可组合性要求。UDA 实际上是独立的对象,可以用于任何 T-SQL 查询,通常用在可以使用系统聚合函数的任何相同的地方。不需要假定它所操作的查询。例如,可以将其包括在视图定义(不过,索引视图中不支持 UDA)和标量子查询中。
1067
1068---|---
1069? |
1070
1071聚合算法细节。在 Order By 子句(如果查询中有)之前可能对 UDA 进行求值,因此不能保证传递给聚合函数的值的顺序。如果聚合算法需要按照特定的顺序使用值,则不能使用 UDA。同样地,UDA 从整组中使用值并且返回单一值。如果需要必须为组中的每个值返回值的聚合函数,则应该考虑使用存储过程或流表值函数来编写您的函数。详细信息请参见本文中的“产生结果”一节。
1072
1073? |
1074
1075对副作用和数据访问的需要。不允许 UDA 进行数据访问或有副作用。如果您的函数需要保留大量的数据作为聚合的中间状态,或因为其他某种原因需要进行数据访问,则必须使用过程。
1076
1077使用 UDA 的第一种方法在这三个选择中可能提供最好的性能。通常,如果没有碰到上面所列的限制,就应该尝试将聚合函数编写为 UDA。如果无法使用 UDA 方法,则使用 SqlReader 的托管代码方法可能比 T-SQL 光标方法执行得更好。
1078
1079可以用 UDA 方法编写的有用的聚合的示例还包括:找到每组中第 N 大(或第 N 小)值,找到每组中前 N 个最大值的平均值或总和,等等。
1080
1081## 用户定义的类型 (UDT)
1082
1083现在,我们来讲 SQL Server 2005 中功能更强大但是经常被错误理解的一个功能。使用用户定义的类型 (UDT),可以扩展数据库的标量类型系统(不仅仅为系统类型定义您自己的别名,这在 SQL Server 以前的版本中一直可用)。定义 UDT 就像用托管代码编写类,创建程序集,然后使用“create type”语句在 SQL Server 中注册该类型一样简单。下面是实现 UDT 的主干代码:
1084
1085
1086 [SqlUserDefinedTypeAttribute(Format.Native)]
1087 public struct SimpleUdt: INullable
1088 {
1089 public override string ToString() {...}
1090 public bool IsNull { get; }
1091 public static SimpleUdt Null { get; }
1092 public static SimpleUdt Parse(SqlString s) {...}
1093 ...
1094 }
1095 create type simpleudt from [myassembly].[SimpleUdt]
1096 create table t (mycolumn simpleudt)
1097
1098
1099何时创建 UDT
1100
1101SQL Server 2005 中的 UDT 不是对象相关的扩展性机制。它们是扩展数据库的标量类型系统的一种方法。标量类型系统包括 SQLServer 附带的列类型(如 int、nvarchar 和 uniqueidentifier 等类型)。例如,使用 UDT,可以定义您自己的、用于列定义的类型。如果您的类型确实是一个适合建模为列的原子值,请创建 UDT。
1102
1103如果需要定义您自己的标量类型,请使用 UDT。这种类型的示例情况包括各种日历中的自定义日期/时间数据类型和货币数据类型。使用 UDT,可以创建单个对象来公开类型上可用的所有行为,并且封装或隐藏该类型所存储的基础数据。需要访问数据的每个人都必须使用 UDT 的编程接口。如果能够利用 .NET Framework 中现有的功能(如国际化或日历功能),这实际上又是考虑将类型实现为 UDT 的一个很好的理由。
1104
1105何时不创建 UDT
1106
1107不要使用 UDT 来对复杂的业务对象(如雇员、联系人或客户)进行建模。您将会陷入 UDT 的所有列限制(如,8KB 大小限制、索引</sqltypename></typename></ac></maxweeks;l++)></column-name></complex-calculation></table></column-name></complex-calculation>