2.2 标量UDF
标量UDF返回单个（标量）值。可以在允许使用标量表达式（scalar expression）的地方使用标量UDF。例如查询、约束、计算列等。标量UDF要求满足下面这几个语法要求：
n 必须包含BEGIN/END块以定义函数的主体。
n 调用时必须限定架构（除非像存储过程调一样被EXEC调用，如EXEC myFunction 3, 4）。
n 调用时不允许忽略可选参数（有默认值的参数）；或者，至少为它们指定DEFAULT关键字。
下面介绍T-SQL和CLR UDF。
T-SQL标量UDF
当操作的主要成本来自于基于集合的数据操作而不是过程逻辑和计算时，T-SQL UDF通常要比CLR UDF快。不仅UDF是这样，其他类型的程序也是如此。创建UDF时，在函数头指定名称、定义输入参数、定义返回值的数据类型。作为一个标量UDF的示例，下面的代码创建fn_ConcatOrders函数，它接收客户ID作为输入并返回串联该客户的OrderID得到的字符串。
SET NOCOUNT ON;
USE Northwind;
GO
IF OBJECT_ID('dbo.fn_ConcatOrders') IS NOT NULL
DROP FUNCTION dbo.fn_ConcatOrders;
GO
CREATE FUNCTION dbo.fn_ConcatOrders
(@cid AS NCHAR(5)) RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @orders AS VARCHAR(8000);
SET @orders = '';
SELECT @orders = @orders + CAST(OrderID AS VARCHAR(10)) + ';'
FROM dbo.Orders
WHERE CustomerID = @cid;
RETURN @orders;
END
GO
函数先声明变量 @orders并初始化为空字符串。函数中的查询使用了一个特殊的SELECT赋值语法。它扫描符合条件的行，每一行都为 @orders变量赋值。该值是通过串联最新的 @orders内容和当前行的OrderID值并用一个分号作为分隔符得到的。
重要 该查询不保证串联的顺序。OrderID将按SQL
Server扫描数据的物理顺序进行串联。在这样的查询中，不要依赖ORDER BY子句。如果你指定了ORDER BY子句，SQL
Server不会产生错误，但它不保证在赋值前对数据排序。
同样，微软也没有正式的文档描述聚合串联（aggregate concatenation）技术（无论是否使用ORDER
BY子句）。这里所描述的行为仅仅是基于观察得到的，我还未发现不使用ORDER
BY时会失败。但要记住，官方不保证说所有符合条件行的元素都会被串联，所以你最好避免在产品代码中完全依赖这项技术。
要测试fn_ConcatOrders函数，运行下面的查询，它生成的输出以简化形式显示在表6-1中。
SELECT CustomerID, dbo.fn_ConcatOrders(CustomerID) AS Orders
FROM dbo.Customers;
表6-1 为每个客户串联订单ID个得到的结果(被简化)
|
CustomerID |
Orders |
|
ALFKI |
10643;10692;10702;10835;10952;11011; |
|
ANATR |
10308;10625;10759;10926; |
|
ANTON |
10365;10507;10535;10573;10677;10682;10856; |
|
AROUT |
10355;10383;10453;10558;10707; 10741;10743;10768;10793;10864;10920; 10953;11016; |
|
BERGS |
10278;10280;10384;10444;10445;10524;10572;10626; 10654;10672;10689;10733;10778;10837; 10857;10866;10875;10924; |
|
BLAUS |
10501;10509;10582;10614;10853;10956;11058; |
|
BLONP |
10265;10297;10360;10436;10449; 10559;10566;10584;10628;10679;10826; |
|
BOLID |
10326;10801;10970; |
|
BONAP |
10331;10340;10362;10470;10511;10525;10663;10715; 10730;10732;10755;10827;10871;10876;10932;10940;11076; |
|
BSBEV |
10289;10471;10484;10538; 10539;10578;10599;10943;10947;11023; |
|
… |
… |
在SQL Server 2005中，你不必再使用这种方法实现字符串串联。你可以使用FOR XML PATH选项，并用一个空字符串作为其输入，就像这样：
SET NOCOUNT ON;
USE Northwind;
GO
SELECT CustomerID,
(SELECT CAST(OrderID AS VARCHAR(10)) + ';' AS [text()]
FROM dbo.Orders AS O
WHERE O.CustomerID = C.CustomerID
ORDER BY OrderID
FOR XML PATH('')) AS Orders
FROM dbo.Customers AS C;
这样你就可以完全控制串联的顺序了。
在SQL Server 2005中使用用户定义聚合函数（UDA）也可以解决这个问题。但使用UDA你将无法控制串联的顺序，且串联的字符串被限制为8000个字节。关于UDA的更多信息，请参考Inside T-SQL Querying。
完成后，运行下面的代码进行清理：
IF OBJECT_ID('dbo.fn_ConcatOrders') IS NOT NULL
DROP FUNCTION dbo.fn_ConcatOrders;
性能问题
当你把外部表的属性作为输入提供给标量UDF时，在查询中调用这个函数的成本非常高，你应该清楚这一点。甚至当函数只包含一个带有标量表达式的returnbb 子句时，它也不是内联（inline）函数。逐行调用该函数的开销会导致非常高的成本。运行一个简单的性能测试就可以发现，相对于内联表达式（inline expression），在查询中使用UDF的成本非常高。
在运行性能测试之前，要确保在数据库中已经包含了Nums表。我在第1章提供了创建和填充Nums表的代码。在SQL Server Management Studio （SSMS）中启用执行后放弃结果选项，这样你的统计结果将不包含生成输出所花费的时间。
先从Nums中查询一百万行，并使用一个n加1的内联表达式。
SELECT n, n+1 AS n2 FROM dbo.Nums WHERE n <= 1000000;
第一次执行代码时可能要扫描物理数据。数据被加载到缓存后，再次运行该查询并统计运行时间。在我的系统上运行该代码时，它不到一秒钟就完成了。
接下来创建fn_add1 UDF：
IF OBJECT_ID('dbo.fn_add1') IS NOT NULL
DROP FUNCTION dbo.fn_add1;
GO
CREATE FUNCTION dbo.fn_add1(@i AS INT) RETURNS INT
AS
BEGIN
RETURN @i + 1;
END
GO
使用fn_add1运行该查询：
SELECT n, dbo.fn_add1(n) AS n2 FROM dbo.Nums WHERE n <= 1000000;
查询耗时3秒钟。
后面这个查询的高成本与每次函数调用的开销有关。你可以在执行查询时运行SQL Server Profiler来跟踪SP:Completed（或 SP:Starting）事件，以观察对UDF的多次调用。
为限制跟踪的大小，你可以只测试几行。例如，使用筛选器n<=10。图6-1显示了我在跟踪查询时得到的事件。
图6-1 Profiler跟踪对标量UDF的多次调用
这时，关闭SSMS中的执行后放弃结果选项。
在代码简单性和维护性方面，使用UDF有诸多好处，不过在性能方面，如果你设法在查询中使用内联表达式实现计算并避免使用UDF，通常会更好些。有时，一些看起来需要循环或过程逻辑的计算居然也可以使用内联表达式实现，你也许会对此感到很吃惊。
例如，下面的查询统计变量 @find中的字符串在Customers. CompanyName列中出现的次数。
DECLARE @find AS NVARCHAR(40);
SET @find = N'n';
SELECT CompanyName,
(LEN(CompanyName+'*') - LEN(REPLACE(CompanyName, @find, '')+'*'))
/ LEN(@find) AS Cnt
FROM dbo.Customers;
表6-2 以简化形式显示了查询所生成的结果
|
CompanyName |
Cnt |
|
Alfreds Futterkiste |
0 |
|
Ana Trujillo Emparedados y helados |
1 |
|
Antonio Moreno Taquería |
3 |
|
Around the Horn |
2 |
|
Berglunds snabbköp |
2 |
续表
|
CompanyName |
Cnt |
|
Blauer Saee Delikatessen |
1 |
|
Blondesddsl père et fils |
1 |
|
Bólido Comidas preparadas |
0 |
|
Bon app' |
1 |
|
Bottom-Dollar Markets |
0 |
|
… |
… |
该表达式使用REPLACE函数计算合计数。它的逻辑是这样的，先把字符串中的所有 @find都删除（也就是用‘’替换掉），然后检查得到的字符串比原来短多少，这样就可以计算出 @find在字符串中出现的次数。需要注意的是，在计算它们的长度时为每个字符串添加一个“*”以避免当字符串包含尾随空格时得到错误的长度。
在约束中使用UDF
标量UDF可以用于约束。这一节将讨论并演示如何在DEFAULT、CHECK、PRIMARY KEY和UNIQUE约束中使用UDF。
DEFAULT约束
标量UDF可以用在DEFAULT约束中，但它有一个限制，当UDF用于DEFAULT约束时不能接收该表的列作为输入参数。代码清单6-1中的代码创建表T1和名为fn_T1_getkey的UDF，它返回T1中缺少的最小键值。
代码清单6-1 创建表T1 和 fn_T1_getkey UDF
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
keycol INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY CHECK (keycol > 0),
datacol VARCHAR(10) NOT NULL
);
GO
IF OBJECT_ID('dbo.fn_T1_getkey') IS NOT NULL
DROP FUNCTION dbo.fn_T1_getkey;
GO
CREATE FUNCTION dbo.fn_T1_getkey() RETURNS INT
AS
BEGIN
RETURN
CASE
WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1 ELSE (SELECT MIN(keycol + 1)
FROM dbo.T1 AS A
WHERE NOT EXISTS
(SELECT *
FROM dbo.T1 AS B
WHERE B.keycol = A.keycol + 1))
END;
END
GO
下面的代码向keycol列添加一个DEFAULT约束，该约束调用fn_T1_getkey函数：
ALTER TABLE dbo.T1 ADD DEFAULT（dbo.fn_T1_getkey()） FOR keycol;
注意
这个DEFAULT约束只在插入单行时执行，插入多行时不会执行。在实际的业务方案中重用键值非常不明智。我使用这个例子只是为了演示而已。
下面的代码先向表中插入三行，生成的键值为1、2、3，然后删除键值为2的行，再插入一个新行，并生成键值2
INSERT INTO dbo.T1(datacol) VALUES('a');
INSERT INTO dbo.T1(datacol) VALUES('b');
INSERT INTO dbo.T1(datacol) VALUES('c');
DELETE FROM dbo.T1 WHERE keycol = 2;
INSERT INTO dbo.T1(datacol) VALUES('d');
使用下面的代码查询该表，如表6-3所示，键值2被赋给最后插入的行(datacol=‘d’)，因为键值为2的行之前被删除：
SELECT * FROM dbo.T1;
表6-3 T1 的内容
|
Keycol |
datacol |
|
1 |
a |
|
2 |
d |
|
3 |
C |
CHECK约束
不同于在DEFAULT约束中使用的UDF，在CHECK约束中使用UDF时可以引用表中的列作为输入。在CHECK约束中使用UDF可以实现功能强大的强制完整性规则（integrity rule），这样你就可以在某些情况下避免使用通常成本更高的触发器。在本章的后面，我将演示如何在CHECK约束中使用UDF，示例中的UDF基于正则表达式匹配输入的字符串。
PRIMARY KEY 和 UNIQUE 约束
你可以在调用UDF的计算列上创建UNIQUE 或 PRIMARY KEY约束。这两个约束会在后台创建唯一索引。这意味着目标计算列和它调用的UDF必须满足索引准则（indexing guideline）。例如，UDF必须绑定到架构（使用SCHEMABINDING选项创建），计算列必须具有确定性且是精确的，或者具有确定性且是持久的，等等。你可以在联机丛书中找到关于计算列和UDF索引准则的更详细信息。
下面的代码尝试向T1中添加一个调用fn_add1 UDF的计算列col1，并在该列上创建一个UNIQUE约束。
ALTER TABLE dbo.T1
ADD col1 AS dbo.fn_add1(keycol) CONSTRAINT UQ_T1_col1 UNIQUE;
该操作将失败并返回下面的错误：
Msg 2729, Level 16, State 1, Line 1
Column 'col1' in table 'dbo.T1' cannot be used in an index or statistics or as a partition
key because it is non-deterministic.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
产生该错误的原因是该函数不满足创建索引的必要条件之一，即函数必须绑定到架构。如你所见，该错误消息本身并未指出该错误产生的原因或建议你如何避免该错误。要解决该问题，你应该为该函数增加SCHEMABINDING选项。
ALTER FUNCTION dbo.fn_add1(@i AS INT) RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN @i + 1;
END
GO
再次尝试添加包含UNIQUE约束的计算列，这一次代码可以正确运行。
ALTER TABLE dbo.T1
ADD col1 AS dbo.fn_add1(keycol) CONSTRAINT UQ_T1_col1 UNIQUE;
要在这样的计算列上创建PRIMARY KEY约束需要一点技巧。我们来看如何实现，先从T1中删除已存在的PRIMARY KEY。
ALTER TABLE dbo.T1 DROP CONSTRAINT PK_T1;
然后尝试添加另一个包含PRIMARY KEY约束的计算列col2。
ALTER TABLE dbo.T1
ADD col2 AS dbo.fn_add1(keycol)
CONSTRAINT PK_T1 PRIMARY KEY;
该尝试将失败，并产生下面的错误：
Msg 1711, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on column 'col2' in table 'T1'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
你必须明确保证col2永远不会出现NULL值。为此，你可以把列定义为PERSISTED 和 NOT NULL，就像这样：
ALTER TABLE dbo.T1
ADD col2 AS dbo.fn_add1(keycol) PERSISTED NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY;
PERSISTED选项是SQL Server 2005中新增的。在SQL Server 2000中，要在计算列上创建主键约束，你必须用ISNULL函数封装对UDF的调用，就像这样：
ALTER TABLE dbo.T1
ADD col2 AS ISNULL(dbo.fn_add1(keycol), 0)
CONSTRAINT PK_T1 PRIMARY KEY;
完成后，运行下面的代码进行清理。
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
IF OBJECT_ID('dbo.fn_T1_getkey') IS NOT NULL
DROP FUNCTION dbo.fn_T1_getkey;
GO
IF OBJECT_ID('dbo.fn_T1_datacol_count') IS NOT NULL
DROP FUNCTION dbo.fn_T1_datacol_count;
CLR 标量 UDF
这一节介绍CLR标量UDF并比较它和T-SQL UDF之间的关系。附录A中提供了你在开发、生成、部署和测试CLR程序时应该遵循的操作指南。在本章以及其他描述CLR程序的章节中，我将只关注程序代码本身。附录中提供了命名空间定义和CLRUtilities类。下面是C# 版的命名空间定义和CLRUtilities类的开始部分。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;
public partial class CLRUtilities
{
... 这里是程序定义 ...
}
下面是Visual Basic版本的代码：
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text
Imports System.Text.RegularExpressions
Imports System.Collections
Imports System.Collections.Generic
Imports System.Diagnostics
Imports System.Reflection
Imports System.Runtime.InteropServices
Partial Public Class CLRUtilities
... 这里是程序定义...
End Class
我不会在这里重复命名空间和类的定义。也不会提供所涉及的通用步骤，这些在附录中已经非常详细了。
CLR程序
在SQL Server中开发CLR程序的能力给你提供了强大的功能，但同时它也带来了极大的风险。 .NET包含了丰富的开发技术以及在某些领域比T-SQL更高的性能，T-SQL永远不会被设计用于高效地处理这些领域。这些领域包括复杂计算、遍历和过程逻辑、字符串处理、对操作系统资源的外部访问等等。T-SQL是一种声明式语言（declarative language）。当使用基于集合的查询进行数据操作时，它比 .NET拥有更强大的功能和更高的性能。集成 .NET的危险在于，对于那些还未具备SQL思维方式的开发人员来说它也是一个媒介，会导致他们开发出性能糟糕的代码。在本书中，我将提供一些应该使用 .NET开发的示例。
更多信息 关于基于集合的查询和高效的基于集合解决方案的信息，请参考Inside T-SQL
Querying一书，该书更为深入的介绍了这些主题。
正则表达式
正则表达式为你提供了一种功能强大的方法，可以利用一些简明灵活的符号匹配文本的模式（patterns of text）。ANSI SQL定义了SIMILAR TO谓词以支持正则表达式，不幸的是，SQL Server 2005并未在T-SQL中实现该谓词。但你可利用 .NET代码中的正则表达式。例如，下面的C# 代码定义了一个名为fn_RegExMatch的函数。
// 使用正则表达式验证输入的字符串
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlBoolean fn_RegExMatch(SqlString inpStr,
SqlString regExStr)
{
if (inpStr.IsNull || regExStr.IsNull)
return SqlBoolean.Null;
else
return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value ,
RegexOptions.CultureInvariant);
}
方法头中的属性通知SQL Server该方法具有确定性并且不涉及数据访问。注意代码使用了RegexOptions.CultureInvariant选项以获得文化中立（culture-independent）的匹配结果。如果匹配是文化依赖（culture-dependent）的，该方法将不具有确定性。（详情请参见http://msdn2.microsoft.com/en-us/library/z0sbec17.aspx）。
该函数接收一个字符串（inpStr）和一个正则表达式（regExStr）作为输入。函数的返回类型是SqlBoolean，它有三种可能的值：0、1和Null。如果regExStr或inpStr是Null则返回值为Null，如果在inpStr中找到regExStr模式则返回1，则否返回0。如你所见，该函数的代码非常简单。代码首先测试输入参数是否为NULL，如果有一个是NULL则返回NULL。如果输入参数都不为NULL，则函数返回RegEx.IsMatch方法的结果。这个方法检查第一个参数所提供的字符串是否包含第二个参数中提供的模式。RegEx.IsMatch方法返回 .NET的System.Boolean值，该值必须显式转换为SqlBoolean。
也许你更喜欢使用Visual Basic，下面就是使用Visual Basic实现的函数代码。
' 使用正则表达式验证输入的字符串
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_RegExMatch(ByVal inpStr As SqlString, _
ByVal regExStr As SqlString) As SqlBoolean
If (inpStr.IsNull Or regExStr.IsNull) Then
Return SqlBoolean.Null
Else
Return CType(Regex.IsMatch(inpStr.Value, regExStr.Value, _
RegexOptions.CultureInvariant), SqlBoolean)
End If
End Function
如果你遵循了附录A中描述的指导，那么你已经作好了测试和使用该函数的准备了。这些指导包含：在SQL Server中激活CLR（默认是禁用的）、创建名为CLRUtilities的测试数据库，在Microsoft Visual Studio 2005中开发代码，在磁盘上生成 .dll程序集、从程序集中把Intermediate Language（IL）代码加载到SQL Server数据库，并在数据库中注册程序集中的程序。运行下面的代码来激活SQL Server中的CLR并创建CLRUtilities测试数据库。
SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
IF DB_ID('CLRUtilities') IS NOT NULL
DROP DATABASE CLRUtilities;
GO
CREATE DATABASE CLRUtilities;
GO
USE CLRUtilities;
GO
注意 开启'clr
enabled'服务配置选项（默认是禁用的）后，就表示Microsoft SQL
Server可以在实例级别上运行用户和序集。你不能在更细的粒度级别上控制该选项，所以，激活该选项，你就为整个SQL
Server实例上激活了该选项。激活该选项可能会导致安全风险。风险的级别取决于你允许每个程序执行什么操作。当使用CREATE
ASSEMBLY命令创建程序集时，通过把PERMISSION_SET选项设置为SAFE、 EXTERNAL_ACCESS 或
UNSAFE可以控制代码的访问权限。下面是联机丛书描述这三个选项时的安全说明：
对于执行计算和数据管理任务而无需访问 SQL Server 实例外部资源的程序集，SAFE 是推荐的权限集。
对于访问 SQL Server 实例外部资源的程序集，我们推荐使用 EXTERNAL_ACCESS。EXTERNAL_ACCESS
程序集包含 SAFE 程序集的可靠性和可伸缩性保护，但从安全角度而言，它与 UNSAFE
程序集类似。原因是在默认情况下，EXTERNAL_ACCESS 程序集中的代码以 SQL Server
服务账户身份运行并访问此账户的外部资源，除非此代码显式模拟调用方。因此，创建 EXTERNAL_ACCESS 程序集的权限应只授予以
SQL Server 服务账户身份运行代码的可信登录。有关模拟的详细信息，请参阅 CLR Integration
Security。
指定 UNSAFE 将使程序集的代码在 SQL Server 进程空间中执行任意操作，这些操作有可能破坏SQL
Server的健壮性。UNSAFE 程序集还可能会破坏 SQL Server 或Common Language
Runtime的安全系统。UNSAFE 权限只应授予高度可信的程序集。只有 sysadmin
固定服务器角色的成员才能创建并修改UNSAFE 程序集。
本章所讨论的函数都不需要访问外部资源，所以创建程序集时将使用SAFE权限集（permission set）。在下一章中，我将演示一个需要EXTERNAL_ACCESS权限集的存储过程和一个需要UNSAFE权限集的触发器，但只是出于演示目的。演示这些程序时，我将修改该程序集以支持所需要的权限集。允许程序集进行外部访问会带来安全风险，牢记这一点。
在后面的所有的示例中，我都假设已存在CLRUtilities数据库并且已经使用Visual Studio生成程序集。如果你还没有把程序集加载到数据库，运行下面的代码完成该操作。
USE CLRUtilities;
GO
CREATE ASSEMBLY CLRUtilities
FROM 'C:\CLRUtilities\CLRUtilities\bin\Debug\CLRUtilities.dll'
WITH PERMISSION_SET = SAFE;
-- 如果没有Debug 文件夹，则使用这个地址:
-- FROM 'C:\CLRUtilities\CLRUtilities\bin\CLRUtilities.dll'
当然，如果包含程序集的CLRUtilities.dll文件位于另一个文件夹，则替换为它所在的文件夹。CREATE ASSEMBLY 命令把IL代码从 .dll文件加载到数据库。一旦加载，你就不再需要外部文件了。要注意的是，如果你添加程序后重新生成了程序集，而且在Visual Studio Professional版中未选择自动部署选项，你需要执行ALTER ASSEMBLY 或 DROP 和CREATE ASSEMBLY命令，手工把IL代码重新加载到数据库。如果你遵循了附录A中的指导而且已经创建了本书中讨论的所有程序，可以省略这一步骤。以后我不再提及这一步。
在讨论新的程序时，我会提供在数据库注册它们所需的T-SQL代码（CREATE FUNCTION | PROCEDURE | TRIGGER命令），尽管如果你完全遵循附录A中的指导，实际上并不需要运行这些代码。
下面是你需要在CLRUtilities数据库中注册C# 版fn_RegExMatch所需的代码。
USE CLRUtilities;
GO
IF OBJECT_ID('dbo.fn_RegExMatch') IS NOT NULL
DROP FUNCTION dbo.fn_RegExMatch;
GO
CREATE FUNCTION dbo.fn_RegExMatch
(@inpstr AS NVARCHAR(MAX), @regexstr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_RegExMatch;
下面是注册 Visual Basic版fn_RegExMatch的代码。
CREATE FUNCTION dbo.fn_RegExMatch
(@inpstr AS NVARCHAR(MAX), @regexstr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_RegExMatch;
注意 在注册C# 版本和Visual
Basic版本的函数时，为其指定了不同的EXTERNALNAME (CLRUtilities
CLRUtilities.fn_RegExMatch和CLRUtilities.[CLRUtilities.
CLRUtilities].fn_RegExMatch)。这一点非常另人迷惑。在之前的
.NET版本中(2002,2003)，当你创建了一个类库时，C# 会用和类相同的名称添加根命名空间(root
namespace)，而Visual Basic不是这样。这就导致了不同的结果：Visual Basic创建了一个根命名空间，而C#
不会。为保证使用不同 .NET语言时T-SQL代码的一致性，在使用Visual Basic编程时必须避免创建根命名空间。在Visual
Studio中，右键单击该项目，选择属性、应用程序页。清空“根命名空间”文本框的内容。在本书中，我将假设你未清空该选项，因此，当注册对象时你会看到指定的外部名称的差异。
这样你就可以使用fn_RegExMatch函数了。
更多信息
你可以在互联网上找到更多有用的正则表达式。例如：http://www.regexlib. com。
作为一个使用这个新函数的示例，假设你想检查一个E-mail地址是否有效，你可以使用这个正则表达式：N'^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$'.
该正则表达式检查地址是否以一个单词为开头，包含“at”（@）符号，而且在@符号之后至少有两个单词，单词之间用点(.)分隔。在@符号的前面和后面，可以包含其他一些以点分隔的单词。这个正则表达式非常简单，提供该正则表达式只是为了演示，要学习如何编写更健壮更完整的正则表达式，建议你访问http://www.regularexpressions.info/。
下面的代码返回1，因为它所提供的E-mail地址是有效的。
SELECT dbo.fn_RegExMatch(
N'dejan@solidqualitylearning.com',
N'^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$');
而下面的代码将返回0，因为该地址无效。
SELECT dbo.fn_RegExMatch(
N'dejan#solidqualitylearning.com',
N'^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$');
你还可以在CHECK约束中使用该函数。例如，下面的代码会创建一个使用CHECK约束的TestRegEx表，该约束限制了jpgfilename列的值必须是以jpg为扩展名的文件名。
IF OBJECT_ID('dbo.TestRegEx') IS NOT NULL
DROP TABLE dbo.TestRegEx;
GO
CREATE TABLE dbo.TestRegEx
(
jpgfilename NVARCHAR(4000) NOT NULL
CHECK(dbo.fn_RegExMatch(jpgfilename,
N'^(([a-zA-Z]:)|(\\{2}\w+)\$?)(\\(\w[\w ]*.*))+\.(jpg|JPG)$')
= CAST(1 As BIT))
);
Jpgfilename列的值必须满足下面的模式：以一个A到Z之间的字母开头，后跟一个冒号（盘符），或者是两个反斜线和一个单词（网络共享）。而且该值必须至少包含一个表示磁盘或共享的根目录的反斜线。然后，该值可以再包含一些表示多个子文件夹的反斜线/字符的组合。最后几个字符必须是“. jpg”（大写或小写）。
下面包含有效JPEG文件名的INSERT可以被接受：
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'C:\Temp\myFile.jpg');
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'\\MyShare\Temp\myFile.jpg');
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'\\MyShare\myFile.jpg');
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'C:\myFile.jpg');
而下面包含 .txt文件名的INSERT会被拒绝：
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'C:\Temp\myFile.txt');
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'\\MyShare\\Temp\myFile.jpg');
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'\\myFile.jpg');
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'C:myFile.jpg');
完成后，运行下面的代码进行清理：
IF OBJECT_ID('dbo.TestRegEx') IS NOT NULL
DROP TABLE dbo.TestRegEx;
GO
IF OBJECT_ID('dbo.fn_RegExMatch') IS NOT NULL
DROP FUNCTION dbo.fn_RegExMatch;
显式和隐式转换
在SQL Server 2005中开发CLR对象时，你可以使用 .NET本机类型（native type）和 .NET SQL类型来定义输入/输出参数和变量。.NET SQL类型映射到SQL Server类型时更精确。在程序接口中使用 .NET本机类型会导致与SQL Server或到SQL Server的传递值时发生隐式转换。一些开发人员更乐意使用 .NET SQL类型，因为他们相信隐式转换会产生性能损耗。但这样有一些限制，因为 .NET SQL类型在功能方面并没有 .NET本机类型丰富。例如，.NET的本机类型System.String（C# 中是string, Visual Basic是String）提供了Substring方法，而 .NET SQL的 SqlString类型则没有。
尽管性能并不是它们真正的差别。但我还是建议你在创建CLR对象的 .NET代码中使用SQL类型，因为 .NET本机类型不支持NULL值。例如，如果实现fn_RegExMatch函数时没有使用SqlString类型而是使用 .NET string本机类型作为参数，在代码检查参数值是否为NULL地方你会收到一个编译错误。如果你使用 .NET string 类型并跳过NULL检查，当用NULL参数调用它时函数将返回一个异常。如果你需要 .NET本机类型提供的其他功能，你必须执行一些显式转换。此外，你可以使用SQL类型变量的Value属性得到 .NET本机类型的值，把该值保存在一个 .NET本机类型的变量中，这样就可以使用本机类型的所有常规属性和方法了。在fn_RegExMatch函数中，RegEx.IsMatch需要 .NET string 类型作为输入；通过 .NET SQL类型的Value属性得到该值。该方法的返回类型是一个 .NET本机类型Boolean，所以代码把它显式转换为SqlBoolean。
这一节将证明隐式转换和显式转换之间的性能差异其实并不是很明显。下面的C# 代码定义了函数fn_ImpCast和fn_ExpCast，前者使用 .NET本机类型和隐式转换，而后者则使用 .NET SQL类型和显式转换。
// 比较隐式转换和显式转换
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static string fn_ImpCast(string inpStr)
{
return inpStr.Substring(2, 3);
}
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_ExpCast(SqlString inpStr)
{
return (SqlString)inpStr.ToString().Substring(2, 3);
}
下面是定义该函数的Visual Basic代码：
'比较隐式转换和显式转换
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_ImpCast(ByVal inpStr As String) As String
Return inpStr.Substring(2, 3)
End Function
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_ExpCast(ByVal inpStr As SqlString) As SqlString
Return CType(inpStr.ToString().Substring(2, 3), SqlString)
End Function
下面是在数据库中注册C#版函数的代码：
IF OBJECT_ID('dbo.fn_ImpCast') IS NOT NULL
DROP FUNCTION dbo.fn_ImpCast;
GO
IF OBJECT_ID('dbo.fn_ExpCast') IS NOT NULL
DROP FUNCTION dbo.fn_ExpCast;
GO
-- 创建 fn_ImpCast 函数
CREATE FUNCTION dbo.fn_ImpCast(@inpstr AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_ImpCast;
GO
-- 创建 fn_ExpCast 函数
CREATE FUNCTION dbo.fn_ExpCast(@inpstr AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_ExpCast;
下面的代码注册Visual Basic版的函数：
--创建fn_ImpCast函数
CREATE FUNCTION dbo.fn_ImpCast(@inpstr AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_ImpCast;
GO
-- 创建fn_ExpCast函数
CREATE FUNCTION dbo.fn_ExpCast(@inpstr AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_ExpCast;
下面的代码通过循环调用一百万次fn_ImpCast，运行了16秒：
SET NOCOUNT ON;
GO
DECLARE @a AS NVARCHAR(4000);
DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 1000000
BEGIN
SET @a = dbo.fn_ImpCast(N'123456');
SET @i = @i + 1;
END
下面的代码调用fn_ExpCast函数, 运行了17秒:
DECLARE @a AS NVARCHAR(4000);
DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 1000000
BEGIN
SET @a = dbo.fn_ExpCast(N'123456');
SET @i = @i + 1;
END
如你所见，差别不是很明显，在这个测试中隐式转换方法甚至还要比显式转换方法稍快一些。
完成后，运行下面的代码进行清理。
IF OBJECT_ID('dbo.fn_ImpCast') IS NOT NULL
DROP FUNCTION dbo.fn_ImpCast;
GO
IF OBJECT_ID('dbo.fn_ExpCast') IS NOT NULL
DROP FUNCTION dbo.fn_ExpCast;
SQL签名(SQL Signature)
这一节提供一个函数的T-SQL和CLR两种实现，该函数返回查询字符串的签名。思路是接收查询字符串作为输入并返回表示该查询“签名”或“模板”的字符串。在这个签名中，出现在输入查询字符串中的所有字面值（literal）都被替换为一个通用的符号（在这个例子中是# ）。例如，假设你使用下面的查询字符串。
N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78;'
你希望得到下面的字符串：
N'SELECT * FROM dbo.T1 WHERE col1 = # AND col2 > #'
当你把跟踪到的SQL语句插入一个表后再通过这些数据来统计查询的性能时，这样的函数会非常有用。如果你直接按原始查询字符串分组，逻辑上相同的查询会被放到不同的组中。统计这些查询签名的性能将为你提供更实用更有价值的信息。
T-SQL SQL签名UDF
你可以在代码清单6-2中找到SQL签名函数的T-SQL实现。我要感谢Stuart Ozer，是他编写了这个函数，并允许我在这本书中使用。Stuart是Microsoft SQL Server Customer Advisory Team的成员。
代码清单6-2 创建fn_SQLSigTSQL UDF的脚本
IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL
DROP FUNCTION dbo.fn_SQLSigTSQL;
GO
CREATE FUNCTION dbo.fn_SQLSigTSQL
(@p1 NTEXT, @parselength INT = 4000)
RETURNS NVARCHAR(4000)
--
--该函数以“现状”提供且没有任何担保
--同时也没有授予任何权利 -- 使用该函数所包含的脚本示例受下列条款约束
-- http://www.microsoft.com/info/cpyright.htm
--
-- 分解查询字符串
AS
BEGIN
DECLARE @pos AS INT;
DECLARE @mode AS CHAR(10);
DECLARE @maxlength AS INT;
DECLARE @p2 AS NCHAR(4000);
DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);
DECLARE @p2len AS INT;
SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));
SET @maxlength = CASE WHEN @maxlength > @parselength
THEN @parselength ELSE @maxlength END;
SET @pos = 1;
SET @p2 = '';
SET @p2len = 0;
SET @currchar = '';
set @nextchar = '';
SET @mode = 'command';
WHILE (@pos <= @maxlength)
BEGIN
SET @currchar = SUBSTRING(@p1,@pos,1);
SET @nextchar = SUBSTRING(@p1,@pos+1,1);
IF @mode = 'command'
BEGIN
SET @p2 = LEFT(@p2,@p2len) + @currchar;
SET @p2len = @p2len + 1 ;
IF @currchar IN (',','(',' ','=','<','>','!')
AND @nextchar BETWEEN '0' AND '9'
BEGIN
SET @mode = 'number';
SET @p2 = LEFT(@p2,@p2len) + '#';
SET @p2len = @p2len + 1;
END
IF @currchar = ''''
BEGIN
SET @mode = 'literal';
SET @p2 = LEFT(@p2,@p2len) + '#''';
SET @p2len = @p2len + 2;
END
END
ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!')
SET @mode= 'command';
ELSE IF @mode = 'literal' AND @currchar = ''''
SET @mode= 'command';
SET @pos = @pos + 1;
END
RETURN @p2;
END
GO
fn_SQLSigTSQL函数接收两个输入参数：@p1是输入的查询字符串，@parselength是你要分析的最大字符个数。如果 @parselength小于 @p1中的查询字符串的长度，函数将只分析最左边的 @parselength个字符。该函数一次遍历字符串中的一个字符。在 @mode变量保存一个状态值，该变量可能是下列值之一：‘command’、‘number’ 或 ‘literal’。
Command是默认的状态，它表示当前的字符将被串联到输出字符串中。Number表示识别出一个数字字面值（number literal），这时将串联# 符号。当逗号、左括号、空格或运算符后面跟一个数字时标识一个数字字面值。当下一个字符是逗号、右括号、空格或运算符时状态从Number转到Command。Literal表示标识了一个字符串字面值, 这时将串联字符串' # '。当检测到左引号时标识一个字符串字面值。当检测到右引号时状态从literal转到command。
要测试fn_SQLSigTSQL函数，运行下面的代码：
SELECT dbo.fn_SQLSigTSQL
(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);
输出如下：
SELECT * FROM dbo.T1 WHERE col1 = # AND col2 > #
CLR SQL 签名 UDF
代码清单6-3和6-4包含了SQL签名函数的C# 和 Visual Basic实现。该函数的 .NET版本改编自Stuart的算法。由Andrew J. Kelly 和 Dejan Sarka开发，他们都是Solid Quality Learning的顾问和著名的SQL Server MVP。该函数的 .NET版本与代码清单6-2中的T-SQL版本相似，提供该版本只是为了性能测试和比较而已。在本章的后面，我将描述使用正则表达式生成查询签名的更为强大的基于CLR的解决方案。
代码清单6-3 C# 版的fn_SQLSigCLR函数
// SQL 签名
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_SQLSigCLR(SqlString inpRawString,
SqlInt32 inpParseLength)
{
if (inpRawString.IsNull)
return SqlString.Null;
int pos = 0;
string mode = "command";
string RawString = inpRawString.Value;
int maxlength = RawString.Length;
StringBuilder p2 = new StringBuilder();
char currchar = ' ';
char nextchar = ' ';
int ParseLength = RawString.Length;
if (!inpParseLength.IsNull)
ParseLength = inpParseLength.Value;
if (RawString.Length > ParseLength)
{
maxlength = ParseLength;
}
while (pos < maxlength)
{
currchar = RawString[pos];
if (pos < maxlength - 1)
{
nextchar = RawString[pos + 1];
}
else
{
nextchar = RawString[pos];
}
if (mode == "command")
{
p2.Append(currchar);
if ((",( =<>!".IndexOf(currchar) >= 0)
&&
(nextchar >= '0' && nextchar <= '9'))
{
mode = "number";
p2.Append('#');
}
if (currchar == '\'')
{
mode = "literal";
p2.Append("#'");
}
}
else if ((mode == "number")
&&
(",( =<>!".IndexOf(nextchar) >= 0))
{
mode = "command";
}
else if ((mode == "literal") && (currchar == '\''))
{
mode = "command";
}
pos++;
}
return p2.ToString ();
}
代码清单6-4 Visual Basic版的fn_SQLSigCLR函数
' SQL 签名
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_SQLSigCLR(ByVal inpRawString As SqlString, _
ByVal inpParseLength As SqlInt32) As SqlString
If inpRawString.IsNull Then
Return SqlString.Null
End If
Dim pos As Integer = 0
Dim mode As String = "command"
Dim RawString As String = inpRawString.Value
Dim maxlength As Integer = RawString.Length
Dim p2 As StringBuilder = New StringBuilder()
Dim currchar As Char = " "c
Dim nextchar As Char = " "c
Dim ParseLength As Integer = RawString.Length
If (Not inpParseLength.IsNull) Then
ParseLength = inpParseLength.Value
End If
If (RawString.Length > ParseLength) Then
maxlength = ParseLength
End If
While (pos < maxlength)
currchar = RawString(pos)
If (pos < maxlength - 1) Then
nextchar = RawString(pos + 1)
Else
nextchar = RawString(pos)
End If
If (mode = "command") Then
p2.Append(currchar)
If ((",( =<>!".IndexOf(currchar) >= 0) _
And _
(nextchar >= "0"c And nextchar <= "9"c)) Then
mode = "number"
p2.Append("#")
End If
If (currchar = "'"c) Then
mode = "literal"
p2.Append("#")
End If
ElseIf ((mode = "number") And _
(",( =<>!".IndexOf(nextchar) >= 0)) Then
mode = "command"
ElseIf ((mode = "literal") And _
(currchar = "'"c)) Then
mode = "command"
End If
pos=pos+1
End While
Return p2.ToString
End Function
使用下面的代码注册C# 版fn_SQLSigCLR函数：
IF OBJECT_ID('dbo.fn_SQLSigCLR') IS NOT NULL
DROP FUNCTION dbo.fn_SQLSigCLR;
GO
CREATE FUNCTION dbo.fn_SQLSigCLR
(@rawstring AS NVARCHAR(4000), @parselength AS INT)
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_SQLSigCLR;
如果你使用Visual Basic实现该函数，使用下面的代码进行注册：
CREATE FUNCTION dbo.fn_SQLSigCLR
(@rawstring AS NVARCHAR(4000), @parselength AS INT)
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_SQLSigCLR;
运行下面的代码测试fn_SQLSigCLR函数：
SELECT dbo.fn_SQLSigCLR
(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);
得到的输出如下：
SELECT * FROM dbo.T1 WHERE col1 = # AND col2 > #
比较T-SQL 和 CLR SQL 签名 UDF的性能
在字符串处理方面 .NET代码比T-SQL更快。SQL签名函数就是一个可以演示这种性能差异的极好的例子，尤其是两个版本实现的是同一个算法。你马上就会看到它们在字符串处理方面的性能差异。
首先，运行下面的代码创建Queries表并用100 000个查询字符串填充该表：
IF OBJECT_ID('dbo.Queries') IS NOT NULL
DROP TABLE dbo.Queries;
GO
SELECT CAST(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78'
AS NVARCHAR(MAX)) AS query
INTO dbo.Queries
FROM dbo.Nums
WHERE n <= 100000;
打开SSMS中的执行后放弃结果选项。
当我运行下面这行使用了T-SQL版函数的代码时，用了将近100秒。
SELECT dbo.fn_SQLSigTSQL(query, 4000) FROM dbo.Queries;
而该函数的CLR C# 版在1秒钟之内就完成了，Visual Basic版用了2秒钟。
SELECT dbo.fn_SQLSigCLR(query, 4000) FROM dbo.Queries;
关闭SSMS上的执行后放弃结果选项。
如你所见，该函数的CLR版本大概比T-SQL版本快100倍。
就像我曾提到的，fn_SQLSigCLR函数实现的算法和fn_SQLSigTSQL函数相同，提供它只是为了性能比较。你可以使用正则表达式实现更为强大的基于CLR的解决方案。我以前曾向你展示过如何使用正则表达式进行模式匹配，它用于检查一个字符串是否匹配某模式。你也可以使用正则表达式实现基于模式（pattern-based）的替换。即，你可以使用一个模式替换字符串中出现的其他模式。下面是fn_RegExReplace函数的C# 定义，它调用Regex对象的Replace方法：
// fn_RegExReplace –基于正则表达式的通用替换
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_RegExReplace(
SqlString input, SqlString pattern, SqlString replacement)
{
if (input.IsNull || pattern.IsNull || replacement.IsNull)
return SqlString.Null;
else
return (SqlString)Regex.Replace(
input.Value, pattern.Value, replacement.Value);
}
下面是该函数的Visual Basic定义：
' fn_RegExReplace -基于正则表达式的通用替换
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_RegExReplace( _
ByVal input As SqlString, ByVal pattern As SqlString, _
ByVal replacement As SqlString) As SqlString
If (input.IsNull Or pattern.IsNull Or replacement.IsNull) Then
Return SqlString.Null
Else
Return CType(Regex.Replace( _
input.Value, pattern.Value, replacement.Value), SqlString)
End If
End Function
该函数接收三个输入参数：input、pattern和replacement。函数首先检查参数值是否为Null，如果是则返回Null。如果参数值都不为Null，则函数调用Regex.Replace方法，该方法用replacement模式替换input字符串内出现的每个pattern。下面我来演示如何使用fn_RegExReplace函数生成查询签名，当然你也可以把该函数用于普通的基于模式的字符串替换。
使用下面的代码注册C# 版的fn_RegExReplace函数。
IF OBJECT_ID('dbo.fn_RegExReplace') IS NOT NULL
DROP FUNCTION dbo.fn_RegExReplace;
GO
CREATE FUNCTION dbo.fn_RegExReplace(
@input AS NVARCHAR(MAX),
@pattern AS NVARCHAR(MAX),
@replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_RegExReplace;
如果你用Visual Basic实现该函数，使用下面的代码进行注册。
CREATE FUNCTION dbo.fn_RegExReplace(
@input AS NVARCHAR(MAX),
@pattern AS NVARCHAR(MAX),
@replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_RegExReplace;
下面是一个示例，它演示了如何使用该函数为Queries表中查询字符串的生成查询签名。
SELECT
dbo.fn_RegExReplace(query,
N'([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?# expression coming
)(?:([N])?('')(?:[^'']|'''')*(''))(?# character
)|(?:0x[\da-fA-F]*)(?# binary
)|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?# precise number
)(?:[eE]?[\d]*)))(?# imprecise number
)|(?:[~]?[-+]?(?:[\d]+))(?# integer
))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?# operators
))',
N'$1$2$3#$4')
FROM dbo.Queries;
结合内置的注释，这些模式很容易理解。它能识别（并用#符号替换）比fn_SQLSigCLR函数更多的字面值类型。它可以识别字符字面值、二进制字面值、精确数字、近似数字，甚至可以识别包含字面值的混合表达式（fold expression），并用#符号替换它们。相对于fn_SQLSigCLR函数，该解决方案还有一个优势，你可以维护自己的正则表达式并增强它们的功能以支持更多的情况，而且不需要修改该函数的定义。然而，通过正则表达式所增强的功能需要一定的成本，上面的查询大概会运行12秒，比fn_SQLSigCLR函数慢了12倍，但还是比fn_SQLSigTSQL函数快8倍。
提示
你可能想创建一些具有更广泛用途的函数，比如,不用为函数名称限定数据库(CLRUtilities.dbo.fn_RegExReplace)就可以在所有数据库中访问fn_RegExReplace。为此，在每个要使用该函数的数据库中创建一个该函数的同义词(synonym)，为了能在Northwind数据库中使用该函数，运行下面的代码：
USE Northwind;
GO
CREATE SYNONYM dbo.fn_RegExReplace
FOR CLRUtilities.dbo.fn_RegExReplace;
如果你在model数据库创建了一个同义词，以后你创建的每个新数据库都会创建这个同义词，因为新数据库都是以model副本的方式创建的。这同样也应用于tempdb数据库，它在每次重启动SQL Server时被创建。
完成后，运行下面的代码进行清理。
USE Northwind;
GO
IF OBJECT_ID('dbo.fn_RegExReplace', 'SN') IS NOT NULL
DROP SYNONYM dbo.fn_RegExReplace;
GO
USE CLRUtilities;
GO
IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL
DROP FUNCTION dbo.fn_SQLSigTSQL;
GO
IF OBJECT_ID('dbo.fn_SQLSigCLR') IS NOT NULL
DROP FUNCTION dbo.fn_SQLSigCLR;
GO
IF OBJECT_ID('dbo.fn_RegExReplace') IS NOT NULL
DROP FUNCTION dbo.fn_RegExReplace;
GO