使用clr存储过程可以解决哪些问题?

许多需要非规范化和/或顺序操作的问题都可以由clr非常好地处理,并且可以用于显著提高性能,而不会牺牲SQL端的可用性(很多)。与完全依赖于基于集合或迭代的操作不同,您可以采用混合方法,对大拖运使用基于集合的解决方案,并切换到紧密循环的迭代模型。

内置的 hierarchyid 以及地理空间(即 geography )SQL Server 2008中的类型是非规范化问题的好例子。两者都包含(几乎)任意大量的数据,这些数据很难在不损害性能的情况下正常化—否则,您需要使用递归或游标对它们进行任何有意义的工作,或者使用一个由触发器和/或计划任务组成的老鼠窝来维护非规范化表。

我用clr类型解决的另一个问题是内联压缩。这听起来像是一个毫无意义或学术性的练习,但是当完全规范化的数据被推到太字节时,80-90%的大小减少意味着很多。现在,SQL有自己的内置压缩,而2005年的SQL有vardecimal,这些都是很好的工具,但是一个域感知的“最小化”算法在CPU负载和压缩率方面的效率可以提高几倍。显然,这并不适用于所有问题,但也适用于某些问题。

在这个站点上经常发现的另一个非常常见的问题是动态生成一个序列,例如连续日期序列。常见的解决方案有递归CTE、静态序列表和鲜为人知的 spt_values 表,但是一个简单的clr udf比它们中的任何一个都要好,并且提供了更多的灵活性。

最后一个问题是:用户定义的流聚合也非常有用,特别是对于任何相关的统计数据。在内置的SQL聚合中,有些东西您根本无法组合,例如中间值、加权移动平均值等。UDA还可以接受多个参数,以便对它们进行参数化;从技术上讲,在当前版本的SQL Server中,聚合不能保证以任何特定的顺序接收数据,但您可以绕过这个问题。通过给它喂食限制 ROW_NUMBER 作为一个附加参数,并使用它来实现几乎所有窗口化函数(让聚合吐出一个UDT,然后将其转换为表)。

事实上,很少有真正有用的SQL-CLR应用程序的例子是令人沮丧的;在Google上搜索,你会得到1000万个结果,每一个都有一些愚蠢的字符串连接或regex。这些是有用的,但是花几分钟的时间来了解SQL UDT和UDA,特别是在您自己的应用程序中,您会发现它们有很多用途。当然,不要胡思乱想——仔细考虑纯SQL中是否有更好的解决方案——但也不要低估它们。

字符串操作-正则表达式搜索是经典的。在clr中很容易公开,在直T-SQL中很难做到。

见 this link 有关实施和微观基准的详细信息( SQLCLR is only 47 milliseconds compared to 6.187 seconds for the T-SQL UDF )

已经提到了字符串操作(regex),但也提到了日期时间算法,当然还有另一个重要的方法——调用外部Web服务。

下面是一个我使用clr procs的例子,我认为它很好:

使用clr存储过程和sql作业从外部WebServices进行定时数据更新。

我们有一个应用程序可以将它跟踪的一些数据与外部行业数据源同步。同步每周运行一次,每次更新都按需运行,所以我有一个现有的WebServiceAPI来访问它。事情已经由Windows服务安排好了,但是我想为什么不能像我们的其他SQL作业那样安排它们呢??

我创建了一个引用应用程序的WebServiceAPI的clr存储过程。然后我为@recordid添加了一些参数以支持单同步,并将其安排在Enterprise Manager SQL作业中。

现在,我可以使用该作业来运行db syncs,或者在其他SQL过程或触发器中使用该过程来更新外部提要中的数据。

将来将应用程序WebServiceAPI去掉,直接使用外部WebService可能会更干净。但现在,这是一种实现速度非常快的方法,也是将功能扩展到SQL组的一种很酷的方法。

• 自定义聚合
• 字符串操作
• 自定义数据类型

老实说,我只看到字符串处理,包括将CSV拆分成行。

我会考虑任何需要比默认信任级别更高的内容超出界限,除非我是一个DBA,做DBA类型的工作。

从带有regex和rss feed的msdn示例: Using CLR Integration in SQL Server 2005

它对于从没有提供传统SQL接口的系统中提取数据非常有用,或者供应商对该接口的实现低于标准。

我们在旧的mumps平台上构建了一个核心应用程序,运行在系统间缓存数据库上。数据是分层的,本质上不是关系型的。主全局数组(即表)具有多个数据级别和元素,它们都按帐号分组在一起。即使扫描一列,也需要从磁盘加载整个全局,这需要8个多小时。供应商确实提供了一个ODBC驱动程序和到全局的映射,但它通常会导致扫描和非常慢的查询。

我构建了一个表值函数,它接受一个objectscript(系统间流行性腮腺炎方言)程序,在缓存服务器上执行它,并将输出行作为数据行返回。我可以通过提供一个特定的程序在流行性腮腺炎一侧执行,然后将数据作为一个特别的内联数据源导入到MSSQL中,从而对流行性腮腺炎一侧的数据访问路径进行微观管理(这实际上是获得有效数据访问所需的)。

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