SQL Server Solutions - When to use CLR Integration For Stored Procedures In SQL Server 2005: Part 3 (SQL Server 2005)
In previous tips, we've examined some of the differences between CLR-based and T-SQL-based stored procedures, with a particular focus on performance. So far, we've identified two common scenarios. In one, where you're primarily carrying out one or more straightforward DML operations, we decided that T-SQL code was preferable. In the other, where you need to perform one or more actions for each row in a result set, we suggested that .NET-based code was the better choice. But what if you have a mixed scenario, where your procedure needs to accomplish several tasks that fall into both categories? A good choice here, and if it's possible, is to separate the logic such that a T-SQL procedure carries out the basic DML, and then a CLR-based procedure calls that T-SQL procedure to derive its working data, and continues with .NET code to carry out its more intensive activies. The compromise can squeeze precious performance out of your database application.