When I teach Yukon, the students quite often ask when to use stored procedures written in T-SQL vs. procedures written using any of the CLR languages. My answer is along the lines; if your procedure is purely data access use T-SQL, if the procedure is doing lots of computational work use the CLR.
During the last gig (in Korea), the question was raised again, and instead of just give the standard answer I decided to "croft up" some code to really see the difference. So I created some CLR code along the lines of:
public static double ClrFactorial(double x) {
double y = 1.0;
while(x > 1) {
y = y*x;
x -= 1;
}
return y;
}
I compiled the code and catalogued it in Yukon as a User Defined Function (UDF). I then created the equivalent code in T-SQL as so:
create function SqlFactorial(@x float)
returns float
as
begin
declare @y float
set @y = 1
while @x > 1
begin
set @y = @x * @y
set @x = @x -1
end
return @y
end
Finally I wrote some T-SQL code to compare the functions:
declare @a datetime, @b datetime, @res datetime
set @a = getdate()
select dbo.ClrFactorial(subtotal), dbo.ClrFactorial(totaldue)
from salesorderheader
where subtotal < 100
set @b = getdate()
select datepart(s, @b - @a) secs, datepart(ms, @b - @a) milliseconds
declare @a datetime, @b datetime, @res datetime
set @a = getdate()
select dbo.SqlFactorial(subtotal), dbo.SqlFactorial(totaldue)
from salesorderheader
where subtotal < 100
set @b = getdate()
select datepart(s, @b - @a) secs, datepart(ms, @b - @a) milliseconds
I executed the code and the results were really interesting. I leave it up the the reader to check it out for themselves,(hint: CLR is fast!)