CLR vs T-SQL vs stored procedure

本来复习做过的case是应该当天完成的任务，不过最近忙得够呛。。。当case量开始上去的现在，看来要好好学习下如何分清任务的先后主次。

SQL Server 2005（包括08）引入了CLR编程模型，使用普通编程语言（C#, VB.NET, C++）来写SQL函数触发器等。
CLR介绍：
1. 在数据库中注册和运行托管代码：
编写assembly，使用CREATE ASSEMBLY来放到数据库中。最终CLR函数能被T-SQL查询调用，CLR过程也能被客户端调用。
2. 使用VS来Building, Deploying, and Debugging
有特定模板来新建项目。

到底选择CLR，T-SQL还是stored procedure呢？

A. CLR vs. Transact-SQL
T-SQL包含了数据操作和数据定义特征。数据操作包括说明式查询语言（SELECT/INSERT/UPDATE/DELETE）和过程语言（WHILE, assignment, triggers, cursors等等）。CLR提供了过程语言的替代性方案。
能用说明式查询语言就要尽量用，因为容易优化+能大批量操作。只有要表达的逻辑不能用说明式查询语言来完成了才用CLR。（T-SQL还有很多新增特性，例如递归查询，RANK和ROW_NUMBER等分析性函数，EXCEPT, INTERSECT, APPLY, PIVOT和UNPIVOT 等关系型操作。这些都最好用T-SQL来执行)

那么CLR可以在什么时候用呢？
1. 对每行的数据进行复杂计算，如下范例：
SELECT <complex-calculation>(<column-name>,...)
FROM <table>
WHERE <complex-calculation>(<column-name>,...) = ...
举一个实际的例子。上周有一个需求要对我们Newgroup所有的case进行统计，其中Newsgroup存在数据库中的中文标题都被用Base64编码了，需要在取出来后进行解码。这个时候虽然也可能可以用T-SQL写，不过因为有现成的解码assembly，因此直接引用assembly了。SSIS2005中script还一定要用VB来写，最后用Renjie Yu推荐的Reflector解决了。
2. 对表格式数据操作（顺便说一句，SQL Server 2008中也允许对传递表格了，所以这一条还是要看具体情况了）

CLR数据访问
CLR通过Sqlclient来访问数据，因此代码相对冗长，但最终查询所使用的查询语句和T-SQL都是相同的。
就大多数的过程性的计算而言，CLR占决定性优势，但只有数据访问T-SQL占优。

B. CLR vs. XPs
CLR相比比存储过程的优点如下：
1. 粒状控制
这个名字听起来怪怪的。解释一下的话这里是指安全性控制。CAS在这里得到应用。
2. 数据访问
XPs需要新开个连接。而CLR可以更有效地利用现有连接。
3. 特别的数据类型支持
这里指xml，nvarchar(max)之类，我猜现在的存储过程应该支持了吧
4. Scalability
这个词语我一直不知道怎么样才能最确切的翻译。可测量性？可扩展性？
托管的API使能够控制内存线程同步等。不过08新特性之Resouce Governor也可以做到了。

总而言之，数据访问和发送结果集，CLR表现占优；除此之外，托管代码因为多了一步转换自然性能相对差。

中间层编程
某些逻辑可以放到数据库而非程序中解决。常用的有以下两个：
数据验证
减少网络传输

下面我们来看个范例：
生产计划
该任务包括对未来需求的预测，有以下几个特点：
1. 有大量的数据输入
2. 结果集很小
3. 需要相当多的计算

把算法代码放到中间层会导致大量的需求数据的传输，pass。
因为可能包含复杂数据类型和复杂的计算，所以存储过程pass。
所以这个时候就需要用CLR和T-SQL结合了。

常用应用实例：
1. 使用.NET framework进行数据验证
这里需要用到System.Text.RegularExpressions命名空间的RegEx类（Regular expression），这个比在T-SQL中一个个写like操作更有效。
2. 产生结果集
从数据库对象（存储过程或视图）中获得结果集，如果简单的话可以通过使用视图或inline table-valued function来解决。复杂的话就要靠CLR的ADO.NET provider（如SQLDataReader）了。好像还有个SQLPipe的东西，不过不怎么了解，还是略过算了。

总结
以下的这张图总结了使用场合：http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm#_Toc110615508

参考资料：
how to weigh the strengths and weakness of CLR over T-SQL you might consider reading the following resources from Microsoft:
Using CLR Integration in SQL Server 2005
<http://msdn.microsoft.com/en-us/library/ms345136.aspx>

Performance of CLR Integration
<http://technet.microsoft.com/en-us/library/ms131075.aspx>

other resources that may be helpful:
The Database Administrator’s Guide to the SQL Server Database Engine .NET Common Language Runtime Environment
<http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm#_Toc110615508>

转载于:https://www.cnblogs.com/galaxyyao/archive/2009/02/28/1400420.html