User-defined scalar functions are likely the most obvious candidates for SQLCLR usage. There are two primary reasons for this. The first is that CLR functions actually have lower invocation overhead than T-SQL functions. T-SQL functions require the runtime to create a new T-SQL frame, which is expensive. CLR functions are embedded in the plan as a function pointer for direct execution.
The second advantage comes from the fact the CLR executes compiled code while T-SQL is interpreted at runtime. This is a significant difference that can be magnified as the complexity of the function increases. Let’s look at two comparisons that illustrate these points.
Empty Function
T-SQL:
CREATE FUNCTION SimpleFunctionTSQL(@i INT) RETURNS INT
AS
BEGIN
RETURN @i
END
go
SQLCLR:
public static int SimpleFunction(int i)
{
return i;
}
As you can see, no real work is being done in these functions. That should give us a good idea of the pure invocation overhead. Of course, results will vary with hardware specifications, but in general we see SQLCLR functions outperforming T-SQL functions by at least 3x.
Computation Function
create function IsPrimeTSQL(@x int)
returns bit
as
begin
DECLARE @isprime bit
DECLARE @divisor int
SET @isprime=1
SET @divisor=2
WHILE( @isprime=1 AND @divisor <= (@x/2) )
IF( (@x % @divisor) = 0 )
SET @isprime = 0
ELSE
SET @divisor = @divisor + 1
RETURN @isprime
end
public static int IsPrime(int x)
int isprime = 1;
int divisor = 2;
while (isprime == 1 && divisor <= x / 2)
if ((x % divisor) == 0)
isprime = 0;
else
divisor++;
return isprime;
This second example simulates a computationally intensive function to give us a good idea of what to expect from SQLCLR as complexity increases. In the case of this prime number validating function the performance advantage of SQLCLR over T-SQL is an order of magnitude in speedup!
Disclaimer: This posting is provided “AS IS” with no warranties, and confers no rights.Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.