关于.net：在SQL 2005+中，CLR存储过程是否比TSQL存储过程更受青睐？

Are CLR stored procedures preferred over TSQL stored procedures in SQL 2005+?

我当前的观点是不，更喜欢Transact SQL存储过程，因为它们的重量更轻并且(可能)具有更高的性能，而CLR过程使开发人员可以解决各种麻烦。

但是最近我需要调试一些写得很差的TSQL存储过程。像往常一样，我发现许多问题是由于最初的开发人员没有真正的TSQL经验，所以他们专注于ASP.NET/C#。

因此，使用CLR程序首先将为此类开发人员提供更加熟悉的工具集，其次，调试和测试功能会更强大(例如，Visual Studio而不是SQL Management Studio)。

我很想听听您的经验，因为这似乎不是一个简单的选择。

编写完善，经过深思熟虑的T-SQL和CLR都有很多地方。如果某些函数不经常调用，并且在SQL Server 2000中需要扩展过程，则可以选择CLR。在数据旁边运行诸如计算之类的事情可能也会很有吸引力。但是通过引入新技术来解决糟糕的程序员听起来是个坏主意。

CLR存储过程无意替代基于集合的查询。如果您需要查询数据库，则仍然需要将SQL放入CLR代码中，就像将其嵌入常规代码中一样。这将是浪费时间。

CLR存储过程主要用于两件事：1)与OS交互，例如从文件中读取消息或在MSMQ中放入消息，以及2)执行复杂的计算，尤其是当您已经用.NET语言编写的代码要做计算。

在SQL Server中托管CLR的目的是为数据库开发人员提供更多灵活的选项，使其可以尝试完成任务。就像其他人提到的那样，SQL非常适合对数据集进行操作和修改。任何使用复杂的业务/域规则进行了大型应用程序开发的人都可能会告诉您-尝试使用纯SQL强制执行其中一些规则(有时在单个宏查询中)可能会带来噩梦。

只有某些任务可以以过程或OO方式更好地处理。通过选择使用.NET代码来分解逻辑顺序，查询操作可以更易于阅读和调试。使用了CLR存储的proc之后，我可以告诉您逐步使用调试器确实可以更轻松地了解数据库级别的情况。

仅举一个例子，我们在这里经常使用CLR存储的proc作为动态搜索查询的"网关"。说一个搜索请求，最多可以包含30个不同的搜索参数。用户显然不使用全部30个参数，因此传入的数据结构将具有30个参数，但大多数为DBNULL。出于明显的安全原因，客户端没有选择生成动态语句的选项。生成的动态语句在内部生成，而无需担心外部"额外"。

除了文件系统访问(CLR proc具有明显的优势)之外，我还将使用T-SQL proc。如果您有特别复杂的计算，则可以将该代码放入CLR函数中，然后从proc中调用它(udf是我发现CLR集成真正发挥作用的地方)。然后，您可以为任务的特定部分获得CLR集成的好处，但可以将尽可能多的存储在proc逻辑中的信息保留在DB中。

我相信这两个并不相等...适合彼此对抗。
CLR Integration应该逐步淘汰过去的"扩展存储过程"。我们的工作场所中有一些...本质上是处理/逻辑上的SQL数据块，这些数据很难/不可能通过常规的DB存储过程/ T SQL来完成。因此他们将其写为C ++ DLL中的扩展存储过程，可以类似地对其进行调用。
现在它们已被淘汰，而CLR集成可以替代

数据库存储过程：如果可以在T SQL存储过程中完成，请执行。
CLR存储过程：如果逻辑太复杂或繁琐而无法通过T SQL进行...如果它需要花费更少的CLR代码行来解决(字符串操作，复杂/自定义排序或过滤等)，请使用此方法方法。

通常，如果您不需要太多与数据库的接口，则可以使用CLR。因此，假设您正在解析或解码值。这在CLR中更容易完成，然后返回值。

尝试在CLR中进行compelx查询并不是走的路。

顺便说一句，这在2008年也没有改变。

根据您所说的，我希望您对开发人员进行一般的t-SQl和数据库方面的培训，而不是允许他们在CLR中执行t-sql任务，从而对性能造成更大的损害。不了解数据库的开发人员以此为借口，避免以最适合数据库性能的方式进行操作，因为他们想采取他们认为更简单的方法。

我们遇到了带有CLR函数的情况，该函数在常规SQL proc中被调用了数千次。这是从另一个系统导入数据的过程。该函数验证了数据并很好地处理了null。

如果我们在TSQL中执行该操作，则该过程将在大约15秒内完成。如果我们使用CLR功能，则过程将在20-40分钟内完成。 CLR函数看起来更优雅，但是据我们所知，每次使用CLR函数都会遇到启动问题。因此，如果您使用一个CLR函数进行了大笔操作，那很好，因为启动时间比操作时间要短。或者，如果您以适度的次数调用CLR函数，则该函数的所有调用的总启动时间将很小。但是要小心循环。

另外，为了保持可维护性，最好不要使用超出您实际需要的语言。

有关此主题的SQL Server联机丛书的页面列出了这些优点：

更好的编程模型。 .NET Framework语言在许多方面都比Transact-SQL丰富，它提供了SQL Server开发人员以前不可用的结构和功能。开发人员还可以利用.NET Framework库的功能，该库提供了广泛的类集，可用于快速有效地解决编程问题。
改善安全性。托管代码在由数据库引擎托管的公共语言运行时环境中运行。 SQL Server利用此功能为SQL Server早期版本中提供的扩展存储过程提供了更安全，更安全的替代方法。
能够定义数据类型和聚合函数。用户定义的类型和用户定义的聚合是两个新的托管数据库对象，它们扩展了SQL Server的存储和查询功能。
通过标准化环境简化开发。数据库开发已集成到Microsoft Visual Studio .NET开发环境的未来版本中。开发人员使用与编写中间层或客户端层.NET Framework组件和服务相同的工具来开发和调试数据库对象和脚本。
改善性能和可扩展性的潜力。在许多情况下，.NET Framework语言编译和执行模型提供了比Transact-SQL更高的性能。

它总是归结为正确的工作工具，因此它实际上取决于您要完成的工作。

但是，通常来说，正确的是CLR proc具有更大的开销，并且永远不会对T-SQL这样的集合操作执行。我的指导方针是在T-SQL中完成所有操作，除非您需要的内容在T-SQL中变得过于复杂。然后，尽力使T-SQL方法起作用。 :-)

CLR proc很棒，并且确实有其位置，但是它们的使用应该是例外，而不是规则。

我针对类似问题发布了以下答案：SQL SERVER CLR的优势。
不过，我将在此处添加，C＃/ VB.net / etc是一种比T-SQL更熟悉的语言，这不应成为在T-SQL上使用SQLCLR的理由。如果某人不知道如何在T-SQL中完成某事，请首先寻求帮助以查找T-SQL解决方案。如果不存在，请走CLR路线。

SQL Server中的SQLCLR / CLR集成只是帮助解决某些(不是全部)问题的另一个工具。有一些事情比纯T-SQL可以做得更好，还有一些事情只能通过SQLCLR来完成。我为SQL Server Central(通往SQLCLR的阶梯)撰写了一篇文章：什么是SQLCLR？ (在那里阅读文章需要免费注册)，解决了这个问题。基础知识(有关详细信息，请参见链接的文章)：

流表值函数(sTVF)
动态SQL(在函数内)
更好地访问外部资源/替换xp_cmdshell
- 传递数据更容易
- 取回结果集的多列比较容易
- 没有外部依赖项(例如7zip.exe)
- 通过模拟提高安全性
多线程能力
错误处理(功能内)
自定义汇总
自定义类型
修改状态(在函数内且没有OPENQUERY / OPENROWSET)
执行存储过程(只读；在函数内且没有OPENQUERY / OPENROWSET的情况下)
性能(注意：这并非在所有情况下都具有意义，但在某些情况下肯定取决于操作的类型和复杂性)
可以捕获输出(即发送到SSMS中``消息''选项卡的内容)(例如PRINT和RAISERROR的严重性为0到10)-我忘了在文章中提到这一点;-)。

需要考虑的另一件事是，有时能够在应用程序和数据库之间共享代码是有益的，这样数据库就可以洞悉某些业务逻辑，而不必构建仅内部的自定义屏幕来访问该应用程序代码。例如，我曾在一个系统中工作，该系统从客户导入数据文件，并使用大多数字段的自定义哈希值并将该值保存到数据库中的行中。这允许在再次导入数据时轻松跳过行，因为该应用程序将从输入文件中散列值并与存储在行中的散列值进行比较。如果它们相同，那么我们立即知道所有字段都没有更改，因此我们转到下一行，这是一个简单的INT比较。但是，用于执行哈希的算法仅存在于应用程序代码中，因此无论是用于调试客户案例还是通过标记至少具有一个字段的行进行更改(通过我们的应用程序进行更改)来寻找将某些处理工作分流到后端服务的方法而不是在更新的导入文件中查找更改)，我无能为力。那将是一个很好的机会，即使没有进行正常处理，在DB中拥有相当简单的业务逻辑也是如此。由于无法在数据库中获得等于编码值的值，因此无法解决问题。

如果有兴趣在不编写任何代码的情况下查看其中的某些功能，则免费版本的SQL＃(我是作者)具有RegEx函数，自定义聚合(UDA)，自定义类型(UDT)等。

我会添加一些可能没有提到的使用CLR的原因。