i want to ask you one question to all expertise who are using 2005 with CLR.
Is there any performance problem you are facing while using your SPs Functions
with CLR integration in it.
My question is is there any performance drawback using CLR in SQl Server?
Yes, definitely. In General CLR is not faster than in-line SQL, it's slower. There are a variety of reasons for this, but it boils down to the overhead of the call and return and certain limitations in the call interface, particularly, not being able large data sets/objects in every case that you might want to.
Now, encapsulated SQL routines have the same or greater overhead, but SQL has an option that CLR does not to overcome this, in-lining. For instance you can avoid the overhead of calling a SQL-based scalar UDF (user-defined function) by simply including the SQL code "in line" in the calling query itself. Obviously you cannot do this with CLR.
Not so obvious are other options in SQL that have the same effect. Views, CTEs (common table expressions), and SQL-based inline TVFs (table-valued functions) are not treated as encapsulated routines in SQL, but rather as expression macros that are effectively in-lined at run-time.
That said, we have seen many cases where CLR provides substantial performance gains, so long as the processing gain per row or per parameter or per byte substantially exceeds the calling overhead. However, if it is just generally applied in a blanket fashion, you are more likely to see performance decreases than increases (over well-written SQL, it's easy to beat poorly written SQL).