RBarryYoung (12/19/2008)

---

IMHO SQLCLR only outperforms T-SQL in two general cases:

1) In cases where the same activation path must be followed by both (Triggers, Service Broker activation procs) then CLR appears to perform comparably or better (though that probably depends on how much data interaction is required).

2) In cases where the CLR-required activation path can be bypassed by in-lining T-SQL (user defined functions and stored procedures) CLR can only equal or beat the speed of T-SQL:
if the amount of calculation per activation is very high, or is much higher for T-SQL than it is for CLR and the additional data access required (outside of the passed parameters) is very low or
if CLR enables a lower-order query than T-SQL can achieve (such as reduce a Triangular Join to a Linear one)

In practice (2) hardly ever happens.

There is also a third general case to consider:

3) Cases where SQLCLR has an activation path that T-SQL cannot use (User Defined Aggregates, User Defined Type methods). These cannot necessarily be said to outperform T-SQL, because they are things that you cannot even do them with T-SQL, though you might be able to approach the problem in a different way that can use T-SQl.

ritesh.saluja (12/19/2008)

---

Ex. You wrote "In cases where the same activation path must be followed by both". I am unable to understand this.
...
similarly "In cases where the CLR-required activation path can be bypassed by in-lining T-SQL",

CREATE Function dbo.fnAdd (@A as int, @B as int)
 Returns int As
Begin
	Return @A+@B
End

Public function dnAdd(A as Long, B as Long) as Long
    Return A + B
End Function

Select ID, dbo.fnAdd(colA, colB) as [Total]
 From MillionRowTable

Select ID, dbo.dnAdd(colA, colB) as [Total]
 From MillionRowTable

Select ID, (colA + colB) as [Total]
 From MillionRowTable

Triggers and Service Broker activation procedures are different, because some kind of "activation" or "invocation" must occur, so there is no way for the T-SQL to get around this overhead.