Arguments in favor of each of the two choices are numerous. CLR integrations offer a more powerful programming model, according to . This is true because the .NET Framework languages are from many points of view richer than Transact-SQL, offering capabilities that were not available to SQL Server developers before. Also the enhancements found in the .Net Framework are open for usage in this case. But the Transact-SQL materialize the results of calling a table valued function for example into an intermediate table, which can support constraints and unique indexes on the results. This is one of the features that can be extremely useful when large results are returned. Also writing in T-SQL is obviously better in the perspective of portability and code reuse. Stored procedures written in T-SQL can be easily moved with no or little adjustment to other relational database platforms, while opting for the CLR integration and writing user functions in C# would bound the application to a specific vendor. These are the main reasons for which we have chosen to incorporate business rules into T-SQL procedures.
The latest .Net release of Visual Studio and SQL Server added a brand new feature, unique to the best of our knowledge: integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. What this means is that it is possible now to write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any of the.NET Framework languages, including the revitalized Visual Basic .NET and of course C#. The code written in one of those languages is managed code and when it is used to run inside SQL Server it is referred to as a "CLR routine". Examples of CLR routines are: scalar-valued user-defined functions, table-valued user-defined functions, user-defined procedures, user-defined triggers. Any routine has a T-SQL declaration and can be used anywhere in SQL Server that the Transact-SQL equivalent can be used.
So, the question is obvious. Which strategy should developers follow ? The traditional T-SQL one, or the CLR integration of managed code, written in any of the supported programming languages ?