Niels SQL Server Blog

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!)