CLR vs. T-SQL vs stored procedure

The case that I have reviewed should be completed on the current day, but I have been very busy recently... When the number of cases starts, it seems that you have to learn how to distinguish the priorities of tasks.

SQL Server 2005 (including 08) introduces the CLR programming model and uses common programming languages (C #, VB. NET, C ++) to write SQL function triggers.
CLR introduction:
1. Register and run the managed code in the database:
Compile the assembly and use create assembly to put it into the database. Ultimately, CLR functions can be called by T-SQL queries, and CLR processes can also be called by clients.
2. Use VS for Building, Deploying, and Debugging
A specific template is provided to create a project.

Choose CLR, T-SQL or stored procedure?

A. CLR vs. Transact-SQL
T-SQL contains data operations and data definition features. Data operations include SELECT/INSERT/UPDATE/DELETE and WHILE, assignment, triggers, cursors ). CLR provides alternative solutions for Process Languages.
If you can use a descriptive Query Language, try to use it as much as possible, because it is easy to optimize + It can be operated in large batches. CLR is used only when the logic to be expressed cannot be completed using the declarative query language. (T-SQL also has a lot of new features, such as recursive queries, RANK and ROW_NUMBER Analytic Functions, counter T, INTERSECT, APPLY, commit and uncommit relational operations. These are best performed with T-SQL)

When can CLR be used?
1. perform complex calculations on the data in each row, as shown in the following example:
SELECT <complex-calculation> (<column-name> ,...)
FROM <table>
WHERE <complex-calculation> (<column-name>,...) =...
Here is a practical example. Last week, we had a requirement to make statistics on all the cases of our Newgroup. The Chinese headers in the Newsgroup database were base64-encoded and must be decoded after being retrieved. At this time, although it may be possible to write with a T-SQL, but because there is a ready-made decoding assembly, so the assembly is directly referenced. The script in SSIS2005 must also be written in VB, and finally solved with Reflector recommended by Renjie Yu.
2. Operate on table format data (by the way, SQL Server 2008 also allows the transfer of tables, so this article still depends on the specific situation)

CLR Data Access
CLR accesses data through Sqlclient, so the code is relatively lengthy, but the query statement used for the final query is the same as the T-SQL.
For most procedural computing, CLR represents a decisive advantage, but only data access T-SQL prevails.

B. CLR vs. XPs
Compared with stored procedures, CLR has the following advantages:
1. granular control
This name sounds strange. Here, security control is used. CAS is applied here.
2. Data Access
A new connection is required for XPs. The CLR can more effectively use existing connections.
3. Support for Special Data Types
This refers to xml, nvarchar (max), and so on. I guess the stored procedure should support it now.
4. Scalability
I have never known how to translate this word exactly. Measurable? Scalability?
Managed API enables control of Memory thread synchronization and so on. However, the Resouce Governor of the 08 New feature can also be implemented.

All in all, CLR is dominant in Data Access and sending result sets. In addition, hosted code has poor performance due to one step of conversion.

Middle Layer Programming
Some logics can be stored in databases rather than programs. The following are commonly used:
Data Verification
Reduce Network Transmission

Here is an example:
Production Plan
This task includes prediction of future needs and has the following features:
1. Large amounts of data input
2. The result set is small.
3. A considerable amount of computing is required

Putting the algorithm code in the middle layer will lead to a large amount of data transmission and pass requests.
Because it may contain complex data types and complex computing, the stored procedure pass.
So we need to use CLR and T-SQL at this time.

Common application instances:
1. Use. NET framework for data verification
Here we need to use the RegEx class (Regular expression) of the System. Text. RegularExpressions namespace, which is more effective than a write like operation in the T-SQL.
2. Generate a result set
Obtain the result set from the database object (stored procedure or view). If it is simple, you can use the view or inline table-valued function. In case of complexity, it is necessary to rely on the clr ado. NET provider (such as SQLDataReader. It seems that there is another SQLPipe thing, but I don't know much about it. I should skip it.

Summary
This figure below summarizes the use cases: http://www.sqlskills.com/resources/Whitepapers/ SQL %20Server%20DBA%20Guide%20to%20SQLCLR.htm#_Toc110615508

References:
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>