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",
Mostly what I am getting at here is that activation/invocation of code, be it SQL or CLR, has substantial overhead. If there is a way for SQL code to be used, without having to activate or invoke it separately, then that will almost always be faster.
For instance, we could write a function in T_SQL to add two numbers together:
CREATE Function dbo.fnAdd (@A as int, @b-2 as int)
Returns int As
We could also write an equivalent VB.net function like so:
Public function dnAdd(A as Long, B as Long) as Long
Return A + B
Now if we write a query that uses them like so:
Select ID, dbo.fnAdd(colA, colB) as [Total]
Select ID, dbo.dnAdd(colA, colB) as [Total]
both will be similarly slow because both will have to be invoked or activated 1 million times (according to Microsoft, the CLR will be slightly faster, though I have not tested it). This is misleading though, because with T-SQL you have an option to get around this overhead:
Select ID, (colA + colB) as [Total]
This is called "in-lining", because you are putting the fnAdd() functions code "in-line" with the rest of the SQL code.
Because this gets rid of the activation overhead (and also because the SQL compiler can optimize it better) it is almost always faster than either T-SQL or CLR in Functions or Stored Procedures. Finally, because this option is obviously only available to T-SQL, it means that CLR in Procedures and Functions can almost never beat T-SQL for the simple reason that the T-SQL does not have to be in a separate Function or Stored Procedure.
Triggers and Service Broker activation procedures are different, because some kind of "activation" or "invocation" must occur, so there is not way for the T-SQL to get around this overhead.