SQL Server CLR 极速入门，启用、设计、部署、运行

先总结：SQL Server CLR 设计简单，部署方便，效率很高，很安全，随数据库移动。测试用例，环境：SQL Server 2005/2008，Visual Studio 2005/2008

在SQL Server里启用CLR：

在SQL Server里执行以下命名，来启用CLR

view plain copy to clipboard print ?

1. execsp_configure'clrenabled',1--1,启用clr0,禁用clr
2. reconfigure

exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
reconfigure

用Visual Studio设计功能，并部署到相关数据库：

打开Visual Studio-->新建项目-->数据库-->SQL Server项目-->添加数据库引用里新建链接（一会将会把CLR部署到这个数据库上）-->右击解决方案，添加“用户自定义函数”这时，系统会生成一个示例文件 Function1.cs 内容：

view plain copy to clipboard print ?

1. usingSystem;
2. usingSystem.Data;
3. usingSystem.Data.SqlClient;
4. usingSystem.Data.SqlTypes;
5. usingMicrosoft.SqlServer.Server;
6. 
   
7. publicpartialclassUserDefinedFunctions
8. {
9. [Microsoft.SqlServer.Server.SqlFunction]
10. publicstaticSqlStringFunction1()
11. {
12. //在此处放置代码
13. returnnewSqlString("Hello");
14. }
15. };

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),初期感觉查询速度还挺快，后来觉得越来越慢。

初步分析结果显示：

1. 数据有近8000条
2. 有3000多条数据len(txtContent)得到结果在4000字符以上
3. 使用“数据库引擎优化顾问”，对其优化提速为"0%"
4. SQL语句类似：SELECT * FROM dbo.Articles WHERE txtContent LIKE '%柳永法%'
5. 以前做过的所有系统，从没有遇到这种问题

近一步分析结果：

1. 数据条数很少，速度却这么慢，分析可能是数据库引擎问题 换台机器试问题依旧，排除
2. like效率问题，以前的系统都是条数多，而这次遇到的是每条数据里字段内容很长，like除了在数据条数大时会出现性能问题外，还跟每条的字段内容长度有关。在网上查询并测试确认，确实是数据内容长度问题，而这个系统里是不可能使用 like '柳永法%'这样可以使用索引的查询的。

以前的txtContent LIKE '%柳永法%'用时10到12秒，而用我写的SQL Server CLR函数dbo.ContainsOne(txtContent,'柳永法')=1只用了1秒左右，够神奇吧。

执行以下语句三次，相当于8年后数据量，有6万多条数据

view plain copy to clipboard print ?

1. INSERTdbo.Articles(txtTitle,txtContent)SELECTtxtTitle,txtContentFROMdbo.Articles

INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent  FROM dbo.Articles

再执行测试，一般的 like用时82秒，而clr用时5秒，够有看头吧。

函数及测试语句如下：

view plain copy to clipboard print ?

1. [Microsoft.SqlServer.Server.SqlFunction]
2. publicstaticSqlBooleanContainsOne(SqlCharsinput,stringsearch)
3. {
4. returnnewstring(input.Value).Contains(search);
5. }
6. 
   
7. SELECTCOUNT(*)FROMdbo.ArticlesWHEREdbo.ContainsOne(txtContent,'柳永法')=1

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean ContainsOne(SqlChars input, string search)
{
    return new string(input.Value).Contains(search);
}

SELECT COUNT(*) FROM dbo.Articles WHERE dbo.ContainsOne(txtContent,'柳永法')=1

另外，我比较热衷于正则表达式，所以我还想给SQL Server增加一个正则表达式替换的功能，写起来也非常容易：[Microsoft.SqlServer.Server.SqlFunction]public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement){ return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);}

娃哈哈，一切都这么的顺利，这么的得心应手，怎能不让我推荐,在此贴上我写的一此函数：

view plain copy to clipboard print ?

1. usingSystem;
2. usingSystem.Data;
3. usingSystem.Data.SqlClient;
4. usingSystem.Data.SqlTypes;
5. usingMicrosoft.SqlServer.Server;
6. usingSystem.Text.RegularExpressions;
7. usingSystem.Collections.Generic;
8. usingSystem.IO;
9. 
   
10. /*
11. 请先在SQLServer里执行以下命名，来启用CLR
12. execsp_configure'clrenabled',1--1,启用clr0,禁用clr
13. reconfigure
14. */
15. 
    
16. publicpartialclassUserDefinedFunctions
17. {
18. ///<summary>
19. ///SQLCLR使用正则表达式替换，eg:
20. selectdbo.RegexReplace('<span>柳永法http://www.yongfa365.com/</span>','<.+?>','')
21. updateArticlessettxtContent=dbo.RegexReplace(txtContent,'<.+?>','')
22. --结果：柳永法http://www.yongfa365.com/
23. ///</summary>
24. name="input">源串，或字段名</param>
25. name="pattern">正则表达式</param>
26. returns>替换后结果</returns>
27. [Microsoft.SqlServer.Server.SqlFunction]
28. publicstaticSqlStringRegexReplace(SqlCharsinput,SqlStringpattern,SqlStringreplacement)
29. {
30. returnRegex.Replace(newstring(input.Value),pattern.Value,replacement.Value,RegexOptions.Compiled);
31. }
32. 
    
33. 
    
34. 
    
35. 
    
36. ///<summary>
37. ///SQLCLR使用正则表达式替换，eg:
38. selectdbo.RegexSearch('<span>柳永法</span>','<.+?>','')
39. select*fromArticleswheredbo.RegexSearch(txtContent,'柳永法')=1;
40. ///</summary>
41. name="input">源串，或字段名</param>
42. name="pattern">正则表达式</param>
43. returns>查询结果，1,0</returns>
44. [Microsoft.SqlServer.Server.SqlFunction]
45. publicstaticSqlBooleanRegexSearch(SqlCharsinput,stringpattern)
46. {
47. returnRegex.Match(newstring(input.Value),pattern,RegexOptions.Compiled).Success;
48. }
49. 
    
50. ///<summary>
51. Contains查找是否满足条件,eg:
52. selectdbo.ContainsOne('我是柳永法，','柳永法');
53. select*fromArticleswheredbo.ContainsOne(txtContent,'柳永法')=1;
54. ///</summary>
55. name="input">源串，或字段名</param>
56. name="search">要搜索的字符串</param>
57. returns>返回是否匹配,1,0</returns>
58. [Microsoft.SqlServer.Server.SqlFunction]
59. publicstaticSqlBooleanContainsOne(SqlCharsinput,stringsearch)
60. {
61. returnnewstring(input.Value).Contains(search);
62. }
63. 
    
64. 
    
65. ///<summary>
66. Contains查找是否满足其中之一的条件,eg:
67. selectdbo.ContainsAny('我是柳永法，','柳,永,法');
68. select*fromArticleswheredbo.ContainsAny(txtContent,'柳,永,法')=1;
69. ///</summary>
70. name="input">源串，或字段名</param>
71. name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
72. returns>返回是否匹配,1,0</returns>
73. [Microsoft.SqlServer.Server.SqlFunction]
74. publicstaticSqlBooleanContainsAny(SqlCharsinput,stringsearch)
75. {
76. stringstrTemp=newstring(input.Value);
77. insearch.Split(','))
78. {
79. Contains(item))
80. {
81. returntrue;
82. }
83. }
84. returnfalse;
85. }
86. 
    
87. ///<summary>
88. Contains查找是否满足所有的条件,eg:
89. selectdbo.ContainsAll('我是柳永法，','柳,永,法');
90. select*fromArticleswheredbo.ContainsAll(txtContent,'柳,永,法')=1;
91. ///</summary>
92. name="input">源串，或字段名</param>
93. name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
94. returns>返回是否匹配,1,0</returns>
95. [Microsoft.SqlServer.Server.SqlFunction]
96. publicstaticSqlBooleanContainsAll(SqlCharsinput,stringsearch)
97. {
98. stringstrTemp=newstring(input.Value);
99. insearch.Split(','))
100. {
101. Contains(item))
102. {
103. returnfalse;
104. }
105. }
106. returntrue;
107. }
108. 
     
109. };

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.IO;

/*
请先在SQL Server里执行以下命名，来启用CLR
exec sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
reconfigure
*/

public partial class UserDefinedFunctions
{
    /// <summary>
    /// SQL CLR 使用正则表达式替换，eg:
    /// select dbo.RegexReplace('<span>柳永法http://www.yongfa365.com/</span>','<.+?>','')
    /// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')
    /// --结果：柳永法http://www.yongfa365.com/
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="pattern">正则表达式</param>
    /// <returns>替换后结果</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
    {
        return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
    }




    /// <summary>
    /// SQL CLR 使用正则表达式替换，eg:
    /// select dbo.RegexSearch('<span>柳永法</span>','<.+?>','')
    /// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="pattern">正则表达式</param>
    /// <returns>查询结果，1,0</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean RegexSearch(SqlChars input, string pattern)
    {
        return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
    }

    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足条件,eg:
    /// select dbo.ContainsOne('我是柳永法，','柳永法');
    /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串</param>
    /// <returns>返回是否匹配,1,0</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean ContainsOne(SqlChars input, string search)
    {
        return new string(input.Value).Contains(search);
    }


    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:
    /// select dbo.ContainsAny('我是柳永法，','柳,永,法');
    /// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
    /// <returns>返回是否匹配,1,0</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean ContainsAny(SqlChars input, string search)
    {
        string strTemp=new string(input.Value);
        foreach (string item in search.Split(','))
        {
            if (strTemp.Contains(item))
            {
                return true;
            }
        }
        return false;
    }

    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:
    /// select dbo.ContainsAll('我是柳永法，','柳,永,法');
    /// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
    /// <returns>返回是否匹配,1,0</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean ContainsAll(SqlChars input, string search)
    {
        string strTemp = new string(input.Value);
        foreach (string item in search.Split(','))
        {
            if (!strTemp.Contains(item))
            {
                return false;
            }
        }
        return true;
    }

};

重要提示：

1. 官方说明里有其dll部署方法，比较麻烦，推荐直接用Visual Studio部署，方便快捷。
2. SQL Server CLR 部署到某个数据库后，便成为那个数据库的一部分，即便备份及还原到其它机器上，它依然具有CLR带来的功能。
3. SqlString 数据类型转换成 nvarchar(4,000)，而 SqlChars 转换成 nvarchar(max)。尽可能使用 nvarchar(max) 并且最大程度地保证灵活性。然而，如果所有相关字符串包含的字符都少于 4,000 个，使用 nvarchar(4,000) 则性能可得到显著改善。
4. CLR里返回的bool对应SQL Server里的bit,即：1/0/Null,而不是true/false,所以，没法直接用dbo.ContainsOne(txtContent,'柳永法')实现bool形，而得这么用：dbo.ContainsOne(txtContent,'柳永法')=1

参考：SQL Server CLR 集成简介：http://msdn.microsoft.com/zh-cn/library/ms254498(VS.80).aspxSQL Server 2005 正则表达式使模式匹配和数据提取变得更容易：http://msdn.microsoft.com/zh-cn/magazine/cc163473.aspxSQLCLR（一）应用C#和SQLCLR编写SQL Server用户定义函数