c# - 使用 CLR 存储过程可以解决哪些好问题？

Question

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

MSDN 提供了一些使用指南，例如繁重的字符串操作 (regex)，或替换声明大量表变量和游标的 T-SQL。我很想知道 SO 用户正在使用 CLR 存储过程以及示例/基准解决什么问题。

例如，我发现 CLR 存储过程 + SSRS 是将数据操作逻辑从 SSRS 和 T-SQL 中取出并放入更易于阅读和操作的托管代码中的好方法。

Answer 1

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

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

我用 CLR 类型解决的另一个问题是内联压缩。这听起来像是一项毫无意义或学术性的练习，但当您的完全标准化数据达到 TB 级时，大小减少 80-90% 意义重大。 SQL 现在有自己的内置压缩，SQL 2005 有 vardecimal，这些也是很好的工具，但是域感知“最小化”算法在 CPU 负载和压缩率方面的效率可以提高几倍。显然这并不适用于所有问题，但适用于某些问题。

此站点上经常发现的另一个非常常见的问题是动态生成序列 - 例如一系列连续的日期。常见的解决方案是递归 CTE、静态序列表和鲜为人知的 spt_values 表，但简单的 CLR UDF 的性能优于其中任何一种，并提供更多的灵 active 。

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

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