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.

Go back