原文:http://www.yongfa365.com/Item/SQL-Server-CLR.html
首先总结:SQL Server CLR 设计简单,部署方便,效率很高,很安全,随数据库移动。
测试用例,视频演示在此下载:http://download.csdn.net/source/2279319
环境:SQL Server 2005/2008,Visual Studio 2005/2008 在SQL Server里执行以下命名,来启用CLR 打开Visual Studio-->新建项目-->数据库-->SQL Server项目-->添加数据库引用里新建链接(一会将会把CLR部署到这个数据库上)-->右击解决方案,添加"用户自定义函数"
这时,系统会生成一个示例文件 Function1.cs 内容: 现在可以直接右击解决方案,选择"部署",状态栏里显示"部署已成功" 再次进入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万多条数据 再执行测试,一般的 like用时82秒,而clr用时5秒,够有看头吧。
函数及测试语句如下: 另外,我比较热衷于正则表达式,所以我还想给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);
}
娃哈哈,一切都这么的顺利,这么的得心应手,怎能不让我推荐,在此贴上我写的一此函数: 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
*/
publicpartial class UserDefinedFunctions
{
///
/// SQL CLR 使用正则表达式替换,eg:
/// select dbo.RegexReplace('柳永法http://www.yongfa365.com/','','')
/// update Articles set txtContent=dbo.RegexReplace(txtContent,'','')
/// --结果:柳永法http://www.yongfa365.com/
///
/// 源串,或字段名
/// 正则表达式
/// 替换后结果
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
{
return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
}
///
/// SQL CLR 使用正则表达式替换,eg:
/// select dbo.RegexSearch('柳永法','','')
/// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1;
///
/// 源串,或字段名
/// 正则表达式
/// 查询结果,1,0
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlBoolean RegexSearch(SqlChars input, string pattern)
{
return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
}
///
/// SQL CLR 使用.net的Contains查找是否满足条件,eg:
/// select dbo.ContainsOne('我是柳永法,','柳永法');
/// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;
///
/// 源串,或字段名
/// 要搜索的字符串
/// 返回是否匹配,1,0
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlBoolean ContainsOne(SqlChars input, string search)
{
return new string(input.Value).Contains(search);
}
///
/// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:
/// select dbo.ContainsAny('我是柳永法,','柳,永,法');
/// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1;
///
/// 源串,或字段名
/// 要搜索的字符串,以","分隔,自己处理空格问题
/// 返回是否匹配,1,0
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlBoolean ContainsAny(SqlChars input, string search)
{
string strTemp=new string(input.Value);
foreach (string item in search.Split(','))
{
if (strTemp.Contains(item)) {
returntrue; } }
returnfalse; }
///
/// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:
/// select dbo.ContainsAll('我是柳永法,','柳,永,法');
/// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1;
///
/// 源串,或字段名
/// 要搜索的字符串,以","分隔,自己处理空格问题
/// 返回是否匹配,1,0
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlBoolean ContainsAll(SqlChars input, string search)
{
string strTemp = new string(input.Value);
foreach (string item in search.Split(','))
{
if (!strTemp.Contains(item)) {
returnfalse; } }
returntrue; } };
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 { /// /// SQL CLR 使用正则表达式替换,eg: /// select dbo.RegexReplace('柳永法http://www.yongfa365.com/','','') /// update Articles set txtContent=dbo.RegexReplace(txtContent,'','') /// --结果:柳永法http://www.yongfa365.com/ /// /// 源串,或字段名 /// 正则表达式 /// 替换后结果 [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); } /// /// SQL CLR 使用正则表达式替换,eg: /// select dbo.RegexSearch('柳永法','','') /// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1; /// /// 源串,或字段名 /// 正则表达式 /// 查询结果,1,0 [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean RegexSearch(SqlChars input, string pattern) { return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success; } /// /// SQL CLR 使用.net的Contains查找是否满足条件,eg: /// select dbo.ContainsOne('我是柳永法,','柳永法'); /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1; /// /// 源串,或字段名 /// 要搜索的字符串 /// 返回是否匹配,1,0 [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean ContainsOne(SqlChars input, string search) { return new string(input.Value).Contains(search); } /// /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg: /// select dbo.ContainsAny('我是柳永法,','柳,永,法'); /// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1; /// /// 源串,或字段名 /// 要搜索的字符串,以","分隔,自己处理空格问题 /// 返回是否匹配,1,0 [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; } /// /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg: /// select dbo.ContainsAll('我是柳永法,','柳,永,法'); /// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1; /// /// 源串,或字段名 /// 要搜索的字符串,以","分隔,自己处理空格问题 /// 返回是否匹配,1,0 [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; } };
重要提示: 官方说明里有其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
参考:
SQL Server CLR 集成简介:http://msdn.microsoft.com/zh-cn/library/ms254498(V S.80).aspx
SQL Server 2005 正则表达式使模式匹配和数据提取变得更容易:http://msdn.microsoft.com/zh-cn/magazine/cc163473. aspx
SQLCLR(一)入门:http://www.cnblogs.com/DavidFan/archive/2007/05/08 /738557.html
应用C#和SQLCLR编写SQL Server用户定义函数:http://blog.csdn.net/zhzuo/archive/2009/05/24/4212 982.aspx#mark4
首先总结:SQL Server CLR 设计简单,部署方便,效率很高,很安全,随数据库移动。
测试用例,视频演示在此下载:http://download.csdn.net/source/2279319
环境:SQL Server 2005/2008,Visual Studio 2005/2008 在SQL Server里执行以下命名,来启用CLR 打开Visual Studio-->新建项目-->数据库-->SQL Server项目-->添加数据库引用里新建链接(一会将会把CLR部署到这个数据库上)-->右击解决方案,添加"用户自定义函数"
这时,系统会生成一个示例文件 Function1.cs 内容: 现在可以直接右击解决方案,选择"部署",状态栏里显示"部署已成功" 再次进入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万多条数据 再执行测试,一般的 like用时82秒,而clr用时5秒,够有看头吧。
函数及测试语句如下: 另外,我比较热衷于正则表达式,所以我还想给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);
}
娃哈哈,一切都这么的顺利,这么的得心应手,怎能不让我推荐,在此贴上我写的一此函数: 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
*/
publicpartial class UserDefinedFunctions
{
///
/// SQL CLR 使用正则表达式替换,eg:
/// select dbo.RegexReplace('柳永法http://www.yongfa365.com/','','')
/// update Articles set txtContent=dbo.RegexReplace(txtContent,'','')
/// --结果:柳永法http://www.yongfa365.com/
///
/// 源串,或字段名
/// 正则表达式
/// 替换后结果
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
{
return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
}
///
/// SQL CLR 使用正则表达式替换,eg:
/// select dbo.RegexSearch('柳永法','','')
/// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1;
///
/// 源串,或字段名
/// 正则表达式
/// 查询结果,1,0
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlBoolean RegexSearch(SqlChars input, string pattern)
{
return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
}
///
/// SQL CLR 使用.net的Contains查找是否满足条件,eg:
/// select dbo.ContainsOne('我是柳永法,','柳永法');
/// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;
///
/// 源串,或字段名
/// 要搜索的字符串
/// 返回是否匹配,1,0
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlBoolean ContainsOne(SqlChars input, string search)
{
return new string(input.Value).Contains(search);
}
///
/// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:
/// select dbo.ContainsAny('我是柳永法,','柳,永,法');
/// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1;
///
/// 源串,或字段名
/// 要搜索的字符串,以","分隔,自己处理空格问题
/// 返回是否匹配,1,0
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlBoolean ContainsAny(SqlChars input, string search)
{
string strTemp=new string(input.Value);
foreach (string item in search.Split(','))
{
if (strTemp.Contains(item)) {
returntrue; } }
returnfalse; }
///
/// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:
/// select dbo.ContainsAll('我是柳永法,','柳,永,法');
/// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1;
///
/// 源串,或字段名
/// 要搜索的字符串,以","分隔,自己处理空格问题
/// 返回是否匹配,1,0
[Microsoft.SqlServer.Server.SqlFunction]
publicstatic SqlBoolean ContainsAll(SqlChars input, string search)
{
string strTemp = new string(input.Value);
foreach (string item in search.Split(','))
{
if (!strTemp.Contains(item)) {
returnfalse; } }
returntrue; } };
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 { /// /// SQL CLR 使用正则表达式替换,eg: /// select dbo.RegexReplace('柳永法http://www.yongfa365.com/','','') /// update Articles set txtContent=dbo.RegexReplace(txtContent,'','') /// --结果:柳永法http://www.yongfa365.com/ /// /// 源串,或字段名 /// 正则表达式 /// 替换后结果 [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); } /// /// SQL CLR 使用正则表达式替换,eg: /// select dbo.RegexSearch('柳永法','','') /// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1; /// /// 源串,或字段名 /// 正则表达式 /// 查询结果,1,0 [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean RegexSearch(SqlChars input, string pattern) { return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success; } /// /// SQL CLR 使用.net的Contains查找是否满足条件,eg: /// select dbo.ContainsOne('我是柳永法,','柳永法'); /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1; /// /// 源串,或字段名 /// 要搜索的字符串 /// 返回是否匹配,1,0 [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean ContainsOne(SqlChars input, string search) { return new string(input.Value).Contains(search); } /// /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg: /// select dbo.ContainsAny('我是柳永法,','柳,永,法'); /// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1; /// /// 源串,或字段名 /// 要搜索的字符串,以","分隔,自己处理空格问题 /// 返回是否匹配,1,0 [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; } /// /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg: /// select dbo.ContainsAll('我是柳永法,','柳,永,法'); /// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1; /// /// 源串,或字段名 /// 要搜索的字符串,以","分隔,自己处理空格问题 /// 返回是否匹配,1,0 [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; } };
重要提示: 官方说明里有其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
参考:
SQL Server CLR 集成简介:http://msdn.microsoft.com/zh-cn/library/ms254498(V S.80).aspx
SQL Server 2005 正则表达式使模式匹配和数据提取变得更容易:http://msdn.microsoft.com/zh-cn/magazine/cc163473. aspx
SQLCLR(一)入门:http://www.cnblogs.com/DavidFan/archive/2007/05/08 /738557.html
应用C#和SQLCLR编写SQL Server用户定义函数:http://blog.csdn.net/zhzuo/archive/2009/05/24/4212 982.aspx#mark4