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

首先总结：SQL Server CLR 设计简单，部署方便，效率很高，很安全，随数据库移动。
测试用例，视频演示在此下载：http://download.csdn.net/source/2279319
环境：SQL Server 2005/2008/2008 R2，Visual Studio 2005/2008/2010

在SQL Server里启用CLR：

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

view plaincopy to clipboardprint?
  1. EXEC sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr  
  2.   
  3. RECONFIGURE WITH OVERRIDE --不加 WITH OVERRIDE在SQL Server 2008 R2上運行不通過  

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

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

view plaincopy to clipboardprint?
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Data.SqlTypes;  
  5. using Microsoft.SqlServer.Server;  
  6.   
  7. public partial class UserDefinedFunctions  
  8. {  
  9.     [Microsoft.SqlServer.Server.SqlFunction]  
  10.     public static SqlString Function1()  
  11.     {  
  12.         // 在此处放置代码  
  13.         return new SqlString("Hello");  
  14.     }  
  15. };  

现在可以直接右击解决方案，选择“部署”，状态栏里显示“部署已成功”

在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 '柳永法%'这样可以使用索引的查询的。

        想来想去只能是使用全文索引，但总会有一些记录查不出来,而这个要求就这么高，所以暂时放弃。这时想到了SQL Server CLR,以前只是听过，觉得可能有用，都收藏了起来，现在打开Chrome,把Google Bookmark上收藏的关于SQL Server的CLR的链接全部打开研究了几分钟，自己写了个函数，部署，测试，哈哈……。忒玄妙了，以前的txtContent LIKE '%柳永法%'用时10到12秒，而用我写的SQL Server CLR函数dbo.ContainsOne(txtContent,'柳永法')=1只用了1秒左右，够神奇吧。

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

view plaincopy to clipboardprint?
  1. INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent  FROM dbo.Articles  

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

函数及测试语句如下：
 

view plaincopy to clipboardprint?
  1. [Microsoft.SqlServer.Server.SqlFunction]  
  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  

另外，我比较热衷于正则表达式，所以我还想给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 plaincopy to clipboardprint?
  1. using System.Data.SqlTypes;  
  2. using System.Text.RegularExpressions;  
  3.   
  4. /* 
  5. 请先在SQL Server里执行以下命名，来启用CLR 
  6. EXEC sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr 
  7. RECONFIGURE WITH OVERRIDE 
  8. */  
  9. namespace CtripSZ.SQLCLR  
  10. {  
  11.     public static partial class UserDefinedFunctions  
  12.     {  
  13.   
  14.   
  15.         /// <summary>  
  16.         /// SQL CLR 使用正则表达式替换，eg:  
  17.         /// select dbo.RegexReplace('<span>柳永法</span>','<.+?>','')  
  18.         /// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')  
  19.         /// --结果：柳永法  
  20.         /// </summary>  
  21.         /// <param name="input">源串，或字段名</param>  
  22.         /// <param name="pattern">正则表达式</param>  
  23.         /// <returns>替换后结果</returns>  
  24.         [Microsoft.SqlServer.Server.SqlFunction]  
  25.         public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)  
  26.         {  
  27.             if (input.IsNull) return null;  
  28.             return Regex.Replace(input.ToStr(), pattern.Value, replacement.Value);  
  29.         }  
  30.   
  31.         /// <summary>  
  32.         /// SQL CLR 使用正则表达式替换，eg:  
  33.         /// select dbo.RegexSearch('<span>柳永法</span>','<.+?>','')  
  34.         /// select * from Articles where dbo.RegexIsMatch(txtContent,'柳永法')=1;  
  35.         /// </summary>  
  36.         /// <param name="input">源串，或字段名</param>  
  37.         /// <param name="pattern">正则表达式</param>  
  38.         /// <returns>查询结果，1,0</returns>  
  39.         [Microsoft.SqlServer.Server.SqlFunction]  
  40.         public static SqlBoolean RegexIsMatch(SqlChars input, string pattern)  
  41.         {  
  42.             if (input.IsNull) return false;  
  43.             return Regex.IsMatch(input.ToStr(), pattern);  
  44.         }  
  45.   
  46.         /// <summary>  
  47.         /// SQL CLR 使用.net的Contains查找是否满足条件,eg:  
  48.         /// select dbo.ContainsOne('我是柳永法，','柳永法');  
  49.         /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;  
  50.         /// </summary>  
  51.         /// <param name="input">源串，或字段名</param>  
  52.         /// <param name="search">要搜索的字符串</param>  
  53.         /// <returns>返回是否匹配,1,0</returns>  
  54.         [Microsoft.SqlServer.Server.SqlFunction]  
  55.         public static SqlBoolean ContainsOne(SqlChars input, string search)  
  56.         {  
  57.             if (input.IsNull) return false;  
  58.             return input.ToStr().Contains(search);  
  59.         }  
  60.   
  61.         /// <summary>  
  62.         /// 實現類似 DateTime.ToString("yyyy-MM-dd");  
  63.         /// </summary>  
  64.         /// <param name="input"></param>  
  65.         /// <param name="format"></param>  
  66.         /// <returns></returns>  
  67.         [Microsoft.SqlServer.Server.SqlFunction]  
  68.         public static SqlString GetDateTimeString(SqlDateTime input, string format)  
  69.         {  
  70.             if (input.IsNull) return null;  
  71.             return input.Value.ToString(format);  
  72.         }  
  73.   
  74.   
  75.   
  76.         /// <summary>  
  77.         /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:  
  78.         /// select dbo.ContainsAny('我是柳永法，','柳|永|法');  
  79.         /// select * from Articles where dbo.ContainsAny(txtContent,'柳|永|法')=1;  
  80.         /// </summary>  
  81.         /// <param name="input">源串，或字段名</param>  
  82.         /// <param name="search">要搜索的字符串，以"|"分隔，自己处理空格问题</param>  
  83.         /// <returns>返回是否匹配,1,0</returns>  
  84.         [Microsoft.SqlServer.Server.SqlFunction]  
  85.         public static SqlBoolean ContainsAny(SqlChars input, string search)  
  86.         {  
  87.             if (input.IsNull) return false;  
  88.   
  89.             string strTemp = input.ToStr();  
  90.             foreach (string item in search.Split('|'))  
  91.             {  
  92.                 if (strTemp.Contains(item))  
  93.                 {  
  94.                     return true;  
  95.                 }  
  96.             }  
  97.             return false;  
  98.         }  
  99.   
  100.         /// <summary>  
  101.         /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:  
  102.         /// select dbo.ContainsAll('我是柳永法，','柳|永|法');  
  103.         /// select * from Articles where dbo.ContainsAll(txtContent,'柳|永|法')=1;  
  104.         /// </summary>  
  105.         /// <param name="input">源串，或字段名</param>  
  106.         /// <param name="search">要搜索的字符串，以"|"分隔，自己处理空格问题</param>  
  107.         /// <returns>返回是否匹配,1,0</returns>  
  108.         [Microsoft.SqlServer.Server.SqlFunction]  
  109.         public static SqlBoolean ContainsAll(SqlChars input, string search)  
  110.         {  
  111.             if (input.IsNull) return false;  
  112.   
  113.             string strTemp = input.ToStr();  
  114.             foreach (string item in search.Split('|'))  
  115.             {  
  116.                 if (!strTemp.Contains(item))  
  117.                 {  
  118.                     return false;  
  119.                 }  
  120.             }  
  121.             return true;  
  122.         }  
  123.   
  124.   
  125.         /// <summary>  
  126.         ///   
  127.         /// </summary>  
  128.         /// <param name="input"></param>  
  129.         /// <returns></returns>  
  130.         public static string ToStr(this SqlChars input)  
  131.         {  
  132.             if (input.IsNull) return null;  
  133.   
  134.             return new string(input.Value);  
  135.         }  
  136.     };  
  137.   
  138. }  

重要提示：

    1. 官方说明里有其dll部署方法，比较麻烦，推荐直接用Visual Studio部署，方便快捷。
    2. 如果要部署到自己沒權限訪問的機器上，可以先部署到本地，然後在bin\release下找到一個SQL文件，只用此文件便可完成部署，必須使用sqlcmd執行，如：sqlcmd -S .\devdbinstance -U sa -P XXXXXX -i D:\SqlClr\CtripSZ.SQLCLR.sql另：此SQL是根據目標數據庫不同，而不同，使用時要注意
    3. SQL Server CLR 部署到某个数据库后，便成为那个数据库的一部分，即便备份及还原到其它机器上，它依然具有CLR带来的功能。
    4. SqlString 数据类型转换成 nvarchar(4,000)，而 SqlChars 转换成 nvarchar(max)。尽可能使用 nvarchar(max) 并且最大程度地保证灵活性。然而，如果所有相关字符串包含的字符都少于 4,000 个，使用 nvarchar(4,000) 则性能可得到显著改善。
    5. CLR里返回的bool对应SQL Server里的bit,即：1/0/Null,而不是true/false,所以，没法直接用dbo.ContainsOne(txtContent,'柳永法')实现bool形，而得这么用：dbo.ContainsOne(txtContent,'柳永法')=1
    6. SQL Server 2005/2008/2008 R2使用的都是CLR 2.0所以，只能选择，2.0,3.0,3.5的.net framework

posted on 2012-09-19 10:49  &大飞  阅读(345)  评论(0)  编辑  收藏  举报

导航