使用CLR Function代替T-SQL函数，优化检索效率

前言：
在使用存储过程查询数据中，T-SQL字符串拆分函数效率低下，这个时候我们可以采用CLR Function代替T-SQL函数，使用DLL执行字符串分解过程，并返回值到SQL中。测试复杂运行的速度：未使用CLR Function时 CPU时间消耗为3228毫秒，占用时间为2375毫秒，使用后CPU时间消耗为125毫秒，占用时间为118毫秒。

概述
微软在推出SQL Server 2005后，实现了对.NET CLR的集成，使得.NET代码可在SQL Server服务器进程中执行。开发人员通过C#和SQLCLR可轻松创建存储过程、用户定义函数、触发器和用户定义类型等功能，改变了以前只能通过T- SQL语言来实现这些功能的局面。作为SQLCLR的典型应用，本文将通过C#编写基础简单的字符串分割，来演示怎么使用CLR Function代替T-SQL函数。

首先第一步：
在Visual Studio中新建一个名为“CLR_GetParamsList”的类库项目。然后，新建一个名为“CLRFunctions”的类，并在其内添加一个名为“Split”的方法，并添加相应的引用，代码如下：

using System;

using System.Collections;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SqlTypes;

namespace CLR_GetParamsList

{

    public class CLRFunctions

    {

        [Microsoft.SqlServer.Server.SqlFunction(Name = "Clr_Split", FillRowMethodName = "SplitFillRow", TableDefinition = "item nvarchar(256)")]

        public static IEnumerable Split(SqlString input, SqlString separators)

        {

            string[] sArray;

            if (input.IsNull)

            {

                sArray = new string[] { null };

            }

            else

            {

                string s = input.ToString();

                sArray = s.Split(',');

            }

            return sArray;

        }

    }

}

（注：这里我们未来将传入的示例参数为“1，2，3，4，5”格式，“，”为分隔符）

第二步
我们需要编译这个项目为一个DLL，并在SQL Server中注册它。这也是比较简单的，在VS中右键单击项目，选择“生成”后程序就会生成一个DLL。可以在如下所示那样的路径里找到编译好的DLL。

使用CLR Function代替T-SQL函数，优化检索效率

第三步
默认情况下，SQL Server中的CLR是关闭的，所以我们需要在SQL中执行如下命令打开CLR：（注：使用哪个数据库就在那个数据库中注册，注册到master或其他数据库中是无法使用的）

exec sp_configure 'clr enabled',1

reconfigure

go

第四步
为了调用我们写的那个方法，需要在SQL Server中注册我们刚刚编译好的那个DLL。我们可以在数据库中使用如下命令来注册DLL（路径为你的DLL文件的路径），这里为了路径方便我把DLL提取到C盘根目录下存放。

CREATE ASSEMBLY CLR_GetParamsList FROM 'C:\CLR_GetParamsList.dll'

第五步
在SQL Server中调用我们的.NET方法，为了调用.NET方法，我们可以写一个SQL Server自定义函数，并在其内使用“EXTERNAL NAME”来通知SQL Server使用CLR功能。代码如下：

CREATE FUNCTION [dbo].[CLR_GetParamsList](@input [NVARCHAR](MAX), @separators [NVARCHAR](MAX))

RETURNS  TABLE (

    [ID] [NVARCHAR](1000) NULL

) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [CLR_GetParamsList].[CLR_GetParamsList.CLRFunctions].[Split]

最后
执行你的存储过程吧！

SELECT * FROM [dbo].[CLR_GetParamsList] ('1,2,3,4,5,6,7,8,9,0',',')

提示
当你不使用这个方法的时候，可以在数据库中把它注销掉！

DROP ASSEMBLY CLR_GetParamsList（这个是注销该DLL文件）

总结
SQL Server 一般使用SQL-CLR来实现专门执行计算的操作功能，使用T-SQL来实现基于集合的操作功能，或许没有直接建表值函数和标量函数那么快速，但是在处理某些功能时却明显优于T-SQL，优势和劣势相对应，如果数量级较小建议使用T-SQL直接处理，方便管理，如果数量级较大影响到运行速度，也许CLR就是你的备选方案。

秒客网

使用CLR Function代替T-SQL函数，优化检索效率

相关文章