使用CLR存储过程需要解决哪些问题？

Question

我已经在SQL server中使用CLR存储过程一段时间了，但我仍然想知道使用它们的最佳状态是什么。

MSDN为重用字符串操作（regex）提供了一些使用指南，或者替换声明大量表变量和游标的T-SQL。我很想知道用户使用CLR存储特效解决什么问题，以及示例/基准。

例如，我发现CLR存储过程+ SSRS是一种很好的方式，可以将数据操作逻辑从SSRS和T-SQL中取出，并转换为易于阅读和操作的托管代码。

Answer 1

要求非规范化和/或连续操作的许多问题都可以由CLR处理非常好，可以用来显着提高性能，而无需在SQL结束牺牲可用性（许多）。与其完全依赖基于集合的操作或迭代操作，您可以采用混合方法，对大规模运输使用基于集合的解决方案，并转换为紧密循环的迭代模型。

SQL Server 2008中内置的hierarchyid和地理空间（即geography）类型是非规范化问题的很好例子。两者都包含（几乎）任意大量的数据，在不损害性能的情况下很难正常化 - 您需要使用递归或游标来完成任何有意义的工作，或者使用鼠标套件的触发器和/或计划任务来维护一个非规范化表。

我用CLR类型解决的另一个问题是内联压缩。这可能听起来像是毫无意义的或学术性的练习，但是当您的完全标准化的数据推入太字节时，大小减少80-90％意味着很多。 SQL现在具有自己的内置压缩功能，而SQL 2005具有vardecimal，并且这些工具也是很好的工具，但是在CPU负载和压缩率方面，域感知“最小化”算法的效率要高出几倍。显然这不适用于所有问题，但它适用于一些问题。

在这个网站上经常发现的另一个非常常见的问题是在运行中生成一个序列 - 例如一个连续的日期序列。常见的解决方案是递归CTE，静态序列表和鲜为人知的spt_values表，但简单的CLR UDF比其中任何一个表现都更好，并且提供了更大的灵活性。

最后在我的列表中：用户定义的流式聚合也非常有用，尤其是与统计相关的任何事情。有些东西你根本无法从内置的SQL聚合中编写出来，比如中值，加权移动平均数等等.UAD也可以使用多个参数，以便对它们进行参数化;从技术上讲，汇总不能保证在当前版本的SQL Server中以任何特定顺序接收数据，但是您可以通过给它一个ROW_NUMBER作为附加参数来避开该限制，并使用它来实现任何窗口函数（具有总体吐出一个UDT，然后可以转换为一张表）。

实际上很令人沮丧的是，很少有真正有用的SQL-CLR应用程序的例子;在Google上搜索，你会得到1000万个结果，每一个结果都是一些愚蠢的字符串连接或正则表达式。这些是有用的，但花几分钟时间来了解SQL UDT和UDA，特别是您将会在自己的应用程序中看到它们的大量用途。当然，不要疯狂 - 仔细想想纯SQL中是否有更好的解决方案 - 但不要打折它们。

Answer 2

字符串操作 - 正则表达式搜索是一个经典。在CLR中很容易暴露，在直接的T-SQL中很难做到。

查看this link了解实施细节和微基准（SQLCLR is only 47 milliseconds compared to 6.187 seconds for the T-SQL UDF）。

Answer 3

字符串操作（正则表达式）已经被提及，但也包括DateTime算术，当然还有另一个biggie - 调用外部Web服务。

Answer 4

• 自定义汇总
• 字符串操作
• 自定义数据类型

说实话，我只看到字符串处理，其中包括分割的CSV成行。

我会考虑任何需要更多的默认信任级别超出界限，除非我是DBA做DBA类型的东西。

从MSDN用正则表达式和RSS的例子：Using CLR Integration in SQL Server 2005

Answer 5

下面是我用CLR特效的，我认为是整齐的东西一个例子：使用CLR存储的特效和SQL作业外部web服务

定时的数据更新。

我们有一个应用程序，可以将一些跟踪的数据与外部行业数据提要进行同步。同步每周运行一次，并按需更新单一更新，所以我有一个现有的webservice API来访问它。事情已经由Windows服务安排，但我想为什么不能像我们其他的SQL工作一样安排它们？

我创建了一个CLR存储过程，它提供了应用程序的webservice API。然后，我为@RecordID添加了一些参数以支持单个同步，并在企业管理器SQL作业中安排它。

现在我可以使用作业运行dB同步或使用其他SQL proc或触发器内的proc来更新外部提要中的数据。

未来将应用程序Web服务API取出可能会更清洁，并且只需直接使用外部Web服务即可。但现在，这是非常快速的实现方式，也是一种将功能扩展到SQL组的好方法。

Answer 6

将数据从不提供传统SQL接口的系统中提取出来或者该接口的供应商的实现是低于标准时非常有用。

我们有一个核心应用程序构建在旧MUMPS平台上，运行在Intersystems缓存数据库上。数据是分层的，本质上不是关系数据。主全局数组（即表）具有多个数据级别和元素，所有数据级别和元素都按帐号分组在一起。即使扫描一列，也需要从磁盘加载整个全局，并且需要8个小时以上。供应商确实提供了ODBC驱动程序和映射到全局变量，但它经常导致扫描和极慢的查询。

我构建了一个表值函数，它采用ObjectScript（Intersystem的MUMPS方言）程序，在Cache服务器上执行它，并将输出行作为数据行返回。我可以通过提供一个特定的程序在该端执行，然后在MSSQL中轻松地将数据作为临时的内联数据源导入，从而对MUMPS端的数据访问路径进行微操作（这实际上是获得高效数据访问所需的路径）。

我可以使用TVF驱动数据选择或使用CROSS APPLY在另一端进行查找并且效率相当高。如果我强制MSSQL使用并行执行计划，我甚至可以在远程端并行运行多个查询。