原文: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
发表评论
-
C语言字符串函数大全
2012-07-06 09:52 511函数名: strrev 功能: 串倒转用法: char * ... -
基于commons-fileupload组件的上传下载与删除
2012-07-03 13:44 810作者:蜗牛 527812862 1. 首先把ja ... -
怎么在DropdownList下面嵌入图片
2012-07-02 12:54 872flex4 的DropdownList下拉框组件本身并不支 ... -
[tamarin系列之3] ASC编译器命令行基础
2012-07-02 12:54 9141、ASC编译器简介 avmshell执行了abc文 ... -
mx_internal变量读取和设置
2012-07-02 12:54 670Flex中很多组件的变量都有mx_internal命名空间 ... -
让数据自己来讲故事
2012-07-02 12:54 539Adobe Flash Builder 4 简 ... -
flash builder 制作 浏览器并 读写配制文件
2012-07-02 12:54 736flex/spark" xmlns:mx=&q ... -
Flex 开发框架汇总
2012-07-01 10:21 608现有成熟常用的Flex ... -
探究 Flex 组件的生命周期
2012-07-01 10:21 491简介: 最为新一 ... -
Silverlight与Flex的比较选择
2012-07-01 10:21 518flash已经出现很多 ... -
重写Flex组件
2012-07-01 10:21 647一、为什么要重写组件 1、在FLEX已有组件无法满足业务 ... -
实现最大化、最小化、缩放功能的TitleWindow
2012-07-01 10:20 744前段时间在做flex的一个文档在线浏览时用到了需要Titl ... -
iphone开发-3ds降价继续友好合作?Adobe发布开发平台AIR
2012-06-30 16:49 640iphone开发-3ds降价继续友 ... -
ADOBE推出“Adobe RIA开发工程师”认证
2012-06-30 16:48 3ADOBE推出“Adobe RIA开发 ... -
成为一名高端的Flash游戏开发工程师需要具备的方方面面
2012-06-30 16:48 621成为一名高端的Flash游 ... -
[转载]Test 4 : Ben Forta谈Flash和HTML5游戏开发的差异性
2012-06-30 16:48 737[转载]Test 4 : Ben Forta谈Fl ... -
Ben Forta谈Flash和HTML5游戏开发的差异性
2012-06-30 16:48 735Ben Forta谈Flash和HTML5游戏 ...
相关推荐
/// /// SQL CLR 使用正则表达式替换,eg: /// select dbo.RegexReplace('柳永法</span>','<.+?>','') /// update Articles set txtContent=dbo.RegexReplace...http://www.yongfa365.com/Item/SQL-Server-CLR.html
演示如何一步步SQL server CLR调试部署
SQL SERVER CLR 的讲解,如果配置CLR,及一个CLR 库的文档 ,可以针对例子,编写自已的CLR 函数.
CHM帮助文件中有各函数详细的说明,以及部署、卸载、配置SQL Server的SQL脚本。CLR扩展DLL 在MS-SQL Server 环境中实现文件、目录操作,程序使用VS2010编写,语言Vb.net。运行起来飞一般的感觉! 包含文件、目录操作...
关于sqlserver2008 clr集成 资源源于不但搜索,自由源于不但努力
不多说,CLR Types for Microsoft SQL Server 2014,64位,支持 ReportViewer 2015。 找了好久才找到,真的来之不易,象征性收1个积分。
Sqlserver2005 CLR数据库对象开发指南
使用 SQL Server 2005中的 CLR 集成
部署查看Windows SQL服务器报表的时候会用到
Microsoft System CLR Types For SQL Server 2012 /SQLSysClrTypes.msi
详细介绍SQL/CLR,让你用c#更轻松的来写存储过程,触发器等等
microsoft system clr types for sql server 2012 中文 microsoft system clr types for sql server 2012.7z
第19章 使用CLR访问SQL Server 2008
提高你的数据库编程效率_Microsoft CLR Via Sql Server 文章中的数据库脚本。详细情况请参阅博客:http://blog.csdn.net/ghostbear
SQL Server CLR 存储过程实现数据的ZIP压缩和解压,可对表和视图里的数据进行压缩, 完全实现Zip的压缩和打包算法,不依赖磁盘上的文件。
找了好久的microsoft system clr types for sql server 2012/2014,为了rdlc可编辑需要这个插件
microsoft system clr types for sql server 2012 想安装report viewer2012需要用到这个
百度microsoft system clr types for sql server 2012出来的微软下载地址都只是一个txt的文件,csdn居然有人要50分,太气愤了,积分意思的收1分,确实找这个花了点时间,里面包含英文、简、繁体。解压密码: ...
Microsoft® System CLR Types for Microsoft® SQL Server® 2012 SQL Server System CLR Types 包包含用于在 SQL Server 2012 中实现 geometry、geography 和 hierarchy ID 类型的组件。此组件可单独从服务器安装...
microsoft system clr types for sql server 2014 VS2017 开发报表时使用。