【IT168技术】首先总结：SQL Server CLR 设计简单，部署方便，效率很高，很安全，随数据库移动。
测试用例，视频演示在此下载：http://download.csdn.net/source/2279319
环境：SQL Server 2005/2008，Visual Studio 2005/2008
在SQL Server里启用CLR：
在SQL Server里执行以下命名，来启用CLR
exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
reconfigure
用Visual Studio设计功能，并部署到相关数据库：
打开Visual Studio-->新建项目-->数据库-->SQL Server项目-->添加数据库引用里新建链接(一会将会把CLR部署到这个数据库上)-->右击解决方案，添加“用户自定义函数”
这时，系统会生成一个示例文件 Function1.cs 内容：
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Function1()
{
// 在此处放置代码
return new SqlString("Hello");
}
}
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{ [Microsoft.SqlServer.Server.SqlFunction] public static SqlString Function1() { // 在此处放置代码 return new SqlString("Hello"); }}
现在可以直接右击解决方案，选择“部署”，状态栏里显示“部署已成功”
在SQL Server里执行我们部署的CLR函数：
再次进入SQL Server,进入到相关数据库，执行 Select dbo.Function1(),全显示执行结果："Hello"
这个函数你可以在 “数据库-->可编程性-->函数-->标量值函数” 里看到
OK,这就是整个流程，Very Easy.
更高级应用：
当然我们用CLR 不是只为了让他生成一个Hello就完事的，这里来说明一下柳永法(yongfa365)的用途：
去年给公司设计了个OA系统，公司的一些文件内容都非常长，所以选择了varchar(max),初期感觉查询速度还挺快，后来觉得越来越慢。
初步分析结果显示：
数据有近8000条
有3000多条数据len(txtContent)得到结果在4000字符以上
使用“数据库引擎优化顾问”，对其优化提速为"0%"
SQL语句类似：SELECT * FROM dbo.Articles WHERE txtContent LIKE '%柳永法%'
以前做过的所有系统，从没有遇到这种问题
近一步分析结果：
数据条数很少，速度却这么慢，分析可能是数据库引擎问题 换台机器试问题依旧，排除
like效率问题，以前的系统都是条数多，而这次遇到的是每条数据里字段内容很长，like除了在数据条数大时会出现性能问题外，还跟每条的字段内容长度有关。在网上查询并测试确认，确实是数据内容长度问题，而这个系统里是不可能使用 like '柳永法%'这样可以使用索引的查询的。
想来想去只能是使用全文索引，但总会有一些记录查不出来,而这个要求就这么高，所以暂时放弃。这时想到了SQL Server CLR,以前只是听过，觉得可能有用，都收藏了起来，现在打开Chrome,把Google Bookmark上收藏的关于SQL Server的CLR的链接全部打开研究了几分钟，自己写了个函数，部署，测试，哈哈……。忒玄妙了，以前的txtContent LIKE '%柳永法%'用时10到12秒，而用我写的SQL Server CLR函数dbo.ContainsOne(txtContent,'柳永法')=1只用了1秒左右，够神奇吧。
执行以下语句三次，相当于8年后数据量，有6万多条数据
INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent FROM dbo.Articles
INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent FROM dbo.Articles
再执行测试，一般的 like用时82秒，而clr用时5秒，够有看头吧。
函数及测试语句如下：
2 public static SqlBoolean ContainsOne(SqlChars input, string search)
3 {
4 return new string(input.Value).Contains(search);
5 }
6
7 SELECT COUNT(*) FROM dbo.Articles WHERE dbo.ContainsOne(txtContent,'柳永法')=1
8
另外，我比较热衷于正则表达式，所以我还想给SQL Server增加一个正则表达式替换的功能，写起来也非常容易：
1 [Microsoft.SqlServer.Server.SqlFunction]
2 public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
3 {
4 return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
5 }
娃哈哈，一切都这么的顺利，这么的得心应手，怎能不让我推荐,在此贴上我写的一此函数：
2 using System.Data;
3 using System.Data.SqlClient;
4 using System.Data.SqlTypes;
5 using Microsoft.SqlServer.Server;
6 using System.Text.RegularExpressions;
7 using System.Collections.Generic;
8 using System.IO;
9
10 /*
11 请先在SQL Server里执行以下命名，来启用CLR
12 exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
13 reconfigure
14 */
15
16 public partial class UserDefinedFunctions
17 {
18 ///
19 /// SQL CLR 使用正则表达式替换，eg:
20 /// select dbo.RegexReplace('柳永法http://www.yongfa365.com/','<.+?>','')
21 /// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')
22 /// --结果：柳永法http://www.yongfa365.com/
23 ///
24 /// 源串，或字段名
25 /// 正则表达式
26 /// 替换后结果
27 [Microsoft.SqlServer.Server.SqlFunction]
28 public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
29 {
30 return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
31 }
32
33
34
35
36 ///
37 /// SQL CLR 使用正则表达式替换，eg:
38 /// select dbo.RegexSearch('柳永法','<.+?>','')
39 /// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1;
40 ///
41 /// 源串，或字段名
42 /// 正则表达式
43 /// 查询结果，1,0
44 [Microsoft.SqlServer.Server.SqlFunction]
45 public static SqlBoolean RegexSearch(SqlChars input, string pattern)
46 {
47 return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
48 }
49
50 ///
51 /// SQL CLR 使用.net的Contains查找是否满足条件,eg:
52 /// select dbo.ContainsOne('我是柳永法，','柳永法');
53 /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;
54 ///
55 /// 源串，或字段名
56 /// 要搜索的字符串
57 /// 返回是否匹配,1,0
58 [Microsoft.SqlServer.Server.SqlFunction]
59 public static SqlBoolean ContainsOne(SqlChars input, string search)
60 {
61 return new string(input.Value).Contains(search);
62 }
63
64
65 ///
66 /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:
67 /// select dbo.ContainsAny('我是柳永法，','柳,永,法');
68 /// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1;
69 ///
70 /// 源串，或字段名
71 /// 要搜索的字符串，以","分隔，自己处理空格问题
72 /// 返回是否匹配,1,0
73 [Microsoft.SqlServer.Server.SqlFunction]
74 public static SqlBoolean ContainsAny(SqlChars input, string search)
75 {
76 string strTemp=new string(input.Value);
77 foreach (string item in search.Split(','))
78 {
79 if (strTemp.Contains(item))
80 {
81 return true;
82 }
83 }
84 return false;
85 }
86
87 ///
88 /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:
89 /// select dbo.ContainsAll('我是柳永法，','柳,永,法');
90 /// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1;
91 ///
92 /// 源串，或字段名
93 /// 要搜索的字符串，以","分隔，自己处理空格问题
94 /// 返回是否匹配,1,0
95 [Microsoft.SqlServer.Server.SqlFunction]
96 public static SqlBoolean ContainsAll(SqlChars input, string search)
97 {
98 string strTemp = new string(input.Value);
99 foreach (string item in search.Split(','))
100 {
101 if (!strTemp.Contains(item))
102 {
103 return false;
104 }
105 }
106 return true;
107 }
108
109 };
110
重要提示：
官方说明里有其dll部署方法，比较麻烦，推荐直接用Visual Studio部署，方便快捷。
SQL Server CLR 部署到某个数据库后，便成为那个数据库的一部分，即便备份及还原到其它机器上，它依然具有CLR带来的功能。
SqlString 数据类型转换成 nvarchar(4,000)，而 SqlChars 转换成 nvarchar(max)。尽可能使用 nvarchar(max) 并且最大程度地保证灵活性。然而，如果所有相关字符串包含的字符都少于 4,000 个，使用 nvarchar(4,000) 则性能可得到显著改善。
CLR里返回的bool对应SQL Server里的bit,即：1/0/Null,而不是true/false,所以，没法直接用dbo.ContainsOne(txtContent,'柳永法')实现bool形，而得这么用：dbo.ContainsOne(txtContent,'柳永法')=1
